Mybaits映射器之动态SQL语句
wptr33 2024-12-01 05:03 15 浏览
前言:
今天我们一起聊聊Mybaits的映射器构建动态SQL语句来实现复杂的业务查询场景,围绕着Mybaits提供的7个用于构建动态sql的元素来进行分析。
但是,通常如果有一个比较复杂的查询场景,我们会先通过 Java 代码进行前期的处理(如参数处理),然后再去构建较为简单的 SQL 语句,而不会直接构建复杂的 SQL 语句进行查询,这也是符合当下大家较为认可的开发规范(尽可能使用简单的 SQL 语句)。
if 元素
if 元素,MyBatis 映射器中用于实现条件判断的元素。
if 元素是我们在 MyBatis 映射器中最常使用的动态 SQL 语句元素,没有之一。MyBatis 中的 if 元素与 Java 中的 if 关键字功能是一样的,用于实现条件判断,它只有一个属性 test,用于编写条件判断语句。
假设我们有如下需求,实现根据输入的用户信息来查询所有符合条件的用户,如果没有输入任何用户信息,则查询全部用户,那么我们可以这样来写 MyBatis 映射器中的 SQL 语句:
<select id="selectByRecord" parameterType="com.cw.entity.UserDO" resultType="com.cw.entity.UserDO">
select * from user
where 1 = 1
<if test="user.name != null">
and name = #{user.name, jdbcType=VARCHAR}
</if>
<if test="user.age != null">
and age = #{user.age, jdbcType=INTEGER}
</if>
<if test="user.gender != null">
and gender = #{user.gender, jdbcType=VARCHAR}
</if>
<if test="user.idType != null">
and id_type = #{user.idType, jdbcType=INTEGER}
</if>
<if test="user.idNumber != null">
and id_number = #{user.idNumber, jdbcType=VARCHAR}
</if>
</select>
Mapper 接口中的方法:
List<UserDO> selectByRecord(@Param("user") UserDO userDO);
单元测试代码:
public void testSelectByRecord() throws IOException {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserDO userDO = new UserDO();
userDO.setName("李四");
userDO.setAge(20);
userDO.setGender("F");
userDO.setIdType(1);
userDO.setIdNumber("11010119990708785X");
List<UserDO> users = userMapper.selectByRecord(userDO);
log.info("查询全部用户:{}", JSON.toJSONString(users));
sqlSession.close();
}
查看控制台输出:
我们删除测试案例中的部分参数赋值,只留下为 name 字段赋值的部分,再来执行测试案例,并观察控制台输出的 SQL 语句:
可以看到,此时输出的查询语句中,条件语句中只剩下了根据 name 字段查询的部分了。
if 元素支持的运算符
在 if 元素中使用了运算符不等于(!=),除此之外我们还可以在 if 元素中使用其它的常见运算符:
注意,如果使用的运算符中含有“<”或者“&”,你需要使用它的转义符,否则在 XML 解析的过程中无法处理。例如,在上面的表格中,小于(<),小于等于(<=)和逻辑与(&&)需要使用它们的转义符。
实际上,MyBatis 支持的运算符远远不止这些,MyBatis 甚至连移位运算符(如右移运算符>>)和异或运算符(^或 xor)都可以支持。不过在日常工作中,上面表格中展示的运算符已经能够满足我们绝大部分的场景了。
where 元素
上面的案例中,我们使用了“丑陋”的条件语句:1 = 1。想必很多小伙伴都这么写过 MyBatis 的 SQL 语句吧?这也是无奈之举,因为 SQL 的语法规则中,条件语句的第一个条件不能以 and 开头,因此我们加上“1 = 1”来保证动态组装的条件不会作为条件语句的第一个条件。
虽然,MySQL 5.7 之后对“1 = 1”进行了优化,来保证不会产生性能问题,但是这么做会让代码变得非常丑陋,其次也会显得我们功力浅薄。
MyBatis 贴心的为我们准备了解决办法:where 元素,它会先根据 if 元素的处理结果生成子句,然后将子句前缀的“and”和“or”移除,最后在子句的开始处插入“where”,如果不存在子句,则不会插入“where”。
我们将上面的案例改写为使用 where 元素的方式,如下:
<select id="selectByRecord" parameterType="com.wyz.entity.UserDO" resultType="com.wyz.entity.UserDO">
select * from user
<where>
<if test="user.name != null">
and name = #{user.name, jdbcType=VARCHAR}
</if>
<if test="user.age != null">
and age = #{user.age, jdbcType=INTEGER}
</if>
<if test="user.gender != null">
and gender = #{user.gender, jdbcType=VARCHAR}
</if>
<if test="user.idType != null">
and id_type = #{user.idType, jdbcType=INTEGER}
</if>
<if test="user.idNumber != null">
and id_number = #{user.idNumber, jdbcType=VARCHAR}
</if>
</where>
</select>
可以看到控制台输出的 SQL 语句的条件语句中,首个条件前的 and 已经不见了:
现在我们删除所有参数赋值,再来执行测试案例:可以看到,此时控制台输出的 SQL 语句中,已经完全没有了 where 的身影。
set 元素
set 元素与 where 元素类似,不过 set 元素是用在 update 语句中的,set 元素会先根据 if 元素的处理结果生成子句,然后将子句结尾处的“,”移除,最后在子句的开始处插入“set”,如果不存在子句则不会插入“set”。与 where 元素不同的是,在 SQL 规范中,update 语句是必须包含 set 子句的,否则无法处理 SQL 语句。
写一个使用 set 元素更新用户信息的案例,首先来写 MyBatis 映射器中的 SQL 语句:
<update id="updateByRecord" parameterType="com.wyz.entity.UserDO">
update user
<set>
<if test="user.name != null">
name = #{user.name, jdbcType=VARCHAR},
</if>
<if test="user.age != null">
age = #{user.age, jdbcType=INTEGER},
</if>
<if test="user.gender != null">
gender = #{user.gender, jdbcType=VARCHAR},
</if>
<if test="user.idType != null">
id_type = #{user.idType, jdbcType=INTEGER},
</if>
<if test="user.idNumber != null">
id_number = #{user.idNumber, jdbcType=VARCHAR},
</if>
</set>
where user_id = #{user.userId, jdbcType=INTEGER}
</update>
可以看到,在 update 语句中,最后一个赋值语句里我们同样在id_number = #{user.idNumber, jdbcType=VARCHAR}后添加了“,”。
接着我们来定义 Mapper 接口中的方法:
int updateByRecord(@Param("user") UserDO userDO);
测试代码:
public void testUpdateByRecord() throws IOException {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserDO userDO = new UserDO();
userDO.setUserId(1);
userDO.setName("王二麻子");
userDO.setAge(19);
userDO.setGender("M");
userDO.setIdType(1);
userDO.setIdNumber("123456789012345678");
int count = userMapper.updateByRecord(userDO);
sqlSession.commit();
sqlSession.close();
}
执行测试案例,可以看到控制台输出的 SQL 语句中,set 元素成功的为 update 语句插入了 set 语句,并且为 set 语句中的最后一项赋值操作自动删除了“,”,如下:
trim 元素
上面我们展示了在 select 语句中和 update 语句中动态组装 where 语句和 set 语句,那么如果想要在 insert 语句中动态组装插入字段呢?是不是有类似 where 元素和 set 元的元素呢?
不过 MyBatis 提供了另一个更加灵活(意味着你需要做更多的处理来实现你的需求) 的元素:tirm 元素。
trim 元素中定义了 4 个属性,可以将它们分为两组:
- 前缀相关的属性: prefix 属性:子句中要在开始处插入的字符串; prefixOverrides 属性:子句中要被移除的前缀字符串;
- 后缀相关的属性: suffix 属性:子句中要在结尾处插入的字符串; suffixOverrides 属性:子句中要被移除的后缀字符串。
trim 元素中,同样先是根据 if 元素的处理结果生成子句,然后根据 prefixOverrides 属性和 suffixOverrides 属性的配置移除子句前后缀的字符串,最后根据 prefix 属性和 suffix 属性的配置,在子句的开始处和结尾处插入字符串。
Tips:你可能会被属性名称中的 Overrides 所迷惑,认为是 prefix(suffix)中指定的字符串替换了 prefixOverrides(suffixOverrides)中指定的字符串,实际上在 MyBatis 的源码中,MyBatis 是通过移除后拼接来实现的。
我们使用 trim 元素实现具有动态插入功能的 insert 语句,如下:
<insert id="insertByRecord" parameterType="com.wyz.entity.UserDO">
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="user.userId != null">
user_id,
</if>
<if test="user.name != null">
name,
</if>
<if test="user.age != null">
age,
</if>
<if test="user.gender != null">
gender,
</if>
<if test="user.idType != null">
id_type,
</if>
<if test="user.idNumber != null">
id_number,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="user.userId != null">
#{user.userId, jdbcType=INTEGER},
</if>
<if test="user.name != null">
#{user.name, jdbcType=VARCHAR},
</if>
<if test="user.age != null">
#{user.age, jdbcType=INTEGER},
</if>
<if test="user.gender != null">
#{user.gender, jdbcType=VARCHAR},
</if>
<if test="user.idType != null">
#{user.idType, jdbcType=INTEGER},
</if>
<if test="user.idNumber != null">
#{user.idNumber, jdbcType=VARCHAR},
</if>
</trim>
</insert>
定义接口方法:
int insertByRecord(@Param("user") UserDO userDO);
测试方法:
public void testInsertByRecord() throws IOException {
Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserDO userDO = new UserDO();
userDO.setUserId(5);
userDO.setName("赵六");
userDO.setAge(1);
userDO.setGender("F");
userDO.setIdType(1);
userDO.setIdNumber("123456789012345678");
int count = userMapper.insertByRecord(userDO);
sqlSession.commit();
sqlSession.close();
}
来观察控制台的输出:
foreach 元素
foreach 元素提供了在映射器中遍历集合(对应 Java 中的 List,Set)和字典的能力(对应 Java 中的 Map)的能力,通常我们会在构建 in 条件语句中使用。foreach 元素提供了 6 个属性:
假设我们有一个查询页面,其中的一个查询条件为证件类型,并且证件类型允许多选,因此我们在处理查询语句时需要考虑到传入多个证件类型的场景,此时最容易想到的方式是使用 in 来构建条件语句。
我们先来写 Mapper 接口中的方法:
List<UserDO> selectByIdTypes(@Param("idTypes") List<Integer> idTypes);
映射器中的 SQL 语句:
<select id="selectByIdTypes" resultType="com.wyz.entity.UserDO">
select * from user
where id_type in
<foreach collection="idTypes" item="idType" separator="," open="(" close=")">
#{idType, jdbcType=INTEGER}
</foreach>
</select>
单元测试:
public void testSelectByIdTypes() throws IOException {
Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Integer> idTypes = List.of(1, 2);
List<UserDO> users = userMapper.selectByIdTypes(idTypes);
}
执行单元测试,来看控制台输出的 SQL 语句:
使用 foreach 元素实现批量插入
foreach 元素的功能非常强大,我们不仅仅可以用 foreach 元素来构建 in 条件语句,也可以使用 foreach 元素来实现批量插入。
首先来类 Mapper 接口中的方法:
int batchInsert(@Param("users") List<UserDO> users);
接着我们来写 MyBatis 映射器中的 SQL 语句:
<insert id="batchInsert">
insert into user(user_id, name, gender, age, id_type, id_number)
values
<foreach collection="users" item="user" open="(" separator="), (" close=")">
#{user.userId, jdbcType=INTEGER},
#{user.name, jdbcType=VARCHAR},
#{user.gender, jdbcType=VARCHAR},
#{user.age, jdbcType=INTEGER},
#{user.idType, jdbcType=INTEGER},
#{user.idNumber, jdbcType=VARCHAR}
</foreach>
</insert>
单元测试:
public void testBatchInsert() throws IOException {
Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<UserDO> users = new ArrayList<>();
for (int i = 0; i < 2; i++) {
UserDO user = new UserDO();
user.setUserId(3 + i);
user.setName("批量" + (i + 1));
user.setAge(18 + i);
user.setGender("F");
user.setIdType(1);
user.setIdNumber("11010319981112325" + i);
users.add(user);
}
userMapper.batchInsert(users);
sqlSession.commit();
sqlSession.close();
}
观察控制台输出的 SQL 语句:
除了以上两种简单的用法外,foreach 元素还可以帮助我们实现更多复杂的 SQL 语句,比如之前我利用 foreach 元素实现过联表 union all 之后的条件查询语句,更多的用法大家可以自行探索。
choose 元素
choose 元素的功能与 Java 中的 switch 相似,choose 元素包含两个子元素:when 元素和 otherwise 元素。choose 元素连同它的子元素在用法上与 Java 中的switch...case...default语句的用法相同。
比如说我们有个需求,根据传入的条件查询用户信息,允许传入姓名和性别,传入了姓名就按姓名查询(忽略性别),如果没有传入姓名就按照性别查询,如果两者都没有传入,就查询证件类型为 1 的用户,这里我们使用 UserDO 作为入参,那么 MyBatis 映射器中的 SQL 语句我们可以这么写:
<select id="selectByUserNameOrGender" parameterType="com.wyz.entity.UserDO" resultType="com.wyz.entity.UserDO">
select * from user
<where>
<choose>
<when test="user.name != null">
and name = #{user.name, jdbcType=VARCHAR}
</when>
<when test="user.gender != null">
and gender = #{user.gender, jdbcType=VARCHAR}
</when>
<otherwise>
and id_type = 1
</otherwise>
</choose>
</where>
</select>
单元测试的代码:
public void testSelectByUserNameOrGender() throws IOException {
Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserDO user = new UserDO();
user.setName("张三");
user.setGender("F");
List<UserDO> users = userMapper.selectByUserNameOrGender(user);
}
单元测试中,我们构建查询参数时同时传入了姓名和性别,最后我们来执行单元测试,观察控制台的输出:
可以看到,在控制台输出的 SQL 语句的查询条件中只有 name 这一个条件,这也符合我们对 choose 元素的认知。
Tips:你可以在构建查询参数时删除为 name 字段赋值的 Java 代码来观察控制台输出的 SQL 语句,以及同时删除为 name 和 gender 赋值的 Java 代码,再来观察控制台输出的 SQL 语句。
bind 元素
ind 元素允许你在 MyBatis 映射器中创建一个自定义局部变量,最常见的场景就是我们在模糊查询中使用 bind 元素。
通常我们在 MyBatis 中构建模糊查询语句,可能会使用如下几种方式:
- 直接在 Java 代码中为参数添加“%”后传递到 SQL 语句中;
- 直接在 MyBatis 映射器文件中拼接“%”,例如:select * from user where name like '${surname}%';
- 使用 MySQL 提供的 concat 函数(Oracle 中使用 “||”)拼接“%”,例如:select * from user where name like concat(#{surname, jdbcType=VARCHAR}, '%')。
除了以上几种方法外,我们还可以使用 bind 元素来构建 MyBatis 映射器中的局部变量,来实现模糊查询的功能。例如,我们需要查询某个姓氏的用户有多少,可以这样构建 MyBatis 映射器中的 SQL 语句:
<select id="selectBySurname" resultType="com.wyz.entity.UserDO">
<bind name="bindValue" value="surname + '%'" />
select * from user where name like #{bindValue}
</select>
单元测试的代码:
public void testSelectBySurname() throws IOException {
Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<UserDO> users = userMapper.selectBySurname("张");
sqlSession.commit();
sqlSession.close();
}
控制台输出的 SQL 语句:
MyBatis 映射器中允许使用 bind 元素来创建多个自定一局部变量,如:
<select id="selectBySurname" resultType="com.wyz.entity.UserDO">
<bind name="bindValue" value="surname + '%'" />
<bind name="bindValue2" value="'%' + surname + '%'" />
select * from user where name like #{bindValue}
</select>
好了,以上就是我们在mybaits中构建动态sql用到的重要元素,在工作中我们也是必不可少的~
相关推荐
- Python自动化脚本应用与示例(python办公自动化脚本)
-
Python是编写自动化脚本的绝佳选择,因其语法简洁、库丰富且跨平台兼容性强。以下是Python自动化脚本的常见应用场景及示例,帮助你快速上手:一、常见自动化场景文件与目录操作...
- Python文件操作常用库高级应用教程
-
本文是在前面《Python文件操作常用库使用教程》的基础上,进一步学习Python文件操作库的高级应用。一、高级文件系统监控1.1watchdog库-实时文件系统监控安装与基本使用:...
- Python办公自动化系列篇之六:文件系统与操作系统任务
-
作为高效办公自动化领域的主流编程语言,Python凭借其优雅的语法结构、完善的技术生态及成熟的第三方工具库集合,已成为企业数字化转型过程中提升运营效率的理想选择。该语言在结构化数据处理、自动化文档生成...
- 14《Python 办公自动化教程》os 模块操作文件与文件夹
-
在日常工作中,我们经常会和文件、文件夹打交道,比如将服务器上指定目录下文件进行归档,或将爬虫爬取的数据根据时间创建对应的文件夹/文件,如果这些还依靠手动来进行操作,无疑是费时费力的,这时候Pyt...
- python中os模块详解(python os.path模块)
-
os模块是Python标准库中的一个模块,它提供了与操作系统交互的方法。使用os模块可以方便地执行许多常见的系统任务,如文件和目录操作、进程管理、环境变量管理等。下面是os模块中一些常用的函数和方法:...
- 21-Python-文件操作(python文件的操作步骤)
-
在Python中,文件操作是非常重要的一部分,它允许我们读取、写入和修改文件。下面将详细讲解Python文件操作的各个方面,并给出相应的示例。1-打开文件...
- 轻松玩转Python文件操作:移动、删除
-
哈喽,大家好,我是木头左!Python文件操作基础在处理计算机文件时,经常需要执行如移动和删除等基本操作。Python提供了一些内置的库来帮助完成这些任务,其中最常用的就是os模块和shutil模块。...
- Python 初学者练习:删除文件和文件夹
-
在本教程中,你将学习如何在Python中删除文件和文件夹。使用os.remove()函数删除文件...
- 引人遐想,用 Python 获取你想要的“某个人”摄像头照片
-
仅用来学习,希望给你们有提供到学习上的作用。1.安装库需要安装python3.5以上版本,在官网下载即可。然后安装库opencv-python,安装方式为打开终端输入命令行。...
- Python如何使用临时文件和目录(python目录下文件)
-
在某些项目中,有时候会有大量的临时数据,比如各种日志,这时候我们要做数据分析,并把最后的结果储存起来,这些大量的临时数据如果常驻内存,将消耗大量内存资源,我们可以使用临时文件,存储这些临时数据。使用标...
- Linux 下海量文件删除方法效率对比,最慢的竟然是 rm
-
Linux下海量文件删除方法效率对比,本次参赛选手一共6位,分别是:rm、find、findwithdelete、rsync、Python、Perl.首先建立50万个文件$testfor...
- Python 开发工程师必会的 5 个系统命令操作库
-
当我们需要编写自动化脚本、部署工具、监控程序时,熟练操作系统命令几乎是必备技能。今天就来聊聊我在实际项目中高频使用的5个系统命令操作库,这些可都是能让你效率翻倍的"瑞士军刀"。一...
- Python常用文件操作库使用详解(python文件操作选项)
-
Python生态系统提供了丰富的文件操作库,可以处理各种复杂的文件操作需求。本教程将介绍Python中最常用的文件操作库及其实际应用。一、标准库核心模块1.1os模块-操作系统接口主要功能...
- 11. 文件与IO操作(文件io和网络io)
-
本章深入探讨Go语言文件处理与IO操作的核心技术,结合高性能实践与安全规范,提供企业级解决方案。11.1文件读写11.1.1基础操作...
- Python os模块的20个应用实例(python中 import os模块用法)
-
在Python中,...
- 一周热门
-
-
C# 13 和 .NET 9 全知道 :13 使用 ASP.NET Core 构建网站 (1)
-
因果推断Matching方式实现代码 因果推断模型
-
git pull命令使用实例 git pull--rebase
-
面试官:git pull是哪两个指令的组合?
-
git 执行pull错误如何撤销 git pull fail
-
git pull 和git fetch 命令分别有什么作用?二者有什么区别?
-
git fetch 和git pull 的异同 git中fetch和pull的区别
-
git pull 之后本地代码被覆盖 解决方案
-
还可以这样玩?Git基本原理及各种骚操作,涨知识了
-
git命令之pull git.pull
-
- 最近发表
- 标签列表
-
- git pull (33)
- git fetch (35)
- mysql insert (35)
- mysql distinct (37)
- concat_ws (36)
- java continue (36)
- jenkins官网 (37)
- mysql 子查询 (37)
- python元组 (33)
- mybatis 分页 (35)
- vba split (37)
- redis watch (34)
- python list sort (37)
- nvarchar2 (34)
- mysql not null (36)
- hmset (35)
- python telnet (35)
- python readlines() 方法 (36)
- munmap (35)
- docker network create (35)
- redis 集合 (37)
- python sftp (37)
- setpriority (34)
- c语言 switch (34)
- git commit (34)