SQL語(yǔ)句構(gòu)建器

問(wèn)題

Java程序員面對(duì)的最痛苦的事情之一就是在 Java 代碼中嵌入 SQL 語(yǔ)句。這么來(lái)做通常是由于 SQL 語(yǔ)句需要?jiǎng)討B(tài)來(lái)生成-否則可以將它們放到外部文件或者存儲(chǔ)過(guò)程中。正如你已經(jīng)看到的那樣，MyBatis 在它的 XML 映射特性中有一個(gè)強(qiáng)大的動(dòng)態(tài) SQL 生成方案。但有時(shí)在 Java 代碼內(nèi)部創(chuàng)建SQL語(yǔ)句也是必要的。此時(shí)， MyBatis 有另外一個(gè)特性可以幫到你，在減少典型的加號(hào),引號(hào),新行,格式化問(wèn)題和嵌入條件來(lái)處理多余的逗號(hào)或 AND 連接詞之前。事實(shí)上，在 Java 代碼中來(lái)動(dòng)態(tài)生成 SQL 代碼就是一場(chǎng)噩夢(mèng)。例如：

    String sql = "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, "
    "P.LAST_NAME,P.CREATED_ON, P.UPDATED_ON " +
    "FROM PERSON P, ACCOUNT A " +
    "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID " +
    "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID " +
    "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) " +
    "OR (P.LAST_NAME like ?) " +
    "GROUP BY P.ID " +
    "HAVING (P.LAST_NAME like ?) " +
    "OR (P.FIRST_NAME like ?) " +
    "ORDER BY P.ID, P.FULL_NAME";

解決方案

MyBatis 3 提供了方便的工具類(lèi)來(lái)幫助解決該問(wèn)題。使用 SQL 類(lèi)，簡(jiǎn)單地創(chuàng)建一個(gè)實(shí)例來(lái)調(diào)用方法生成SQL語(yǔ)句。上面示例中的問(wèn)題就像重寫(xiě) SQL 類(lèi)那樣：

    private String selectPersonSql() {
      return new SQL() {{
        SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
        SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
        FROM("PERSON P");
        FROM("ACCOUNT A");
        INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
        INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
        WHERE("P.ID = A.ID");
        WHERE("P.FIRST_NAME like ?");
        OR();
        WHERE("P.LAST_NAME like ?");
        GROUP_BY("P.ID");
        HAVING("P.LAST_NAME like ?");
        OR();
        HAVING("P.FIRST_NAME like ?");
        ORDER_BY("P.ID");
        ORDER_BY("P.FULL_NAME");
      }}.toString();
    }

該例中有什么特殊之處？當(dāng)你仔細(xì)看時(shí)，那不用擔(dān)心偶然間重復(fù)出現(xiàn)的"AND"關(guān)鍵字，或者在"WHERE"和"AND"之間的選擇，抑或什么都不選。該SQL類(lèi)非常注意"WHERE"應(yīng)該出現(xiàn)在何處，哪里又應(yīng)該使用"AND"，還有所有的字符串鏈接。

SQL類(lèi)

這里給出一些示例：

    // Anonymous inner class
    public String deletePersonSql() {
      return new SQL() {{
        DELETE_FROM("PERSON");
        WHERE("ID = ${id}");
      }}.toString();
    }

    // Builder / Fluent style
    public String insertPersonSql() {
      String sql = new SQL()
        .INSERT_INTO("PERSON")
        .VALUES("ID, FIRST_NAME", "${id}, ${firstName}")
        .VALUES("LAST_NAME", "${lastName}")
        .toString();
      return sql;
    }

    // With conditionals (note the final parameters, required for the anonymous inner class to access them)
    public String selectPersonLike(final String id, final String firstName, final String lastName) {
      return new SQL() {{
        SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
        FROM("PERSON P");
        if (id != null) {
          WHERE("P.ID like ${id}");
        }
        if (firstName != null) {
          WHERE("P.FIRST_NAME like ${firstName}");
        }
        if (lastName != null) {
          WHERE("P.LAST_NAME like ${lastName}");
        }
        ORDER_BY("P.LAST_NAME");
      }}.toString();
    }

    public String deletePersonSql() {
      return new SQL() {{
        DELETE_FROM("PERSON");
        WHERE("ID = ${id}");
      }}.toString();
    }

    public String insertPersonSql() {
      return new SQL() {{
        INSERT_INTO("PERSON");
        VALUES("ID, FIRST_NAME", "${id}, ${firstName}");
        VALUES("LAST_NAME", "${lastName}");
      }}.toString();
    }

    public String updatePersonSql() {
      return new SQL() {{
        UPDATE("PERSON");
        SET("FIRST_NAME = ${firstName}");
        WHERE("ID = ${id}");
      }}.toString();
    }

提示 注意，SQL 類(lèi)將原樣插入 ?LIMIT?、?OFFSET?、?OFFSET n ROWS? 以及 ?FETCH FIRST n ROWS ONLY ?子句。換句話(huà)說(shuō)，類(lèi)庫(kù)不會(huì)為不支持這些子句的數(shù)據(jù)庫(kù)執(zhí)行任何轉(zhuǎn)換。 因此，用戶(hù)應(yīng)該要了解目標(biāo)數(shù)據(jù)庫(kù)是否支持這些子句。如果目標(biāo)數(shù)據(jù)庫(kù)不支持這些子句，產(chǎn)生的 SQL 可能會(huì)引起運(yùn)行錯(cuò)誤。

