MyBatis3 DynamicSql风格语法使用指南

Posted by JJput on 01-21,2021

--2021.07.17更新

主要演示DynamicSql风格代码如何使用,基本能应对大部分使用场景。DynamicSql基本介绍点我查看。

本文主要沿着的思路进行介绍,尽量涵盖日常使用所需。

我这里还是要推荐一下大家看官方文档,尽量有问题先找官方文档教程,除非写的跟屎一样,但大概率不会。

本次使用的是mybatis-dynamic-sql1.2.1版本

<!-- 集成mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>
<!-- MyBatis 生成器 -->
<dependency>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-core</artifactId>
    <version>1.4.0</version>
</dependency>
<!-- MyBatis 动态SQL支持 -->
<dependency>
    <groupId>org.mybatis.dynamic-sql</groupId>
    <artifactId>mybatis-dynamic-sql</artifactId>
    <version>1.2.1</version>
</dependency>

SELECT

查询我尽量贴上SQL语句对照着java代码,方便读者阅读和理解。

而且基本都实际运行过,确保没有问题。

列操作

指定列

SQL

SELECT 
	id,label,value 
FROM 
	sys_dict

Java代码

import static com.twj.spirngbasics.server.mapper.SysDictDynamicSqlSupport.*;	//注意导入对应DynamicSqlSupport包的静态属性

SelectStatementProvider selectStatement = SqlBuilder.select(id, label, value)
        .from(sysDict)
        .build()
        .render(RenderingStrategies.MYBATIS3);
List<SysDict> test = sysDictMapper.selectMany(selectStatement);

下面完全等价于上面代码,推荐上方写法,代码更整洁。

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictDynamicSqlSupport.id, SysDictDynamicSqlSupport.label, SysDictDynamicSqlSupport.value)
        .from(SysDictDynamicSqlSupport.sysDict)
        .build()
        .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

可以看到DynamicSql的使用结构完全与sql语句一样,真香。

AS与函数

目前函数只支持avg, min, max, sum

SQL

SELECT 
	id,label.as("name"),MIN(value)
FROM 
	sys_dict
ORDER BY 
	sort ASC

Java代码

SelectStatementProvider selectStatement = SqlBuilder.select(id,label.as("name"),SqlBuilder.max(value),sort)
        .from(SysDictDynamicSqlSupport.sysDict)
	.orderBy(sort)
        .build()
        .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

查询所有列

SQL

SELECT 
	id,label,value,sort.......
FROM 
	sys_dict

Java代码

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)
        .from(SysDictDynamicSqlSupport.sysDict)
        .build()
        .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

WHERE

SQL

SELECT
	* 
FROM
	sys_dict 
WHERE
	label = '男' 
	OR label = '女' 
ORDER BY
	`value` ASC

java代码

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)
        .from(SysDictDynamicSqlSupport.sysDict)
        .where(label, SqlBuilder.isEqualTo("男"))
        .or(label,SqlBuilder.isEqualTo("女"))
        .orderBy(value)
        .build()
        .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

java这里稍微注意一下,isEqualTo的包引用路径是在org.mybatis.dynamic.sql.SqlBuilder包下,可以像之前一样import static org.mybatis.dynamic.sql.SqlBuilder.*;引入所有静态方法。

排序:

  • 升序:默认MySQL可以不加ASC即为升序排序,DynamicSql也是如此,指定列即可;
  • 降序:调用descending()即可,以上方例子为例,原orderBy(value)改为orderBy(value.descending())即可。

SQL

SELECT
	* 
FROM
	sys_dict 
WHERE
	label IN ( '女', '男' ) 
ORDER BY
	`value`

java代码

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)
        .from(SysDictDynamicSqlSupport.sysDict)
        .where(label, SqlBuilder.isIn("女", "男"))
        .orderBy(value)
        .build()
        .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

where条件查询还有很多我就不列举了,附上官方表格:

