浅谈MyBatis3 DynamicSql风格语法使用指南

浅谈MyBatis3 DynamicSql风格语法使用指南

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

本文主要沿着增、删、改、查的思路进行介绍，尽量涵盖日常使用所需。

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

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

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.1.3

mysql

mysql-connector-java

8.0.22

org.mybatis.generator

mybatis-generator-core

1.4.0

org.mybatis.dynamic-sql

mybatis-dynamic-sql

1.2.1

查

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

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

查询指定列

SELECT

id,label,value

FROM

sys_dict

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

SelectStatementProvider selectStatement = SqlBuilder.select(id, label, value)

.from(sysDict)

.build()

.render(RenderingStrategies.MYBATIS3);

List test = sysDictMapper.selectMany(selectStatement);

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

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictDynamicSqlSupport.id, SysDictDynamicSqlSupport.label, SysDictDynamicSqlSupport.value)

.from(SysDictDynamicSqlSupport.sysDict)

.build()

.render(RenderingStrategies.MYBATIS3);

List list = sysDictMapper.selectMany(selectStatement);

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

查询所有列

SELECT

id,label,value,sort.......

FROM

sys_dict

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)

.from(SysDictDynamicSqlSupport.sysDict)

.build()

.render(RenderingStrategies.MYBATIS3);

List list = sysDictMapper.selectMany(selectStatement);

条件查询

SELECT

*

FROM

sys_dict

WHERE

label = '男'

OR label = '女'

ORDER BY

`value` ASC

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)

.from(SysDictDynamicSqlSupport.sysDict)

.where(label, isEqualTo("男"))

.or(label,isEqualTo("女"))

.orderBy(value)

.build()

.render(RenderingStrategies.MYBATIS3);

List 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())即可。

SELECT

*

FROM

sys_dict

WHERE

label IN ( '女', '男' )

ORDER BY

`value`

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)

.from(SysDictDynamicSqlSupport.sysDict)

.where(label, isIn("女", "男"))

.orderBy(value)

.build()

.render(RenderingStrategies.MYBATIS3);

List list = sysDictMapper.selectMany(selectStatement);

where条件查询还有很多我就不一一例举了，我这里有一张官方偷来的表格：

Condition

Example

Result

Between

where(foo, isBetween(x).and(y))

where foo between ? and ?

Equals

where(foo, isEqualTo(x))

where foo = ?

Greater Than

where(foo, isGreaterThan(x))

where foo > ?

Greater Than or Equals

where(foo, isGreaterThanOrEqualTo(x))

where foo >= ?

In

