1 问题

One of the nastiest things a Java developer will ever have to do is embed SQL in Java code. Usually this is done because the SQL has to be dynamically generated - otherwise you could externalize it in a file or a stored proc. As you've already seen, MyBatis has a powerful answer for dynamic SQL generation in its XML mapping features. However, sometimes it becomes necessary to build a SQL statement string inside of Java code. In that case, MyBatis has one more feature to help you out, before reducing yourself to the typical mess of plus signs, quotes, newlines, formatting problems and nested conditionals to deal with extra commas or AND conjunctions. Indeed, dynamically generating SQL code in Java can be a real nightmare. For example:
Java 程序员最痛苦的事情之一是不得不在 Java 代码中嵌入 SQL 语句。通常这样做是因为 SQL 语句必须动态生成——不然你要将其放在外部文件中或一个存储过程中。正如你所看到的，MyBatis 对于在其 XML 映射特性中动态生成 SQL 有一个强大的解决方案。然而，有时候在 Java 代码中构建一个 SQL 语句的字符串是必要的。在这种情况下，Mybatis 有另外一个特性可以帮助你，在你陷入典型的加号、引号、新行、格式化问题和嵌入条件时处理多余的逗号和 AND 连接之前。事实上，动态地在 Java 中生成 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";

2 解决方案

MyBatis 3 offers a convenient utility class to help with the problem. With the SQL class, you simply create an instance that lets you call methods against it to build a SQL statement one step at a time. The example problem above would look like this when rewritten with the SQL class:
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();
}

What is so special about that example? Well, if you look closely, it doesn't have to worry about accidentally duplicating "AND" keywords, or choosing between "WHERE" and "AND" or none at all. The SQL class takes care of understanding where "WHERE" needs to go, where an "AND" should be used and all of the String concatenation.
这个例子有何特殊之处呢？如果你仔细观察，你会发现你不用再担心不小心多写了个 “AND” 关键字，或在 "WHERE" 和 "AND" 之间做选择还是两者都不选。SQL 类会非常注意 "WHERE" 应该出现在哪里，哪里 "AND" 需要使用，以及所有的字符串连接。

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

方法说明如下：

• SELECT(String)
  SELECT(String...)
  Starts or appends to a SELECT clause. Can be called more than once, and parameters will be appended to the SELECT clause. The parameters are usually a comma separated list of columns and aliases, but can be anything acceptable to the driver.
  开始或拼接 SELECT 子句。可以多次调用，参数会拼接给 SELECT 子句。参数通常是一个逗号分隔的列或列别名的列表，但也可以是数据库驱动可以接受的任何类型。

• SELECT_DISTINCT(String)
  SELECT_DISTINCT(String...)
  Starts or appends to a SELECT clause, also adds the DISTINCT keyword to the generated query. Can be called more than once, and parameters will be appended to the SELECT clause. The parameters are usually a comma separated list of columns and aliases, but can be anything acceptable to the driver.
  开始或拼接 SELECT 子句，并添加 DISTINCT 关键字到生成的查询语句中。可以多次调用，参数会拼接给 SELECT 子句。参数通常是一个逗号分隔的列或列别名的列表，但也可以是数据库驱动可以接受的任何类型。

• FROM(String)
  FROM(String...)
  Starts or appends to a FROM clause. Can be called more than once, and parameters will be appended to the FROM clause. Parameters are usually a table name and an alias, or anything acceptable to the driver.
  开始或拼接 FROM 子句。可以多次调用，参数会拼接给 FROM 子句。参数通常是一个表名和一个别名，或者任何数据库驱动可以接受的类型。

• 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...)
  Adds a new JOIN clause of the appropriate type, depending on the method called. The parameter can include a standard join consisting of the columns and the conditions to join on.
  根据方法调用添加一个新的合适类型的 JOIN 子句。参数可以包含一个由列和 join on 的条件组成的标准 join。

