Mybatis3 Dynamic Sql实践
背景
最近在做项目的时候，需要用到多表关联查询，关联的表和查询的条件都是不确定的，且可能会有非常复杂的查询场景，导致查询条件会很复杂，在这种场景下，sql模版是不确定的，所以传统的MyBatis3风格(即经常用的xml风格)或者MyBatis3Simple风格的sql模版框架就显得力不从心，亟需一个更加灵活的动态sql框架，就在我一筹莫展的时候，Mybatis3 Dynamic Sql走进了我的视线。
前言
本文将首先介绍一下Mybatis3 Dynamic Sql是什么，有啥好处，怎么使用Mybatis Generator (MBG)来生成Mybatis3 Dynamic Sql风格的DAO层代码，以及怎么使用其来构建自己的sql语句，以及最后，当Mybatis3 Dynamic Sql现有功能不满足我们需求的时候，怎样去拓展它，实现诸如having语句、find_in_set函数、group_concat函数、甚至是ST_Distance_Sphere（ADS球面距离函数）等一些Mybatis3 Dynamic Sql暂不支持的sql语句或函数。
Mybatis3 Dynamic Sql简介
Mybatis3 Dynamic Sql是一个SQL模板库，它内置支持MyBatis和Spring JDBC template这两个O/R-M框架，生成的SQL可以直接在这两个框架中运行。
Mybatis3 Dynamic Sql的优势
- 不再生成XML映射文件，不需要支持"by example"能力，大量启用MyBatis3的注解，结合现代编码风格，总体代码量比传统运行时生成的代码小很多，也简单很多。
- 使用它生成的高级条件查询灵活性较大，并支持分页、join、union、group by、order by等语句，支持通过任意and/or及嵌套的条件组合构建复杂的where条件查询。
Mybatis Generator (MBG) 用法
MBG的用法以及相关的配置，这篇文章已经很详细了，https://www.cnblogs.com/ZhangZiSheng001/p/12820344.html，在此就不再赘述。
Mybatis3 Dynamic Sql使用方法
如果你数据库中表的DDL是类似这样的:
create table SimpleTable (
id int not null,
first_name varchar(30) not null,
last_name varchar(30) not null,
birth_date date not null,
employed varchar(3) not null,
occupation varchar(30) null,
primary key(id)
);
那么使用Mybatis Generator (MBG)之后，不会生成xml文件，而是会默认生成两个类，一个是support类，类似这样：
package examples.simple;
import java.sql.JDBCType;
import java.util.Date;
import org.mybatis.dynamic.sql.SqlColumn;
import org.mybatis.dynamic.sql.SqlTable;
public final class SimpleTableDynamicSqlSupport {
public static final SimpleTable simpleTable = new SimpleTable();
public static final SqlColumn<Integer> id = simpleTable.id;
public static final SqlColumn<String> firstName = simpleTable.firstName;
public static final SqlColumn<String> lastName = simpleTable.lastName;
public static final SqlColumn<Date> birthDate = simpleTable.birthDate;
public static final SqlColumn<Boolean> employed = simpleTable.employed;
public static final SqlColumn<String> occupation = simpleTable.occupation;
public static final class SimpleTable extends SqlTable {
public final SqlColumn<Integer> id = column("id", JDBCType.INTEGER);
public final SqlColumn<String> firstName = column("first_name", JDBCType.VARCHAR);
public final SqlColumn<String> lastName = column("last_name", JDBCType.VARCHAR);
public final SqlColumn<Date> birthDate = column("birth_date", JDBCType.DATE);
public final SqlColumn<Boolean> employed = column("employed", JDBCType.VARCHAR, "examples.simple.YesNoTypeHandler");
public final SqlColumn<String> occupation = column("occupation", JDBCType.VARCHAR);
public SimpleTable() {
super("SimpleTable");
}
}
}
该类可以理解为一个支持类，它的作用是可以很方便地拿到表中每个字段对应的SqlColumn，方便后面构建查询或插入语句。另外一个类就是mapper类，它通过注解的方式实现了数据库字段和类字段的映射，可以很方便地执行查询或者插入语句，如下：
package examples.simple;
import java.util.List;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
@Mapper
public interface SimpleTableAnnotatedMapper {
@InsertProvider(type=SqlProviderAdapter.class, method="insert")
int insert(InsertStatementProvider<SimpleTableRecord> insertStatement);
@UpdateProvider(type=SqlProviderAdapter.class, method="update")
int update(UpdateStatementProvider updateStatement);
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@Results(id="SimpleTableResult", value= {
@Result(column="A_ID", property="id", jdbcType=JdbcType.INTEGER, id=true),
@Result(column="first_name", property="firstName", jdbcType=JdbcType.VARCHAR),
@Result(column="last_name", property="lastName", jdbcType=JdbcType.VARCHAR),
@Result(column="birth_date", property="birthDate", jdbcType=JdbcType.DATE),
@Result(column="employed", property="employed", jdbcType=JdbcType.VARCHAR, typeHandler=YesNoTypeHandler.class),
@Result(column="occupation", property="occupation", jdbcType=JdbcType.VARCHAR)
})
List<SimpleTableRecord> selectMany(SelectStatementProvider selectStatement);
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@ResultMap("SimpleTableResult")
SimpleTableRecord selectOne(SelectStatementProvider selectStatement);
@DeleteProvider(type=SqlProviderAdapter.class, method="delete")
int delete(DeleteStatementProvider deleteStatement);
@SelectProvider(type=SqlProviderAdapter.class, method="select")
long count(SelectStatementProvider selectStatement);
}
有了这两个类，我们就可以通过Mybatis3 Dynamic Sql的风格构建自己的查询语句，并且交由mapper类执行了，如下：
@Test
public void testSelectByExample() {
try (SqlSession session = sqlSessionFactory.openSession()) {
SimpleTableAnnotatedMapper mapper = session.getMapper(SimpleTableAnnotatedMapper.class);
SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation)
.from(simpleTable)
.where(id, isEqualTo(1))
.or(occupation, isNull())
.build()
.render(RenderingStrategies.MYBATIS3);
List<SimpleTableRecord> rows = mapper.selectMany(selectStatement);
assertThat(rows.size()).isEqualTo(3);
}
}
同样地，也可以构建insert语句或者update语句：
SimpleTableRecord record = new SimpleTableRecord();
record.setId(100);
record.setFirstName("Joe");
record.setLastName("Jones");
record.setBirthDate(new Date());
record.setEmployed(true);
record.setOccupation("Developer");
InsertStatementProvider<SimpleTableRecord> insertStatement = insert(record)
.into(simpleTable)
.map(id).toProperty("id")
.map(firstName).toProperty("firstName")
.map(lastName).toProperty("lastName")
.map(birthDate).toProperty("birthDate")
.map(employed).toProperty("employed")
.map(occupation).toProperty("occupation")
.build()
.render(RenderingStrategies.MYBATIS3);
int rows = mapper.insert(insertStatement);
UpdateStatementProvider updateStatement = update(animalData)
.set(bodyWeight).equalTo(record.getBodyWeight())
.set(animalName).equalToNull()
.where(id, isIn(1, 5, 7))
.or(id, isIn(2, 6, 8), and(animalName, isLike("%bat")))
.or(id, isGreaterThan(60))
.and(bodyWeight, isBetween(1.0).and(3.0))
.build()
.render(RenderingStrategies.MYBATIS3);
int rows = mapper.update(updateStatement);
可以看出，由于Mybatis3 Dynamic Sql大量使用了Java8的函数、lambda表达式、接口默认方法实现等新特性，以及链式函数调用的风格，所以构建sql语句的过程中，代码的自解释性很强，基本符合我们平时书写sql的习惯，写Java代码的同时其实就是在写sql。这样做的好处是显而易见的，sql构建灵活并且代码可读性强，但是缺点也是有的，学习成本比较高，对于习惯了使用xml来配置sql模版的同学来说可能一开始会不太适应，但是其实只要使用了一段时间，就会感觉到这种方法确实是比xml的方式要便捷和灵活。
当然了，这里也并不是说这种方式在技术上会比xml的方式先进多少，而是提供了一种更加灵活地构建sql的选择，当你的查询条件很复杂的时候，当你需要多表join，并且join的表还都不确定的时候（运行时确定），当你无法通过xml构建出稳定的sql模版的时候，那么，或许你应该考虑这种方式了。
目前Mybatis3 Dynamic Sql还处于发展中的阶段，很多函数或者语句的支持还很有限，当这并不妨碍我们自己拓展并使用它，Mybatis3 Dynamic Sql的可拓展性还是很好的。下面举几个我在使用的过程中，拓展实现的一些函数或语句的例子。
拓展实现find_in_set函数
Mybatis3 Dynamic Sql目前并不支持find_in_set函数，但是通过阅读源码，发现要实现这个函数也并不难，只需要继承AbstractFunction这个类就行了，顾名思义，这个类是一个抽象函数类，接收一个column（列）作为参数。find_in_set函数实现如下：
public class FindInSet extends AbstractFunction<Object, FindInSet> {
private Object arg;
private FindInSet(Object arg, BindableColumn<Object> column) {
super(column);
this.arg = arg;
}
public static FindInSet of(Object arg, BindableColumn<Object> column) {
return new FindInSet(arg, column);
}
@Override
public String renderWithTableAlias(TableAliasCalculator tableAliasCalculator) {
if(arg instanceof String) {
//参数两边带引号
return "find_in_set(" + "\"" + securitySql(arg.toString()) + "\"," + this.column.renderWithTableAlias(tableAliasCalculator) + ")";
} else {
return "find_in_set(" + securitySql(arg.toString()) + "," + this.column.renderWithTableAlias(tableAliasCalculator) + ")";
}
}
/**
* 防止sql注入
* @param sql
* @return
*/
private static String securitySql(String sql) {
if(null == sql) {
return null;
}
return sql.replace(";", "");
}
@Override
protected FindInSet copy() {
return new FindInSet(arg, column);
}
}
拓展实现ADS的point及ST_Distance_Sphere函数
实现point函数类似，同样可以继承AbstractFunction：
public class Point extends AbstractFunction<Object, Point> {
private BindableColumn<Object> column1;
private BindableColumn<Object> column2;
public Point(BindableColumn<Object> column1, BindableColumn<Object> column2) {
super(Constant.of(""));
this.column1 = column1;
this.column2 = column2;
}
public static Point of(BindableColumn<Object> column1, BindableColumn<Object> column2) {
return new Point(column1, column2);
}
@Override
protected Point copy() {
return new Point(column1, column2);
}
@Override
public String renderWithTableAlias(TableAliasCalculator tableAliasCalculator) {
return "point(" + column1.renderWithTableAlias(tableAliasCalculator) +
"," + column2.renderWithTableAlias(tableAliasCalculator) + ")";
}
}
实现完point函数之后，就可以实现ST_Distance_Sphere函数了，以下是阿里云文档中关于ST_Distance_Sphere函数的说明：
实现ST_Distance_Sphere函数的方式同样可以继承AbstractFunction：
public class StDistanceSphere extends AbstractFunction<Object, StDistanceSphere> {
private Point point1;
private Point point2;
private Long radius;
public StDistanceSphere(Point point1, Point point2, Long radius) {
super(Constant.of(""));
this.point1 = point1;
this.point2 = point2;
this.radius = radius;
}
public StDistanceSphere(Point point1, Point point2) {
super(Constant.of(""));
this.point1 = point1;
this.point2 = point2;
}
public static StDistanceSphere of(Point point1, Point point2, Long radius) {
return new StDistanceSphere(point1, point2, radius);
}
public static StDistanceSphere of(Point point1, Point point2) {
return new StDistanceSphere(point1, point2);
}
public StDistanceSphere withRadius(Long radius) {
this.radius = radius;
return this;
}
@Override
protected StDistanceSphere copy() {
return new StDistanceSphere(point1, point2, radius);
}
@Override
public String renderWithTableAlias(TableAliasCalculator tableAliasCalculator) {
if(null == radius) {
return "ST_Distance_Sphere(" + point1.renderWithTableAlias(tableAliasCalculator) + ","
+ point2.renderWithTableAlias(tableAliasCalculator) + ")";
} else {
return "ST_Distance_Sphere(" + point1.renderWithTableAlias(tableAliasCalculator) + ","
+ point2.renderWithTableAlias(tableAliasCalculator) + "," + radius + ")";
}
}
}
拓展实现having语句
实现having语句的话，其实和Mybatis3 Dynamic Sql中实现where语句一样，其实在sql语法中，having语句和where类似，只不过可以在having语句中使用聚合函数罢了，具体实现的代码较多，不便贴上来。
结语
说了这么多，其实Mybatis3 Dynamic Sql还有很多不足的地方，比如前面说到的，不支持某些聚合函数、不支持having语句，不支持子查询等，但相信这些在不久的将来都会逐步完善起来，本文只是起一个抛砖引玉的作用，把这个项目介绍给大家，主要是本人在使用这个项目的过程中，觉得实在是挺好用的，而且在拓展一些sql语句的时候，也发现该项目拓展性极强，代码风格和设计也很好，确实是很值得学习的。目前Mybatis3 Dynamic Sql是github上的一个开源项目，主要由Jeff Butler大神维护，相关的文档和社区还不是很完善，也欢迎感兴趣的小伙伴去开源社区贡献一下自己的力量，让Mybatis3 Dynamic Sql越来越强大！
项目地址：https://github.com/mybatis/mybatis-dynamic-sql
官方文档：https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html
