SQL語句構建器

問題

Java程序員面對的最痛苦的事情之一就是在Java代碼中嵌入SQL語句。這么來做通常是由于SQL語句需要動態(tài)來生成-否則可以將它們放到外部文件或者存儲過程中。正如你已經看到的那樣，MyBatis在它的XML映射特性中有一個強大的動態(tài)SQL生成方案。但有時在Java代碼內部創(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";

The Solution

MyBatis 3提供了方便的工具類來幫助解決該問題。使用SQL類，簡單地創(chuàng)建一個實例來調用方法生成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();
    }

該例中有什么特殊之處？當你仔細看時，那不用擔心偶然間重復出現的"AND"關鍵字，或者在"WHERE"和"AND"之間的選擇，抑或什么都不選。該SQL類非常注意"WHERE"應該出現在何處，哪里又應該使用"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();
    }

方法	描述
`SELECT(String)`	開始或插入到 `SELECT`子句。可以被多次調用，參數也會添加到 `SELECT`子句。參數通常使用逗號分隔的列名和別名列表，但也可以是數據庫驅動程序接受的任意類型。
`SELECT_DISTINCT(String)`	開始或插入到 `SELECT`子句，也可以插入 `DISTINCT`關鍵字到生成的查詢語句中。可以被多次調用，參數也會添加到 `SELECT`子句。參數通常使用逗號分隔的列名和別名列表，但也可以是數據庫驅動程序接受的任意類型。
`FROM(String)`	開始或插入到 `FROM`子句。可以被多次調用，參數也會添加到 `FROM`子句。參數通常是表名或別名，也可以是數據庫驅動程序接受的任意類型。
`JOIN(String)`,`INNER_JOIN(String)`,`LEFT_OUTER_JOIN(String)`,`RIGHT_OUTER_JOIN(String)`	基于調用的方法，添加新的合適類型的 `JOIN`子句。參數可以包含由列命和join on條件組合成標準的join。
`WHERE(String)`	插入新的 `WHERE`子句條件，由`AND`鏈接?？梢远啻伪徽{用，每次都由`AND`來鏈接新條件。使用 `OR()` 來分隔`OR`。
`OR()`	使用`OR`來分隔當前的 `WHERE`子句條件。可以被多次調用，但在一行中多次調用或生成不穩(wěn)定的`SQL`。
`AND()`	使用`AND`來分隔當前的 `WHERE`子句條件。可以被多次調用，但在一行中多次調用或生成不穩(wěn)定的`SQL`。因為 `WHERE` 和 `HAVING` 二者都會自動鏈接 `AND`, 這是非常罕見的方法，只是為了完整性才被使用。
`GROUP_BY(String)`	插入新的 `GROUP BY`子句元素，由逗號連接。可以被多次調用，每次都由逗號連接新的條件。
`HAVING(String)`	插入新的 `HAVING`子句條件。由AND連接?？梢员欢啻握{用，每次都由`AND`來連接新的條件。使用 `OR()` 來分隔`OR`.
`ORDER_BY(String)`	插入新的 `ORDER BY`子句元素，由逗號連接?？梢远啻伪徽{用，每次由逗號連接新的條件。
`DELETE_FROM(String)`	開始一個delete語句并指定需要從哪個表刪除的表名。通常它后面都會跟著WHERE語句！
`INSERT_INTO(String)`	開始一個insert語句并指定需要插入數據的表名。后面都會跟著一個或者多個VALUES()。
`SET(String)`	針對update語句，插入到"set"列表中
`UPDATE(String)`	開始一個update語句并指定需要更新的表明。后面都會跟著一個或者多個SET()，通常也會有一個WHERE()。
`VALUES(String, String)`	插入到insert語句中。第一個參數是要插入的列名，第二個參數則是該列的值。

SqlBuilder 和 SelectBuilder (已經廢棄)

在3.2版本之前，我們使用了一點不同的做法，通過實現ThreadLocal變量來掩蓋一些導致Java DSL麻煩的語言限制。但這種方式已經廢棄了，現代的框架都歡迎人們使用構建器類型和匿名內部類的想法。因此，SelectBuilder 和 SqlBuilder 類都被廢棄了。

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

方法	描述
`BEGIN()` / `RESET()`	這些方法清空SelectBuilder類的ThreadLocal狀態(tài)，并且準備一個新的構建語句。開始新的語句時， `BEGIN()`讀取得最好。由于一些原因（在某些條件下，也許是邏輯需要一個完全不同的語句），在執(zhí)行中清理語句 `RESET()`讀取得最好。
`SQL()`	返回生成的 `SQL()` 并重置 `SelectBuilder` 狀態(tài) (好像 `BEGIN()` 或 `RESET()` 被調用了). 因此，該方法只能被調用一次！

SelectBuilder 和 SqlBuilder 類并不神奇，但是知道它們如何工作也是很重要的。 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();
    }

上一篇：Logging下一篇：動態(tài) SQL