Mybatis Dynamic SQL

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>

Copied!

在执行 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

Copied!

一切准备就绪，执行 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");  // 表名
    }
  }
}

Copied!

这些内部类都继承自 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.*;

Copied!

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;

Copied!

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

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

方式一：SelectStatementProvider

使用 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

使用 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); 

 

Completer 转 Provider

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

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

SelectDSLCompleter completer = ...;

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

 

 

Copied!

注意

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

Copied!

 

缩写

另外，你可以把第一个条件『纳入』到 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(...)
;

Copied!

缩写

和 ...与...与... 情况一样，你可以把第一个条件『纳入』到 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)));

Copied!

 

缩写

一样，你也可以将第一个条件纳入到 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)));

 

Copied!

 

#8. 条件删除

TIP

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

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

DELETE  FROM  department WHERE  name = 'test'; 

Copied!

使用 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);

Copied!

#9. 条件修改

TIP

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

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

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

Copied!

使用 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);     

Copied!

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

Copied!

现在 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>

Copied!

初步方案

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

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

Copied!

然后在 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);

Copied!

 

改进方案

 

考虑到 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);
}

Copied!

 

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

Copied!

略

命名示例：

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)