從版本 3.4.2 開(kāi)始，你可以像下面這樣使用可變長(zhǎng)度參數(shù)：

public String selectPersonSql() {
  return new SQL()
    .SELECT("P.ID", "A.USERNAME", "A.PASSWORD", "P.FULL_NAME", "D.DEPARTMENT_NAME", "C.COMPANY_NAME")
    .FROM("PERSON P", "ACCOUNT A")
    .INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID", "COMPANY C on D.COMPANY_ID = C.ID")
    .WHERE("P.ID = A.ID", "P.FULL_NAME like #{name}")
    .ORDER_BY("P.ID", "P.FULL_NAME")
    .toString();
}

public String insertPersonSql() {
  return new SQL()
    .INSERT_INTO("PERSON")
    .INTO_COLUMNS("ID", "FULL_NAME")
    .INTO_VALUES("#{id}", "#{fullName}")
    .toString();
}

public String updatePersonSql() {
  return new SQL()
    .UPDATE("PERSON")
    .SET("FULL_NAME = #{fullName}", "DATE_OF_BIRTH = #{dateOfBirth}")
    .WHERE("ID = #{id}")
    .toString();
}

• 從版本 3.5.2 開(kāi)始，你可以像下面這樣構(gòu)建批量插入語(yǔ)句：

public String insertPersonsSql() {
  // INSERT INTO PERSON (ID, FULL_NAME)
  //     VALUES (#{mainPerson.id}, #{mainPerson.fullName}) , (#{subPerson.id}, #{subPerson.fullName})
  return new SQL()
    .INSERT_INTO("PERSON")
    .INTO_COLUMNS("ID", "FULL_NAME")
    .INTO_VALUES("#{mainPerson.id}", "#{mainPerson.fullName}")
    .ADD_ROW()
    .INTO_VALUES("#{subPerson.id}", "#{subPerson.fullName}")
    .toString();
}

從版本 3.5.2 開(kāi)始，你可以像下面這樣構(gòu)建限制返回結(jié)果數(shù)的 ?SELECT? 語(yǔ)句,：

public String selectPersonsWithOffsetLimitSql() {
  // SELECT id, name FROM PERSON
  //     LIMIT #{limit} OFFSET #{offset}
  return new SQL()
    .SELECT("id", "name")
    .FROM("PERSON")
    .LIMIT("#{limit}")
    .OFFSET("#{offset}")
    .toString();
}

public String selectPersonsWithFetchFirstSql() {
  // SELECT id, name FROM PERSON
  //     OFFSET #{offset} ROWS FETCH FIRST #{limit} ROWS ONLY
  return new SQL()
    .SELECT("id", "name")
    .FROM("PERSON")
    .OFFSET_ROWS("#{offset}")
    .FETCH_FIRST_ROWS_ONLY("#{limit}")
    .toString();
}

SqlBuilder 和 SelectBuilder (已經(jīng)廢棄)

在3.2版本之前，我們使用了一點(diǎn)不同的做法，通過(guò)實(shí)現(xiàn)?ThreadLocal?變量來(lái)掩蓋一些導(dǎo)致?Java DSL?麻煩的語(yǔ)言限制。但這種方式已經(jīng)廢棄了，現(xiàn)代的框架都?xì)g迎人們使用構(gòu)建器類(lèi)型和匿名內(nèi)部類(lèi)的想法。因此，?SelectBuilder? 和 ?SqlBuilder? 類(lèi)都被廢棄了。

下面的方法僅僅適用于廢棄的?SqlBuilder? 和 ?SelectBuilder? 類(lèi)。

?SelectBuilder? 和 ?SqlBuilder? 類(lèi)并不神奇，但是知道它們?nèi)绾喂ぷ饕彩呛苤匾摹? SelectBuilder? 使用 ?SqlBuilder ?使用了靜態(tài)導(dǎo)入和?ThreadLocal?變量的組合來(lái)開(kāi)啟整潔語(yǔ)法，可以很容易地和條件交錯(cuò)。使用它們，靜態(tài)導(dǎo)入類(lèi)的方法即可，就像這樣(一個(gè)或其它，并非兩者):

    import static org.apache.ibatis.jdbc.SelectBuilder.*;

    import static org.apache.ibatis.jdbc.SqlBuilder.*;

這就允許像下面這樣來(lái)創(chuàng)建方法：

    /* DEPRECATED */
    public String selectBlogsSql() {
      BEGIN(); // Clears ThreadLocal variable
      SELECT("*");
      FROM("BLOG");
      return SQL();
    }

    /* DEPRECATED */
    private String selectPersonSql() {
      BEGIN(); // Clears ThreadLocal variable
      SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
      SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
      FROM("PERSON P");
      FROM("ACCOUNT A");
      INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
      INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
      WHERE("P.ID = A.ID");
      WHERE("P.FIRST_NAME like ?");
      OR();
      WHERE("P.LAST_NAME like ?");
      GROUP_BY("P.ID");
      HAVING("P.LAST_NAME like ?");
      OR();
      HAVING("P.FIRST_NAME like ?");
      ORDER_BY("P.ID");
      ORDER_BY("P.FULL_NAME");
      return SQL();
    }