ConditionExampleResult
Betweenwhere(foo, isBetween(x).and(y))where foo between ? and ?
Equalswhere(foo, isEqualTo(x))where foo = ?
Greater Thanwhere(foo, isGreaterThan(x))where foo > ?
Greater Than or Equalswhere(foo, isGreaterThanOrEqualTo(x))where foo >= ?
Inwhere(foo, isIn(x, y))where foo in (?,?)
In (case insensitive)where(foo, isInCaseInsensitive(x, y))where upper(foo) in (?,?) (the framework will transform the values for x and y to upper case)
Less Thanwhere(foo, isLessThan(x))where foo < ?
Less Than or Equalswhere(foo, isLessThanOrEqualTo(x))where foo <= ?
Likewhere(foo, isLike(x))where foo like ? (the framework DOES NOT add the SQL wild cards to the value - you will need to do that yourself)
Like (case insensitive)where(foo, isLikeCaseInsensitive(x))where upper(foo) like ? (the framework DOES NOT add the SQL wild cards to the value - you will need to do that yourself, the framework will transform the value of x to upper case)
Not Betweenwhere(foo, isNotBetween(x).and(y))where foo not between ? and ?
Not Equalswhere(foo, isNotEqualTo(x))where foo <> ?
Not Inwhere(foo, isNotIn(x, y))where foo not in (?,?)
Not In (case insensitive)where(foo, isNotInCaseInsensitive(x, y))where upper(foo) not in (?,?) (the framework will transform the values for x and y to upper case)
Not Likewhere(foo, isLike(x))where foo not like ? (the framework DOES NOT add the SQL wild cards to the value - you will need to do that yourself)
Not Like (case insensitive)where(foo, isNotLikeCaseInsensitive(x))where upper(foo) not like ? (the framework DOES NOT add the SQL wild cards to the value - you will need to do that yourself, the framework will transform the value of x to upper case)
Not Nullwhere(foo, isNotNull())where foo is not null
Nullwhere(foo, isNull())where foo is null

子查询

SQL

SELECT
	* 
FROM
	user_resource 
WHERE
	id IN (
	SELECT
		resource_id 
	FROM
		user_role_resource 
	WHERE
	role_id = '1' 
	)

java代码

SelectStatementProvider selectStatement = SqlBuilder.select(userResourceMapper.selectList)
        .from(UserResourceDynamicSqlSupport.userResource)
        .where(UserResourceDynamicSqlSupport.id, SqlBuilder.isIn(
                select(UserRoleResourceDynamicSqlSupport.resourceId)
                        .from(UserRoleResourceDynamicSqlSupport.userRoleResource)
                        .where(UserRoleResourceDynamicSqlSupport.roleId, SqlBuilder.isEqualTo("1"))))
        .build()
        .render(RenderingStrategies.MYBATIS3);
List<UserResource> list = userResourceMapper.selectMany(selectStatement);

子查询条件还有很多,附上官方表格:

