Mybaits映射器之动态SQL语句
wptr33 2024-12-01 05:03 25 浏览
前言:
今天我们一起聊聊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用到的重要元素,在工作中我们也是必不可少的~
相关推荐
- MySQL进阶五之自动读写分离mysql-proxy
-
自动读写分离目前,大量现网用户的业务场景中存在读多写少、业务负载无法预测等情况,在有大量读请求的应用场景下,单个实例可能无法承受读取压力,甚至会对业务产生影响。为了实现读取能力的弹性扩展,分担数据库压...
- 3分钟短文 | Laravel SQL筛选两个日期之间的记录,怎么写?
-
引言今天说一个细分的需求,在模型中,或者使用laravel提供的EloquentORM功能,构造查询语句时,返回位于两个指定的日期之间的条目。应该怎么写?本文通过几个例子,为大家梳理一下。学习时...
- 一文由浅入深带你完全掌握MySQL的锁机制原理与应用
-
本文将跟大家聊聊InnoDB的锁。本文比较长,包括一条SQL是如何加锁的,一些加锁规则、如何分析和解决死锁问题等内容,建议耐心读完,肯定对大家有帮助的。为什么需要加锁呢?...
- 验证Mysql中联合索引的最左匹配原则
-
后端面试中一定是必问mysql的,在以往的面试中好几个面试官都反馈我Mysql基础不行,今天来着重复习一下自己的弱点知识。在Mysql调优中索引优化又是非常重要的方法,不管公司的大小只要后端项目中用到...
- MySQL索引解析(联合索引/最左前缀/覆盖索引/索引下推)
-
目录1.索引基础...
- 你会看 MySQL 的执行计划(EXPLAIN)吗?
-
SQL执行太慢怎么办?我们通常会使用EXPLAIN命令来查看SQL的执行计划,然后根据执行计划找出问题所在并进行优化。用法简介...
- MySQL 从入门到精通(四)之索引结构
-
索引概述索引(index),是帮助MySQL高效获取数据的数据结构(有序),在数据之外,数据库系统还维护者满足特定查询算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构...
- mysql总结——面试中最常问到的知识点
-
mysql作为开源数据库中的榜一大哥,一直是面试官们考察的重中之重。今天,我们来总结一下mysql的知识点,供大家复习参照,看完这些知识点,再加上一些边角细节,基本上能够应付大多mysql相关面试了(...
- mysql总结——面试中最常问到的知识点(2)
-
首先我们回顾一下上篇内容,主要复习了索引,事务,锁,以及SQL优化的工具。本篇文章接着写后面的内容。性能优化索引优化,SQL中索引的相关优化主要有以下几个方面:最好是全匹配。如果是联合索引的话,遵循最...
- MySQL基础全知全解!超详细无废话!轻松上手~
-
本期内容提醒:全篇2300+字,篇幅较长,可搭配饭菜一同“食”用,全篇无废话(除了这句),干货满满,可收藏供后期反复观看。注:MySQL中语法不区分大小写,本篇中...
- 深入剖析 MySQL 中的锁机制原理_mysql 锁详解
-
在互联网软件开发领域,MySQL作为一款广泛应用的关系型数据库管理系统,其锁机制在保障数据一致性和实现并发控制方面扮演着举足轻重的角色。对于互联网软件开发人员而言,深入理解MySQL的锁机制原理...
- Java 与 MySQL 性能优化:MySQL分区表设计与性能优化全解析
-
引言在数据库管理领域,随着数据量的不断增长,如何高效地管理和操作数据成为了一个关键问题。MySQL分区表作为一种有效的数据管理技术,能够将大型表划分为多个更小、更易管理的分区,从而提升数据库的性能和可...
- MySQL基础篇:DQL数据查询操作_mysql 查
-
一、基础查询DQL基础查询语法SELECT字段列表FROM表名列表WHERE条件列表GROUPBY分组字段列表HAVING分组后条件列表ORDERBY排序字段列表LIMIT...
- MySql:索引的基本使用_mysql索引的使用和原理
-
一、索引基础概念1.什么是索引?索引是数据库表的特殊数据结构(通常是B+树),用于...
- 一周热门
-
-
C# 13 和 .NET 9 全知道 :13 使用 ASP.NET Core 构建网站 (1)
-
程序员的开源月刊《HelloGitHub》第 71 期
-
详细介绍一下Redis的Watch机制,可以利用Watch机制来做什么?
-
假如有100W个用户抢一张票,除了负载均衡办法,怎么支持高并发?
-
Java面试必考问题:什么是乐观锁与悲观锁
-
如何将AI助手接入微信(打开ai手机助手)
-
redission YYDS spring boot redission 使用
-
SparkSQL——DataFrame的创建与使用
-
一文带你了解Redis与Memcached? redis与memcached的区别
-
如何利用Redis进行事务处理呢? 如何利用redis进行事务处理呢英文
-
- 最近发表
- 标签列表
-
- 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)