Mybatis Dynamic SQL

1. 关于 Mybatis Dynamic SQL

官网地址是：Mybatis Dynamic SQL官网 (opens new window)。

首先要澄清的是，这里的『动态 SQL』并非之前的 mybatis mapper.xml 中的 if、foreach 那个『动态 SQL』，而是 Mybatis 官方的另一个项目，这个项目并不是为了取代 Mybatis ，而是为了让开发者更方便的使用 Mybatis ， 也就是说它只是 Mybatis 的一个补充。

Mybatis Dynamic SQL 是一个用于生成动态 SQL 语句的框架。简单来说，就是你在 Java 代码中调用特定的方法，而在这些方法背后，你实际上是 “拼” 出了一条 SQL 语句。当然，根据个人的 “审美” 的不同，有些人可能觉得这样毫无必要，而宁愿在配置文件中去编写 SQL 。这也无可厚非。

简单来说，注解的出现『干掉』了大量的 mapper.xml 文件，而 Mybatis Dynamic SQL 的出现就是为了『干掉』大量的 Example 对象，进一步简化代码。

2. 集成 Dynamic SQL

在 pom.xml 中添加如下依赖，对比之前使用 MBG（MyBatis Generator），仅仅多添加了 MyBatis 的动态 SQL 依赖；

<dependency>
    <groupId>org.mybatis.dynamic-sql</groupId>
    <artifactId>mybatis-dynamic-sql</artifactId>
    <version>1.2.1</version>
</dependency>

在执行 mybatis-generator 生成代码时，需要将 context 的 targetRuntime 属性更改为 MyBatis3DynamicSQL 。

runtime=MyBatis3DynamicSql
dao.type=ANNOTATEDMAPPER
dao.package=xxx.yyy.zzz.dao
po.package=xxx.yyy.zzz.dao.po
xml.package=mybatis/mapper

一切准备就绪，执行 mybatis-generator ，可以发现已经不再生成 mapper.xml 文件和 Example 类，取而代之的是生成了 DynamicSqlSupport 类。

3. SqlTable 和它的子类们

mybatis-generator 所生成的 “东西” 里面最关键的是生了一些名为 XxxDynamicSqlSupport 的工具类，而我们需要关注（未来会频繁涉及到）的是它们的内部类，例如：

public final class DepartmentDynamicSqlSupport {
  ...

  public static final class Department extends SqlTable {
    public final SqlColumn<Long> id = column("id", JDBCType.BIGINT);      // 字段名和字段类型
    public final SqlColumn<String> name = column("name", JDBCType.VARCHAR);
    public final SqlColumn<String> location = column("location", JDBCType.VARCHAR);

    public Department() {
      super("department");  // 表名
    }
  }
}

这些内部类都继承自 SqlTable 类。显而易见，从 SqlTable 这个名字来看，你大概就能猜到它和它的子类的作用：在 MyBatis Dynamic SQL 中，这些内部类就是用来映射表和表字段的。很显然。

• 这些内部类的无参构造方法中调用的父类构造方法时传递的字符串，就是对应着某张表的表名。

  例如上例中的 super("department") ；

• 这些内部类的各个属性，就是对应着某张表的字段名和字段类型。

  例如上例中的 id = column("id", JDBCType.BIGINT) 。

注意

不过，有点讨厌的是，这些 SqlTable 内部类会和你的 PO 类同名。为了避免不必要的麻烦，你可能需要改变一下两者中的某一个，以便于将它俩区分开。

你再仔细观察下 DepartmentDynamicSqlSupport 的源码，其实它做的事情就是 new 了一个 Department 对象作为静态属性（public static final） 再将它（和它的属性）暴露出去给我们（和 MyBatis Dynamic SQL）使用。

例如，MyBatis Generator 生成的 Mapper/Dao 接口中，有一个 selectList 属性，就用到了它们。

补充

既然说到了 Mapper/DAO 接口中的 selectList 属性，那么这里有一个和它类似的 “东西” ：SqlTable 的子类会从 SqlTable 那里继承到一个 allColumns() 方法，它所返回的 BasicColumn 可以用来代表 select * from ... 中的那个 * 。

4. 实现基本的 CRUD 操作

略。

5. SqlBuilder

import static org.mybatis.dynamic.sql.SqlBuilder.*;

SqlBuilder 是一个非常有用的类，使用它可以灵活地构建 SQL 语句的条件，一些常用的条件构建方法如下。

条件	例子	对应 SQL
Between	where(foo, isBetween(x).and(y))	where foo between ? and ?
Equals	where(foo, isEqualsTo(x))	where foo = ?
Greater Than	where(foo, isGreaterThan(x))	where foo > ?
In	where(foo, isIn(x, y))	where foo in (?, ?)
Like	where(foo, isLike(x))	where foo like ?
Not Equals	where(foo, isNotEqualsTo(x))	where foo <> ?
Null	where(foo, isNull())	where foo is null

