MyBatis SQL語句構(gòu)建器

SQL語句構(gòu)建器

問題

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

    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 提供了方便的工具類來幫助解決該問題。使用 SQL 類，簡單地創(chuàng)建一個實例來調(diào)用方法生成SQL語句。上面示例中的問題就像重寫 SQL 類那樣：

    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();
    }

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

SQL類

這里給出一些示例：

    // 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 類將原樣插入 ?LIMIT?、?OFFSET?、?OFFSET n ROWS? 以及 ?FETCH FIRST n ROWS ONLY ?子句。換句話說，類庫不會為不支持這些子句的數(shù)據(jù)庫執(zhí)行任何轉(zhuǎn)換。 因此，用戶應(yīng)該要了解目標數(shù)據(jù)庫是否支持這些子句。如果目標數(shù)據(jù)庫不支持這些子句，產(chǎn)生的 SQL 可能會引起運行錯誤。

從版本 3.4.2 開始，你可以像下面這樣使用可變長度參數(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 開始，你可以像下面這樣構(gòu)建批量插入語句：

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 開始，你可以像下面這樣構(gòu)建限制返回結(jié)果數(shù)的 ?SELECT? 語句,：

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版本之前，我們使用了一點不同的做法，通過實現(xiàn)?ThreadLocal?變量來掩蓋一些導致?Java DSL?麻煩的語言限制。但這種方式已經(jīng)廢棄了，現(xiàn)代的框架都歡迎人們使用構(gòu)建器類型和匿名內(nèi)部類的想法。因此，?SelectBuilder? 和 ?SqlBuilder? 類都被廢棄了。

下面的方法僅僅適用于廢棄的?SqlBuilder? 和 ?SelectBuilder? 類。

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

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

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

這就允許像下面這樣來創(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();
    }