ConditionExampleResult
Equalswhere(foo, isEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))where foo = (select bar from table2 where bar = ?)
Greater Thanwhere(foo, isGreaterThan(select(bar).from(table2).where(bar, isEqualTo(x)))where foo > (select bar from table2 where bar = ?)
Greater Than or Equalswhere(foo, isGreaterThanOrEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))where foo >= (select bar from table2 where bar = ?)
Inwhere(foo, isIn(select(bar).from(table2).where(bar, isLessThan(x)))where foo in (select bar from table2 where bar < ?)
Less Thanwhere(foo, isLessThan(select(bar).from(table2).where(bar, isEqualTo(x)))where foo < (select bar from table2 where bar = ?)
Less Than or Equalswhere(foo, isLessThanOrEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))where foo <= (select bar from table2 where bar = ?)
Not Equalswhere(foo, isNotEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))where foo <> (select bar from table2 where bar = ?)
Not Inwhere(foo, isNotIn(select(bar).from(table2).where(bar, isLessThan(x)))where foo not in (select bar from table2 where bar < ?)

根据业务逻辑添加条件

详细看代码

QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder builder = SqlBuilder.select(SysDictMapper.selectList)
        .from(SysDictDynamicSqlSupport.sysDict)
        .where();

if (x)
    builder.where(label, isIn("女", "男"));

if (y)
    builder.where(row,...);

SelectStatementProvider selectStatement = builder.build().render(RenderingStrategies.MYBATIS3);

List<SysDict> list = sysDictMapper.selectMany(selectStatement);

JOIN

我这里简单做一个示范,很多时候我们表中记录着都是用户id,用户表user记录着用户的详细属性,下面info_test表中有createdBy字段对应着user表中的id字段。
现在查询info_test表,返回idcreatedTime创建用户的名称

SQL

SELECT 
	info_test.id,
	info_test.createTime,
	user.name
FROM
	info_test
	LEFT JOIN user ON user.id = info_test.createdBy;
	

java代码

SelectStatementProvider selectStatement  = SqlBuilder.select(InfoTestDynamicSqlSupport.id,InfoTestDynamicSqlSupport.createdTime,UserDynamicSqlSupport.name)
                .from(InfoTestDynamicSqlSupport.infoTest)
                .leftJoin(UserDynamicSqlSupport.user)
                .on(UserDynamicSqlSupport.id, SqlBuilder.equalTo(InfoTestDynamicSqlSupport.createdBy))
	.build().render(RenderingStrategies.MYBATIS3)
List<InfoTest> infoTestList = InfoTestMapper.selectManyJoinUser(selectStatement);

java代码和SQL语句基本一致,最后重点是查询返回后的数据如何赋予实体对象的属性上。
重点在selectManyJoinUser方法,这是自行创建的,可以进入InfoTestMapper中看到mybatis事先帮我们创建了很多方法,之前的演示示例中都是使用默认的selectMany,显然在连接查询或者更复杂的查询中,selectMany已经无法满足我们的需求了,这时候需要自行copy创建一个新的方法,如下所示。

@Generated("org.mybatis.generator.api.MyBatisGenerator")
@SelectProvider(type = SqlProviderAdapter.class, method = "select")
@Results(id = "InfoTestGroupResult2", value = {
        @Result(column = "id", property = "id", jdbcType = JdbcType.VARCHAR, id = true),
        @Result(column = "name", property = "name", jdbcType = JdbcType.VARCHAR),
        @Result(column = "created_time", property = "createdTime", jdbcType = JdbcType.TIMESTAMP) 
})
List<InfoTest> selectManyJoinUser(SelectStatementProvider selectStatement);

这边需要注意几点:

  1. @Results(id="xxxxx"),这里的id一定要修改并且必须保证唯一性;
  2. @Result(column = "name", property = "name", jdbcType = JdbcType.VARCHAR),其中column为sql语句返回的列名,property对应要赋予对象的属性名。

目前支持四种连接类型:

  1. .join(...) 内连接
  2. .leftJoin(...) 左外连接
  3. .rightJoin(...) 右外连接
  4. .fullJoin(...) 全连接

INSERT

INSERT ONE

新增单条数据

SysDict sysDict = new SysDict();
sysDict.setLabel("测试");
sysDict.setValue("0");
sysDict.setType("test");
sysDict.setSort(0);
sysDict.setDescription("测试");
sysDict.insert("SYSTEM");
int row = sysDictMapper.insert(sysDict);
System.out.println("成功插入条数:" + row);

INSERT LIST

批量新增

List<SysDict> list = new ArrayList<>();
for (int i = 1; i < 10; i++) {
    SysDict sysDict = new SysDict();
    sysDict.setLabel("测试");
    sysDict.setValue(String.valueOf(i));
    sysDict.setType("test");
    sysDict.setSort(i);
    sysDict.setDescription("测试");
    sysDict.insert("SYSTEM");
    list.add(sysDict);
}

int rows = sysDictMapper.insertMultiple(list);
System.out.println("成功插入条数:" + rows);

DELETE

//根据主键删除
sysDictMapper.deleteByPrimaryKey("");

//条件删除
DeleteStatementProvider deleteStatement = deleteFrom(SysDictDynamicSqlSupport.sysDict)
        .where(SysDictDynamicSqlSupport.type, isEqualTo("test"))
        .build()
        .render(RenderingStrategies.MYBATIS3);
sysDictMapper.delete(deleteStatement);

UPDATE

常用的简单更新主要是下面两种:

//根据主键对所有属性进行更新
sysDictMapper.updateByPrimaryKey(sysDict);
//根据主键对不为null的属性进行更新
sysDictMapper.updateByPrimaryKeySelective(sysDict);

根据条件进行更新

UpdateStatementProvider updateStatement = update(SysDictDynamicSqlSupport.sysDict)
        .set(remake).equalToNull()
        .where(type, isEqualTo("test"))
        .build()
        .render(RenderingStrategies.MYBATIS3);

int rows = sysDictMapper.update(updateStatement);
System.out.println("成功更新条数:" + rows);

注意set方法,常用的方法有以下:

  1. set(column).equalToNull() 将对应列更新为null;
  2. set(column).equalTo(T value)将对应列更新为value;
  3. set(column).equalToWhenPresent(T value)如果value不能null的话更新列;
  4. set(column).equalTo(BasicColumn rightColumn)将一列的值设置为另一列的值,还可以对其加,减等操作。