• WHERE(String)
  WHERE(String...)
  Appends a new WHERE clause condition, concatenated byAND. Can be called multiple times, which causes it to concatenate the new conditions each time withAND. Use OR() to split with anOR.
  拼接一个新的 WHERE 条件子句，通过 AND 拼接。可以多次调用，每次都会由 AND 来拼接新的条件。使用 OR() 来分隔 OR。

• OR()
  Splits the current WHERE clause conditions with anOR. Can be called more than once, but calling more than once in a row will generate erraticSQL.
  用 OR 来分隔当前的 WHERE 条件子句。可以多次调用，但是在一行中多次调用将生成不稳定的 SQL。

• AND()
  Splits the current WHERE clause conditions with anAND. Can be called more than once, but calling more than once in a row will generate erraticSQL. Because WHERE and HAVING both automatically concatenate with AND, this is a very uncommon method to use and is only really included for completeness.
  用 AND 来分隔当前的 WHERE 条件子句。可以多次调用，但是在一行中多次调用将生成不稳定的 SQL。因为 WHERE 和 HAVING 都会用 AND 连接自动生成，这是不常用的方法只是基于完整性才被使用。

• GROUP_BY(String)
  GROUP_BY(String...)
  Appends a new GROUP BY clause elements, concatenated by a comma. Can be called multiple times, which causes it to concatenate the new conditions each time with a comma.
  拼接一个新的 GROUP BY 子句元素，由逗号连接。可以被多次调用，每追加一个条件都会使用逗号来连接。

• HAVING(String)
  HAVING(String...)
  Appends a new HAVING clause condition, concatenated by AND. Can be called multiple times, which causes it to concatenate the new conditions each time withAND. Use OR() to split with anOR.
  拼接一个新的 HAVING 条件子句，由 AND 连接。可以被多次调用，每追加一个条件都会使用 AND 来连接。使用 OR() 来分隔 OR。

• ORDER_BY(String)
  ORDER_BY(String...)
  Appends a new ORDER BY clause elements, concatenated by a comma. Can be called multiple times, which causes it to concatenate the new conditions each time with a comma.
  拼接一个新的 ORDER BY 子句元素，由逗号连接。可以被多次调用，每追加一个条件都会使用逗号来连接。

• DELETE_FROM(String)
  Starts a delete statement and specifies the table to delete from. Generally this should be followed by a WHERE statement!
  开始一个 delete 语句并指定要从中删除数据的表。通常它后面应该跟着一个 WHERE 语句。

• INSERT_INTO(String)
  Starts an insert statement and specifies the table to insert into. This should be followed by one or more VALUES() or INTO_COLUMNS() and INTO_VALUES() calls.
  开始一个 insert 语句并指定要插入数据的表。它后面应该跟着一个或多个 VALUES() 或 INTO_COLUMNS() 和 INTO_VALUES() 调用。

• SET(String)
  SET(String...)
  Appends to the "set" list for an update statement.
  为一个 update 语句拼接 "set" 列表。

• UPDATE(String)
  Starts an update statement and specifies the table to update. This should be followed by one or more SET() calls, and usually a WHERE() call.
  开始一个 update 语句并指定要更新数据的表。它后面应该跟着一个或多个SET() 调用，通常还有一个 WHERE() 调用。

• VALUES(String, String)
  Appends to an insert statement. The first parameter is the column(s) to insert, the second parameter is the value(s).
  拼接到一个 insert 语句。第一个参数是要插入的列，第二个参数是对应要插入的值。

• INTO_COLUMNS(String...)
  Appends columns phrase to an insert statement. This should be call INTO_VALUES() with together.
  拼接列短语到一个 insert 语句。它应该跟 INTO_VALUES() 一起调用。

• INTO_VALUES(String...)
  Appends values phrase to an insert statement. This should be call INTO_COLUMNS() with together.
  拼接值短语到一个 insert 语句。它应该跟 INTO_COLUMNS() 一起调用。

