浅谈MyBatis3 DynamicSql风格语法使用指南
作者:LeArn淡然 发布时间:2023-11-25 13:05:06
目录
查
查询指定列
查询所有列
条件查询
子查询
根据业务逻辑添加条件
连接查询
增
新增一条
批量新增
删
改
主要演示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>
查
查询我尽量贴上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<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语句一样,真香。
查询所有列
SELECT
id,label,value,sort.......
FROM
sys_dict
SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)
.from(SysDictDynamicSqlSupport.sysDict)
.build()
.render(RenderingStrategies.MYBATIS3);
List<SysDict> 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<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())即可。
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<SysDict> 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<UserResource> 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 | 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<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);
连接查询
有前面的基础,连接查询其实异曲同工,我这里直接贴上官方示例代码:
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.insert(sysDict);
System.out.println("成功插入条数:" + row);
批量新增
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);
}
MultiRowInsertStatementProvider<SysDict> 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.type, 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)将一列的值设置为另一列的值,还可以对其加,减等操作。
来源:https://blog.csdn.net/qq_35323561/article/details/113373985
猜你喜欢
- 自定义工具类PropertyUtil,并在该类的static静态代码块中读取properties文件内容保存在static属性中以供别的程序
- 首先先简单的说一下其3大特性的定义:封装:隐藏对象的属性和实现细节,仅对外公开接口,控制在程序中属性的读和修改的访问级别。将抽象得到的数据和
- 本文实例讲述了C#实现对Json字符串处理方法,分享给大家供大家参考。具体分析如下:一般对于web应用开发人员来说对Json字符串都会很熟悉
- 为什么Android要申请权限简单说下在Android6.0及6.0以上一些google认为涉及“危险和用户隐私”的一些权限不仅要做清单文件
- 前言在上一篇文章中讲完了SpringMVC的大部分知识,此篇文章中主要讲解 * 。上一篇文章🚩 * 的使用是非常普遍的。例如在 OA系统中通
- 简介Android给我们提供了一种轻量级的异步任务类AsyncTask。该类中实现异步操作,并提供接口反馈当前异步执行结果及进度,这些接口中
- Java虚拟机把描述类的数据从Class文件加载到内存,并对数据进行校验、转换解析和初始化,最终形成可以被虚拟机直接使用的Java类型,这个
- 一、注解(annotations)列表 @SpringBootApplication:包含了@ComponentScan、@Configur
- 每次写批量的时候,都要在网上搜索一下,虽然都做过多次了,但具体的自己还是记不住(汗颜),所以索性今天就记录下来。前期说明:foreach的主
- 标准c++中string类函数介绍注意不是CString之所以抛弃char*的字符串而选用C++标准程序库中的string类,是因为他和前者
- 前言本文给你提供在Spring Boot 应用程序中编写好的单元测试的机制,并且深入技术细节。我们将带你学习如何以可测试的方式创建Sprin
- 1:同步调用:一种阻塞式调用,调用方要等待对方执行完毕才返回,它是一种单向调用 2:回调:一种双向调用模式,也就是说,被调用方在接口被调用时
- 方式一:if语句控制// 例如:Column( mainAxisAlig
- 前言随着网络技术的发展、计算机应用水平广泛提高,原来系统的时效性、数据的正确性、操作的方便性上都存在不足,已影响到系统的正常使用。经过考察比
- 前言Flutter (Channel stable, 2.10.3, on Microsoft Windows [Version 10.0.
- 一、文件的编码package com.study.io;/*** 测试文件编码*/public class EncodeDemo {/***
- Console.WriteLine("This is a Client, host name is {0}", Dns.
- 初学线程时,总是将 run 方法和 start 方法搞混,虽然二者是完全不同的两个方法,但刚开始使用时很难分清,原因就是因为初次使用时效果貌
- 该项目主要实现mybatisplus、多数据源、lombok、druid的集成主要参考 https://mp.baomidou.com/gu
- 一、判断语句最常用的顺序结构只能顺序执行,并不能进行判断和选择。于是便有了下面两种分支结构if语句switch语句1. if语句一个if语句