where(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 Than

where(foo, isLessThan(x))

where foo < ?

Less Than or Equals

where(foo, isLessThanOrEqualTo(x))

where foo <= ?

Like

where(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 Between

where(foo, isNotBetween(x).and(y))

where foo not between ? and ?

Not Equals

where(foo, isNotEqualTo(x))

where foo <> ?

Not In

where(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 Like

where(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 Null

where(foo, isNotNull())

where foo is not null

Null

where(foo, isNull())

where foo is null

子查询

SELECT

*

FROM

user_resource

WHERE

id IN (

SELECT

resource_id

FROM

user_role_resource

WHERE

role_id = '1'

)

SelectStatementProvider selectStatement = SqlBuilder.select(userResourceMapper.selectList)

.from(UserResourceDynamicSqlSupport.userResource)

.where(UserResourceDynamicSqlSupport.id, isIn(

select(UserRoleResourceDynamicSqlSupport.resourceId)

.from(UserRoleResourceDynamicSqlSupport.userRoleResource)

.where(UserRoleResourceDynamicSqlSupport.roleId, isEqualTo("1"))))

.build()

.render(RenderingStrategies.MYBATIS3);

List list = userResourceMapper.selectMany(selectStatement);

子查询还有很多，我这里又有一张官方偷来的表格：

Condition

Example

Result

Equals

where(foo, isEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))

where foo = (select bar from table2 where bar = ?)

Greater Than

http:// where(foo, isGreaterThan(select(bar).from(table2).where(bar, isEqualTo(x)))

where foo > (select bar from table2 where bar = ?)

Greater Than or Equals

where(foo, isGreaterThanOrEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))

where foo >= (select bar from table2 where bar = ?)

In

where(foo, isIn(select(bar).from(table2).where(bar, isLessThan(x)))

where foo in (select bar from table2 where bar < ?)

Less Than

where(foo, isLessThan(select(bar).from(table2).where(bar, isEqualTo(x)))

where foo < (select bar from table2 where bar = ?)

Less Than or Equals

where(foo, isLessThanOrEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))

where foo <= (select bar from table2 where bar = ?)

Not Equals

where(foo, isNotEqualTo(select(bar).from(table2).where(bar, isEqualTo(x)))

where foo <> (select bar from table2 where bar = ?)

Not In

where(foo, isNotIn(select(bar).from(table2).where(bar, isLessThan(x)))

where foo not in (select bar from table2 where bar < ?)

根据业务逻辑添加条件

详细看代码

QueryExpressionDSL.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 list = sysDictMapper.selectMany(selectStatement);

连接查询

有前面的基础，连接查询其实异曲同工，我这里直接贴上官方示例代码：

SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)

.from(orderMaster, "om")

.join(orderDetail, "od").on(orderMaster.orderId, equalTo(orderDetail.orderId))

.build()

.render(RenderingStrategies.MYBATIS3);

目前支持四种连接类型：

.join(...) 内连接

.leftJoin(...) 左外连接

.rightJoin(...) 右外连接

.fullJoin(...) 全连接

增

新增这里就不附上SQL语句了

新增一条

SysDict sysDict = new SysDict();

sysDict.setLabel("测试");

sysDict.setValue("0");

sysDict.setType("test");

sysDict.setSort(0);

sysDict.setDescription("测试");

sysDict.insert("SYSTEM");

int row = sysDictMapper.insRDPhjucVfert(sysDict);

System.out.println("成功插入条数：" + row);

批量新增

List 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);

}

MultiRowInsertStatementProvider multiRowInsert = SqlBuilder.insertMultiple(list)

.into(SysDictDynamicSqlSupport.sysDict)

.map(id).toProperty("id")

.map(createdBy).toProperty("createdBy")

.map(createdTime).toProperty("createdTime")

.map(updateBy).toProperty("updateBy")

.map(updateTime).toProperty("updateTime")

.map(dele).toProperty("dele")

.map(remake).toProperty("remake")

.map(spare1).toProperty("spare1")

.map(value).toProperty("value")

.map(label).toProperty("label")

.map(type).toProperty("type")

.map(description).toProperty("description")

.map(sort).toProperty("sort")

.build()

.render(RenderingStrategies.MYBATIS3);

int rows = sysDictMapper.insertMultiple(multiRowInsert);

System.out.println("成功插入条数：" + rows);

批量新增这里需要注意的是map的添加，也可以不加，但我在使用过程中出现过不加map导致批量新增出现某些必填字段明明赋值了数据库却报没有不能为空，猜测应该是转换成sql语句时into与value没有一一对应，加上map就没问题了。

PS：.map可以直接从xxxDictMapper.insert()中copy过来。

删

//根据主键删除

sysDictMapper.deleteByPrimaryKey("");

//条件删除

DeleteStatementProvider deleteStatement = deleteFrom(SysDictDynamicSqlSupport.sysDict)

.where(SysDictDynamicSqlSupport.typehttp://, isEqualTo("test"))

.build()

.render(RenderingStrategies.MYBATIS3);

sysDictMapper.delete(deleteStatement);

改

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

//根据主键对所有属性进行更新

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方法，常用的方法有以下：

set(column).equalToNull() 将对应列更新为null；

set(column).equalTo(T value)将对应列更新为value；

set(column).equalToWhenPresent(T value)如果value不能null的话更新列；

set(column).equalTo(BasicColumn rightColumn)将一列的值设置为另一列的值，还可以对其加，减等操作。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。