Since version 3.4.2, you can use variable-length arguments as follows:
从 3.4.2 版本开始，你可以使用像下面这样使用 variable-length 参数。

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

4 SqlBuilder 和 SelectBuilder（已废弃）

Before version 3.2 we took a bit of a different approach, by utilizing a ThreadLocal variable to mask some of the language limitations that make Java DSLs a bit cumbersome. However, this approach is now deprecated, as modern frameworks have warmed people to the idea of using builder-type patterns and anonymous inner classes for such things. Therefore the SelectBuilder and SqlBuilder classes have been deprecated.
在 3.2 版本之前，我们使用了一点不同的方法，通过使用一个 ThreadLocal 参数来掩饰一些使 Java DSL 变的笨重的语言限制。然而，这个方法现在已经废弃了，现在的框架都欢迎人们使用 builder-type 模式和匿名内部类的方式。因此，SelectBuilder 和 SqlBuilder 类被废弃了。

The following methods apply to only the deprecated SqlBuilder and SelectBuilder classes.
下面的方法仅适用于已经废弃的 SelectBuilder 和 SqlBuilder 类。

• BEGIN() /RESET()
  These methods clear the ThreadLocal state of the SelectBuilder class, and prepare it for a new statement to be built. BEGIN() reads best when starting a new statement. RESET() reads best when clearing a statement in the middle of execution for some reason (perhaps if the logic demands a completely different statement under some conditions).
  这些方法用来清空 SelectBuilder 类的 ThreadLocal 状态，并且为一个新的语句构建准备它。在开始一个新的语句时 BEGIN() 读取效果最好。 在执行期间因为一些原因（在某些条件下可能是逻辑上需要一个完全不同的语句）来清空一条语句时，RESET() 读取效果最好。

• SQL()
  This returns the generated SQL() and resets the SelectBuilder state (as if BEGIN() or RESET() were called). Thus, this method can only be called ONCE!
  返回生成的 SQL() 并重置 SelectBuilder 的状态（就像 BEGIN() 或 RESET()）。因此，这个方法只能被调用一次。

The SelectBuilder and SqlBuilder classes are not magical, but it's important to know how they work. SelectBuilder and SqlBuilder use a combination of Static Imports and a ThreadLocal variable to enable a clean syntax that can be easily interlaced with conditionals. To use them, you statically import the methods from the classes like this (one or the other, not both):
SelectBuilder 和 SqlBuilder 类并不神奇，但是知道他们是如何工作是很重要的。SelectBuilder 和 SqlBuilder 类使用静态导入和一个 ThreadLocal 参数的组合来开启可以很容易地同条件句交织的整洁语法。使用它们，你可以像下面这样静态导入类（两者取其一）：

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

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

这允许我们像这样来创建方法：

/* DEPRECATED */
public String selectBlogsSql() {
  BEGIN(); // 清空 ThreadLocal 变量
  SELECT("*");
  FROM("BLOG");
  return SQL();
}

/* DEPRECATED */
private String selectPersonSql() {
  BEGIN(); // 清空 ThreadLocal 变量
  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();
}

最后

说明：MyBatis 官网提供了简体中文的翻译，但个人觉得较为生硬，甚至有些地方逻辑不通，于是自己一个个重新敲着翻译的（都不知道哪里来的自信...），有些地方同官网翻译有出入，有些倔强地保留了自己的，有的实在别扭则保留了官网的，这些都会在实践中一一更正。鉴于个人英文能力有限，文章中保留了官方文档原英文介绍（个别地方加以调整修剪），希望有缘看到这里的朋友们能够有自己的理解，不会被我可能错误或不合理的翻译带跑偏(〃'▽'〃)，欢迎指正！

当前版本：mybatis-3.5.0
官网文档：MyBatis
官网翻译：MyBatis 简体中文
项目实践：MyBatis Learn