6. 条件查询

实现思路

使用 SqlBuilder 类构建 StatementProvider，然后调用 Mapper 接口中的方法即可。

按用户名和状态查询后台用户并按创建时间降序排列为例。SQL 实现如下：

SELECT *
FROM employee
WHERE department_id = 2
AND salary BETWEEN 500 AND 3000
ORDER BY salary DESC;

在使用 Dynamic SQL 来实现上述 SQL 语句时，你会发现你所调用的 Dao 的 select 方法接收 2 种类型的参数：SelectStatementProvider 和 SelectDSLCompleter 。

也就是说，你有 2 种方式、风格来『描述』你心里想执行的 SQL 语句。Provider 的写法更像 SQL 语句，对于熟悉 SQL 语句的 Dynamic SQL 的初学者来说，更容易理解；Completer 的写法更简洁。

使用 SelectStatementProvider 构建 Dynamic SQL 。使用 SqlBuilder 的 select 方法可以指定查询列，使用 from 方法可以指定查询表，使用 where 方法可以构建查询条件，使用 orderBy 方法可以指定排序。

import static org.mybatis.dynamic.sql.SqlBuilder.*;   // .isEqualTo(), .isBetween(), ...
import static xxx.yyy.zzz.dao.EmployeeDynamicSqlSupport.*;  // .employee, .departmentId, .salary, ...

// PageHelper.startPage(pageNum, pageSize);
SelectStatementProvider provider = SqlBuilder
      .select(EmployeeDao.selectList)
      .from(employee) 
      .where(departmentId, isEqualTo(2L))
      .and(salary, isBetween(500).and(3000))
      .orderBy(salary.descending())
      .build().render(RenderingStrategies.MYBATIS3);

employeeDao.selectMany(provider).forEach(System.out::println);

使用 SelectDSLCompleter 接口，实现它，或使用等价的 lambda 表达式。Completer 写法要比 Provider 写法 “省” 两三行代码。

import static org.mybatis.dynamic.sql.SqlBuilder.*;   // .isEqualTo(), .isBetween(), ...
import static xxx.yyy.zzz.dao.EmployeeDynamicSqlSupport.*;  // .departmentId, .salary, ...

// PageHelper.startPage(pageNum, pageSize);         
SelectDSLCompleter completer = c -> c
        .where(departmentId, isEqualTo(2L))
        .and(salary, isBetween(500).and(3000))
        .orderBy(salary.descending());

employeeDao.select(completer).forEach(System.out::println); 

SelectDSLCompleter 的底层最终还是使用的是 Provider ，即 Provider 才是根本。

当你拥有一个 Completer 对象时，你可以使用类似如下方式获得对应的 Provider 对象：

SelectDSLCompleter completer = ...;

SelectStatementProvider provider = completer.apply(SqlBuilder.select(selectList).from(employee))
                .build().render(RenderingStrategies.MYBATIS3);

注意

SelectDSLCompleter 写法要比 SelectStatementProvider 写法简洁，因为它省略掉了关于查询的列（即 SQL 语句中 select ... 的这一部分的 ）设置。

在简单情况下，你所执行的 SQL 语句可能就是 select * ，或者是 select 所有列 这种逻辑，但是对于有些情况，比如关联查询，SelectDSLCompleter 省略掉这一部分之后，返回会让你对这部分无法设置，从而拿不到你预期的结果。

所有，优先建议大家使用 Provider 。或者，使用 Completer ，然后在必要的时候转成 Provider 使用。

7. 逻辑条件的组合

逻辑条件的组合大体分为 2 种：

• 单纯的 ...与...与... / ...或...或...

• 与或 混用，由于 或 的优先级更高，因此可以改造成 (... and ...) or (... and ...) or ... 这样的统一形式。

import static org.mybatis.dynamic.sql.SqlBuilder.*;
import static xxx.yyy.zzz.dao.EmployeeDynamicSqlSupport.*;

// Provider 写法
SelectStatementProvider provider = SqlBuilder
        .select(EMPLOYEE.allColumns())
        .from(employee)
        .where()
        .and(departmentId, isEqualTo(2L))
        .and(salary, isBetween(500).and(3000))
        .orderBy(salary.descending())
        .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
        .where()
        .and(departmentId, isEqualTo(2L))
        .and(salary, isLessThan(1300))
        .and(...)
;

另外，你可以把第一个条件『纳入』到 where() 中，从而写成

import static org.mybatis.dynamic.sql.SqlBuilder.*;
import static xxx.yyy.zzz.dao.EmployeeDynamicSqlSupport.*;

