6.SQL语句构建器

MyBatis在它的XML映射特性中有一个强大的动态SQL生成方案。但有时在Java代码内部创建SQL语句也是必要的。此时，MyBatis有另外一个特性可以帮到你，减少典型的加号,引号,新行,格式化问题和嵌入条件来处理多余的逗号或 AND 连接词。
MyBatis 3提供了方便的工具类来帮助解决该问题。使用SQL类，简单地创建一个实例来调用方法生成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";

    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"，还有所有的字符串链接。

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