MyBatis 教程
MyBatis 教程
阅读(116.3k) 书签 赞(14) 我要纠错

MyBatis SQL语句构建器

2022-05-07 17:35 更新

SQL语句构建器

问题

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

解决方案

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类

这里给出一些示例：

    // 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 子句。 参数可以包含一个由列和连接条件构成的标准连接。
WHERE(String) 插入新的 WHERE 子句条件，并使用 AND 拼接。可以被多次调用，对于每一次调用产生的新条件，会使用 AND 拼接起来。要使用 OR 分隔，请使用 OR()。
OR() 使用 OR 来分隔当前的 WHERE 子句条件。 可以被多次调用，但在一行中多次调用会生成错误的 SQL。
AND() 使用 AND 来分隔当前的 WHERE子句条件。 可以被多次调用，但在一行中多次调用会生成错误的 SQL。由于 WHERE 和 HAVING都会自动使用 AND 拼接, 因此这个方法并不常用，只是为了完整性才被定义出来。
GROUP_BY(String) 追加新的 GROUP BY 子句，使用逗号拼接。可以被多次调用，每次调用都会使用逗号将新的条件拼接起来。
HAVING(String) 追加新的 HAVING 子句。使用​ AND​ 拼接。可以被多次调用，每次调用都使用AND来拼接新的条件。要使用 OR 分隔，请使用 OR()。
ORDER_BY(String) 追加新的 ORDER BY 子句，使用逗号拼接。可以多次被调用，每次调用会使用逗号拼接新的条件。
LIMIT(String)``LIMIT(int) 追加新的 LIMIT 子句。 仅在 ​SELECT()​、​UPDATE()​、​DELETE()​ 时有效。 当在 ​SELECT()​ 中使用时，应该配合 ​OFFSET()​ 使用。（于 3.5.2 引入）
OFFSET(String)``OFFSET(long) 追加新的 OFFSET 子句。 仅在 ​SELECT()​ 时有效。 当在 ​SELECT()​ 时使用时，应该配合 ​LIMIT()​ 使用。（于 3.5.2 引入）
OFFSET_ROWS(String)``OFFSET_ROWS(long) 追加新的 OFFSET n ROWS 子句。 仅在 ​SELECT()​ 时有效。 该方法应该配合 ​FETCH_FIRST_ROWS_ONLY()​ 使用。（于 3.5.2 加入）
FETCH_FIRST_ROWS_ONLY(String)``FETCH_FIRST_ROWS_ONLY(int) 追加新的 FETCH FIRST n ROWS ONLY 子句。 仅在 ​SELECT()​ 时有效。 该方法应该配合 ​OFFSET_ROWS()​ 使用。（于 3.5.2 加入）
DELETE_FROM(String) 开始新的 ​delete​ 语句，并指定删除表的表名。通常它后面都会跟着一个​ WHERE​ 子句！
INSERT_INTO(String) 开始新的 ​insert​ 语句，并指定插入数据表的表名。后面应该会跟着一个或多个 ​VALUES() ​调用，或 ​INTO_COLUMNS()​ 和 ​INTO_VALUES()​ 调用。
SET(String) 对 ​update​ 语句追加 ​"set"​ 属性的列表
UPDATE(String) 开始新的 ​update​ 语句，并指定更新表的表名。后面都会跟着一个或多个 ​SET()​ 调用，通常也会有一个 ​WHERE()​ 调用。
VALUES(String, String) 追加数据值到 ​insert​ 语句中。第一个参数是数据插入的列名，第二个参数则是数据值。
INTO_COLUMNS(String...) 追加插入列子句到 ​insert​ 语句中。应与 ​INTO_VALUES()​ 一同使用。
INTO_VALUES(String...) 追加插入值子句到 ​insert​ 语句中。应与 ​INTO_COLUMNS()​ 一同使用。
ADD_ROW() 添加新的一行数据，以便执行批量插入。（于 3.5.2 引入）

提示 注意，SQL 类将原样插入 ​LIMIT​、​OFFSET​、​OFFSET n ROWS​ 以及 ​FETCH FIRST n ROWS ONLY ​子句。换句话说，类库不会为不支持这些子句的数据库执行任何转换。 因此，用户应该要了解目标数据库是否支持这些子句。如果目标数据库不支持这些子句，产生的 SQL 可能会引起运行错误。


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

SqlBuilder 和 SelectBuilder (已经废弃)

在3.2版本之前，我们使用了一点不同的做法，通过实现​ThreadLocal​变量来掩盖一些导致​Java DSL​麻烦的语言限制。但这种方式已经废弃了，现代的框架都欢迎人们使用构建器类型和匿名内部类的想法。因此，​SelectBuilder​ 和 ​SqlBuilder​ 类都被废弃了。

下面的方法仅仅适用于废弃的​SqlBuilder​ 和 ​SelectBuilder​ 类。

方法 描述
BEGIN() / RESET() 这些方法清空​SelectBuilder​类的​ThreadLocal​状态，并且准备一个新的构建语句。开始新的语句时， BEGIN()读取得最好。 由于一些原因（在某些条件下，也许是逻辑需要一个完全不同的语句），在执行中清理语句 RESET()读取得最好。
SQL() 返回生成的 SQL() 并重置 SelectBuilder 状态 (好像 BEGIN() 或 RESET() 被调用了). 因此，该方法只能被调用一次！

​SelectBuilder​ 和 ​SqlBuilder​ 类并不神奇，但是知道它们如何工作也是很重要的。​ SelectBuilder​ 使用 ​SqlBuilder ​使用了静态导入和​ThreadLocal​变量的组合来开启整洁语法，可以很容易地和条件交错。使用它们，静态导入类的方法即可，就像这样(一个或其它，并非两者):

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

这就允许像下面这样来创建方法：

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


以上内容是否对您有帮助：
← MyBatis Java API
MyBatis 日志 →

推荐文章

推荐教程

推荐课程

在线笔记
App下载
App下载

扫描二维码

下载编程狮App

公众号
微信公众号

编程狮公众号