// Provider 写法
SelectStatementProvider provider = SqlBuilder.select(EMPLOYEE.allColumns()).from(EMPLOYEE)
        .where(departmentId, isEqualTo(2L))
        .and(salary, isBetween(500).and(3000))
        .orderBy(salary.descending())
        .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
        .where(departmentId, isEqualTo(2L))
        .and(salary, isLessThan(1300))
        .and(...)
;

除了上述的『平行』地写法外，你还可以将 and() 方法嵌入到 and() 方法中，写成形如：.and(..., and(...), and(...), ...) 的形式：

// Provider 写法
SelectStatementProvider provider = SqlBuilder.select(EMPLOYEE.allColumns()).from(EMPLOYEE)
        .where(departmentId, isEqualTo(2L), and(salary, isBetween(500).and(3000)))
        .orderBy(salary.descending())
        .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
        .where()
        .and(departmentId, isEqualTo(2L), and(salary, isLessThan(1300)), and(...))
;

// Provider 写法
SelectStatementProvider provider = SqlBuilder.select(EMPLOYEE.allColumns()).from(EMPLOYEE)
          .where()
          .or(salary, isLessThan(1000))
          .or(commission, isNotNull())
          .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
          .where()
          .or(salary, isLessThan(1000))
          .or(commission, isNotNull())
          .or(...)
;

和 ...与...与... 情况一样，你可以把第一个条件『纳入』到 where() 中，从而写成

// Provider 写法
SelectStatementProvider provider = SqlBuilder.select(EMPLOYEE.allColumns()).from(EMPLOYEE)
          .where(salary, isLessThan(1000))
          .or(commission, isNotNull())
          .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
          .where(salary, isLessThan(1000))
          .or(commission, isNotNull())
          .or(...)
;

和 与与 情况一样，除了上述的『平行』地写法外，你也可以将 or() 方法嵌入到 or() 方法中，写成形如：.or(..., or(...), or(...), ...) 的形式：

// Provider 写法
SelectStatementProvider provider = SqlBuilder.select(EMPLOYEE.allColumns()).from(EMPLOYEE)
        .where(salary, isLessThan(1000), or(commission, isGreaterThan(200)))
        .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
        .where(salary, isLessThan(1000), or(commission, isNotNull()), or(...))
;

与或 混用的情况下，先要把你『心里』的 SQL 语句改造成通用形式：(... and ...) or (... and ...) or ... 。

// Provider 写法
SelectStatementProvider provider = SqlBuilder.select(EMPLOYEE.allColumns()).from(employee)
        .where()
        .or(departmentId, isEqualTo(2L), and(salary, isLessThan(1500)))
        .or(departmentId, isEqualTo(3L), and(salary, isGreaterThan(1300)))
        .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
        .where()
        .or(departmentId, isEqualTo(2L), and(salary, isLessThan(1500)))
        .or(departmentId, isEqualTo(3L), and(salary, isGreaterThan(1300)));

一样，你也可以将第一个条件纳入到 where() 中。

// Provider 写法
SelectStatementProvider provider = SqlBuilder.select(EMPLOYEE.allColumns()).from(employee)
        .where(departmentId, isEqualTo(2L), and(salary, isLessThan(1500)))
        .or(departmentId, isEqualTo(3L), and(salary, isGreaterThan(1300)))
        .build().render(RenderingStrategies.MYBATIS3);

// Completer 写法
SelectDSLCompleter completer = c -> c
        .where(departmentId, isEqualTo(2L), and(salary, isLessThan(1500)))
        .or(departmentId, isEqualTo(3L), and(salary, isGreaterThan(1300)));

8. 条件删除

TIP

使用 Dynamic SQL 实现条件删除，直接调用 Mapper 接口中生成好的 delete 方法即可。

我们『心里』期望执行的 SQL 如下：

DELETE  FROM  department WHERE  name = 'test'; 

使用 Dynamic SQL 对应 Java 中的实现如下：

DeleteStatementProvider provider = SqlBuilder.deleteFrom(DEPARTMENT)
        .where(DEPARTMENT.name, isEqualTo("test"))
        .build().render(RenderingStrategies.MYBATIS3)
;

DeleteDSLCompleter completer = c -> c
        .where(DEPARTMENT.name, isEqualTo("test"))
;

departmentDao.delete(provider);

9. 条件修改

TIP

使用 Dynamic SQL 实现条件修改，直接调用 Mapper 接口中生成好的update方法即可。

我们『心里』期望执行的 SQL 如下：

update
    department
set name     = 'hello',
    location = 'world'
where id = 5;

使用 Dynamic SQL 对应 Java 中的实现如下：

// Provider 写法
UpdateStatementProvider provider = SqlBuilder.update(DEPARTMENT)
        .set(DEPARTMENT.name).equalTo("hello")
        .set(DEPARTMENT.location).equalTo("world")
        .where(DEPARTMENT.id, isEqualTo(5L))
        .build().render(RenderingStrategies.MYBATIS3);
