MyBatis 入门系列【11】 SQL 构建器MyBatis 在 XML 映射中具备强大的 SQL 动态生成能力。但有

1. 前言

MyBatis 在 XML 映射中具备强大的 SQL 动态生成能力。但有时我们还是需要在 Java 代码里构建 SQL 语句。

在 Java 代码中动态生成 SQL 代码十分麻烦，比如加号、引号、换行、格式化问题、嵌入条件的逗号管理、 AND 连接等，例如：

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";

2. SQL 类

MyBatis 3 提供了方便的工具类来帮助解决此问题。借助 SQL 类，我们只需要简单地创建一个实例，并调用它的方法即可生成 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 类相关示例：

// 匿名内部类风格
public String deletePersonSql() {
  return new SQL() {{
    DELETE_FROM("PERSON");
    WHERE("ID = #{id}");
  }}.toString();
}

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

// 动态条件（注意参数需要使用 final 修饰，以便匿名内部类对它们进行访问）
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();
}

从版本 3.4.2 开始，可以像下面这样使用可变长度参数：

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 开始，可以像下面这样构建批量插入语句：

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 开始，你可以像下面这样构建限制返回结果数的 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();
}

3. 方法 API

3.1 SELECT

SELECT开始新的或追加到已有的SELECT子句，可以被多次调用，参数会被追加到 SELECT 子句。参数通常使用逗号分隔的列名和别名列表，但也可以是数据库驱动程序接受的任意参数。

API：

SELECT(String)
SELECT(String...)

SELECT_DISTINCT开始新的或追加到已有的SELECT子句，并添加 DISTINCT 关键字到生成的查询中。

API：

SELECT_DISTINCT(String)
SELECT_DISTINCT(String...)

3.2 FROM

开始新的或追加到已有的FROM子句。可以被多次调用，参数会被追加到FROM子句。参数通常是一个表名或别名，也可以是数据库驱动程序接受的任意参数。

API：

FROM(String)
FROM(String...)

3.3 JOIN

基于调用的方法，添加新的合适类型的 JOIN 子句。参数可以包含一个由列和连接条件构成的标准连接。

API：

JOIN(String)
JOIN(String...)
INNER_JOIN(String)
INNER_JOIN(String...)
LEFT_OUTER_JOIN(String)
LEFT_OUTER_JOIN(String...)
RIGHT_OUTER_JOIN(String)
RIGHT_OUTER_JOIN(String...)

3.4 WHERE

WHERE插入新的 WHERE 子句条件，并使用 AND 拼接。可以被多次调用，对于每一次调用产生的新条件，会使用 AND 拼接起来。要使用 OR 分隔，请使用 OR()。

API：

WHERE(String)
WHERE(String...)

3.5 OR

OR()使用 OR 来分隔当前的 WHERE 子句条件。可以被多次调用，但在一行中多次调用会生成错误的 SQL。

3.6 AND

AND()使用 AND 来分隔当前的 WHERE子句条件。可以被多次调用，但在一行中多次调用会生成错误的 SQL。由于 WHERE 和 HAVING都会自动使用 AND 拼接, 因此这个方法并不常用，只是为了完整性才被定义出来。

3.7 GROUP_BY

追加新的 GROUP BY 子句，使用逗号拼接。可以被多次调用，每次调用都会使用逗号将新的条件拼接起来。

API：

GROUP_BY(String)
GROUP_BY(String...)

3.8 HAVING

追加新的 HAVING 子句，使用 AND 拼接。可以被多次调用，每次调用都使用AND来拼接新的条件。要使用 OR 分隔，请使用 OR()。

API：

HAVING(String)
HAVING(String...)

3.9 ORDER_BY

追加新的 ORDER BY 子句，使用逗号拼接。可以多次被调用，每次调用会使用逗号拼接新的条件。

API：

ORDER_BY(String)
ORDER_BY(String...)

3.10 LIMIT

追加新的 LIMIT 子句。仅在 SELECT()、UPDATE()、DELETE() 时有效。当在 SELECT() 中使用时，应该配合 OFFSET() 使用（于 3.5.2 引入）。

API：

LIMIT(String)
LIMIT(int)

3.11 OFFSET

追加新的 OFFSET 子句。仅在 SELECT() 时有效。当在 SELECT() 时使用时，应该配合 LIMIT() 使用（于 3.5.2 引入）。

API：

OFFSET(String)
OFFSET(long)

3.12 OFFSET_ROWS

追加新的 OFFSET n ROWS 子句。仅在 SELECT() 时有效。该方法应该配合 FETCH_FIRST_ROWS_ONLY() 使用（于 3.5.2 引入）。

API：

OFFSET_ROWS(String)
OFFSET_ROWS(long)

3.13 FETCH_FIRST_ROWS_ONLY

追加新的FETCH FIRST n ROWS ONLY 子句。仅在 SELECT() 时有效。该方法应该配合 OFFSET_ROWS() 使用（于 3.5.2 引入）。

API：

FETCH_FIRST_ROWS_ONLY(String)
FETCH_FIRST_ROWS_ONLY(int)

3.14 DELETE_FROM

DELETE_FROM(String)开始新的 delete 语句，并指定删除表的表名。通常它后面都会跟着一个WHERE 子句。

3.15 INSERT_INTO

INSERT_INTO(String)开始新的 insert 语句，并指定插入数据表的表名。后面应该会跟着一个或多个VALUES() 调用，或 INTO_COLUMNS() 和 INTO_VALUES() 调用。

3.16 SET

对 update 语句追加 "set" 属性的列表。

API：

SET(String)
SET(String...)

3.17 UPDATE

UPDATE(String)开始新的 update 语句，并指定更新表的表名。后面都会跟着一个或多个 SET() 调用，通常也会有一个 WHERE() 调用。

3.18 VALUES

VALUES(String, String)追加数据值到 insert 语句中。第一个参数是数据插入的列名，第二个参数则是数据值。

3.19 INTO_COLUMNS

INTO_COLUMNS(String...)追加插入列子句到 insert 语句中，应与 INTO_VALUES() 一同使用。

3.20 INTO_VALUES

INTO_VALUES(String...)追加插入值子句到 insert 语句中，应与 INTO_COLUMNS() 一同使用。

3.21 ADD_ROW

ADD_ROW()添加新的一行数据，以便执行批量插入（于 3.5.2 引入）。