;

// Completer 写法
UpdateDSLCompleter completer = c -> c
        .set(DEPARTMENT.name).equalTo("hello")
        .set(DEPARTMENT.location).equalTo("world")
        .where(DEPARTMENT.id, isEqualTo(5L))
; 

departmentDao.update(completer);     

9. 关联查询：select 方案

略。

10. group 和 join 查询

TIP

涉及到多表查询，之前使用 mybatis-generator 的时候基本只能在 mapper.xml 中手写 SQL 实现，使用 Dynamic SQL 可以支持多表查询。

我们『心里』期望执行的 SQL 如下：

-- 查询所有部门信息（部门信息中包含该部门下的员工数量）
select department.id, department.name, department.location, count(employee.id) as employee_quantity
from department
    left join employee on department.id = employee.department_id
group by department.id;

现在 mapper.xml 中定义好映射规则：

<resultMap id="selectDepartmentWithEmployeeQuantityResultMap" type="com.woniu.mybatisdynamicsqlsample.dao.po.Department">
    <id column="id" jdbcType="BIGINT" property="id"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <result column="location" jdbcType="VARCHAR" property="location"/>
    <result column="employee_quantity" jdbcType="INTEGER" property="employeeQuantity"/>
</resultMap>

先在 Dao 中添加一个 selectDepartmentWithEmployeeQuantity 方法，然后使用 @ResultMap 注解引用定义好结果集映射规则；

public interface UmsAdminDao {     
    @SelectProvider(type = SqlProviderAdapter.class, method = "select")     
    @ResultMap("selectDepartmentWithEmployeeQuantityResultMap")
    List<Department> selectDepartmentWithEmployeeQuantity() {
} 

然后在 Service 中调用它，StatementProvider 即可，对应的 Java 代码实现如下：

BasicColumn[] selectList = BasicColumn.columnList(DEPARTMENT.id, DEPARTMENT.name, DEPARTMENT.location, SqlBuilder.count(EMPLOYEE.id).as("employee_quantity"));
SelectStatementProvider provider = SqlBuilder.select(selectList)
        .from(DEPARTMENT)
        .leftJoin(EMPLOYEE).on(DEPARTMENT.id, equalTo(EMPLOYEE.departmentId))
        .groupBy(DEPARTMENT.id)
        .build().render(RenderingStrategies.MYBATIS3);

departmentDao.selectDepartmentWithEmployeeQuantity(provider).forEach(System.out::println);

考虑到 Dao 中有自动生成的 selectOne 和 selectMany 可以供我们使用，所以，我们的 selectDepartmentWithEmployeeQuantity 方法可以去调用它们（从而将 provider/completer 参数挪到方法中，而不是从外部传入）。

改造 dao 接口中的 selectDepartmentWithEmployeeQuantity 方法：

default List<Department> selectDepartmentWithEmployeeQuantity() {
    BasicColumn[] selectList = BasicColumn.columnList(id, name, location, SqlBuilder.count(EMPLOYEE.id).as("employee_quantity"));
    SelectStatementProvider provider = SqlBuilder.select(selectList)
            .from(DEPARTMENT)
            .leftJoin(EMPLOYEE).on(id, equalTo(EMPLOYEE.departmentId))
            .groupBy(id)
            .build().render(RenderingStrategies.MYBATIS3);

    return selectMany(provider);
}

注意：现在真正在『干活』的就是 selectMany 了，所以，我们要将 @ResultMap("selectDepartmentWithEmployeeQuantityResultMap") 注解标注在 selectMany 上，只有这样，selectMany 才能正确映射结果集。

@SelectProvider(type = SqlProviderAdapter.class, method = "select")
@ResultMap("selectDepartmentWithEmployeeQuantityResultMap") // 看这里，看这里，看这里
List<Department> selectMany(SelectStatementProvider selectStatement)

这样，调用 selectDepartmentWithEmployeeQuantity 方法之处，就不需要传参了：

departmentDao.selectDepartmentWithEmployeeQuantity().forEach(System.out::println);

11. 关联查询：association 方案

SelectStatementProvider provider = SqlBuilder.select(employee.allColumns(),
        department.id.as("did"),
        department.name.as("dname"),
        department.location)
        .from(employee)
        .leftJoin(department).on(employee.departmentId, equalTo(department.id))
        .where(employee.salary, isGreaterThan(salary))
        .and(department.name, isEqualTo(name))
        .build().render(RenderingStrategies.MYBATIS3);

System.out.println( provider.getSelectStatement() );

略

命名示例：

Keyword	Sample	SQL 部分
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is, Equals	findByFirstnameIs, findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull	findByAgeIsNull	… where x.age is null
IsNotNull, NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection age)	… where x.age not in ?1
TRUE	findByActiveTrue()	… where x.active = true
FALSE	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)