Mybatis-动态SQL - 博客

1.什么是动态SQL?

Mabits是一个Java持久化框架，它提供了动态SQL的功能。动态SQL是一种根据不同条件动态生成SQL语句的技术。
在Mabits中，动态SQL通常是通过使用一组特殊的标签和代码块来实现的，这些标签和代码块可以根据条件包含或排除不同的部分，从而生成不同的SQL语句。
动态SQL可以让开发者构建更灵活、高效的数据库操作语句，因为他们可以根据实际需要创建出更加精确和优化的SQL语句。Mabits的动态SQL还允许开发者使用参数化查询，可以防止SQL注入攻击，并增强程序的安全性

接下来学习经常使用的几个标签

2.<if>标签

<if>标签是Mabits动态SQL中最常用的一个标签之一，它的作用是根据给定条件包含或排除不同的部分，以生成不同的SQL语句。在XML文件中，<if>标签通常被嵌套在其他标签内，如<select>、<insert>和<update>等标签内，用于控制生成的SQL语句的结构和内容。

<if>标签通常包含一个test属性，该属性被用于指定条件表达式。如果表达式的结果为true，则<if>标签内的内容会被包含在生成的SQL语句中；否则，这些内容会被忽略。以下代码段展示了如何在一个<select>标签内使用<if>标签来动态生成SQL语句：

常见场景：非必传参数时使用<if>
int addUser2(UserEntity user); @Test void addUser2() { String username =
"zhaoliu"; String password = "123456"; UserEntity user = new UserEntity();
user.setUsername(username); user.setPwd(password); int result =
userMapper.addUser2(user); System.out.println("修改行数: "+result); } <insert
id="addUser2"> insert into userinfo(username,password <if test="photo != null">
,photo </if> ) values(#{username},#{pwd} <if test="photo != null"> ,#{photo}
</if> ) </insert>
当不传入photo时，执行结果
JDBC Connection [HikariProxyConnection@579590740 wrapping
com.mysql.cj.jdbc.ConnectionImpl@75b6dd5b] will not be managed by Spring ==>
Preparing: insert into userinfo(username,password ) values(?,? ) ==>
Parameters: zhaoliu(String), 123456(String) <== Updates: 1 Closing non
transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@380e1909] 修改行数: 1
传入photo时，执行结果

JDBC Connection [HikariProxyConnection@645717550 wrapping
com.mysql.cj.jdbc.ConnectionImpl@609e57da] will not be managed by Spring ==>
Preparing: insert into userinfo(username,password ,photo ) values(?,? ,? ) ==>
Parameters: zhaoliu2(String), 123456(String), dog.png(String) <== Updates: 1
Closing non transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@24d61e4] 修改行数: 1 //数据库
mysql> select*from userinfo;
+----+----------+----------+---------+---------------------+---------------------+-------+
| id | username | password | photo | createtime | updatetime | state |
+----+----------+----------+---------+---------------------+---------------------+-------+
| 1 | lisi | 123456 | | 2022-12-06 17:10:48 | 2022-12-06 18:10:48 | 1 | | 3 |
zhangsan | 123456 | | 2023-05-18 17:21:49 | 2023-05-18 17:21:49 | 1 | | 4 |
wangwu | 123456 | | 2023-05-18 17:36:28 | 2023-05-18 17:36:28 | 1 | | 5 |
zhaoliu | 123456 | | 2023-05-20 09:22:08 | 2023-05-20 09:22:08 | 1 | | 6 |
zhaoliu2 | 123456 | dog.png | 2023-05-20 09:35:06 | 2023-05-20 09:35:06 | 1 |
+----+----------+----------+---------+---------------------+---------------------+-------+
5 rows in set (0.00 sec)
注意理解：

3.<trim>标签

学习<trim>标签之前先看一个问题
<insert id="addUser3"> insert into userinfo( <if test="username != null">
username, </if> <if test="pwd != null"> password, </if> <if test="photo !=
null"> photo </if> ) values( <if test="username != null"> #{username}, </if>
<if test="pwd != null"> #{pwd}, </if> <if test="photo != null"> #{photo} </if>
) </insert>
当遇到这种所有参数都为非必填参数时，如果第一个参数传了值，后续都没传，就会出现（username,）这个问题，此时很明显SQL
语句是错误的，肯定不能成功执行或者其它类似这种问题

<trim>标签就能解决此类问题

<trim>标签也是Mabits动态SQL中常用的一个标签，主要用于快速生成包含WHERE、SET等关键字的SQL语句，同时还能够自动处理SQL语句中的逗号（,）和AND/OR等连接符。

<trim>标签通常包含以下属性：

* prefixOverrides：表示在生成SQL语句前需要忽略的字符前缀。
* suffixOverrides：表示在生成SQL语句后需要忽略的字符后缀。
* prefix：表示在生成SQL语句前需要添加的字符串。
* suffix：表示在生成SQL语句后需要添加的字符串。
* suffixOverrides：表示在生成SQL语句中需要忽略掉的字符串。
用法一：删除前缀后缀
<insert id="addUser3"> insert into userinfo <trim prefix="(" suffix=")"
suffixOverrides=","> <if test="username != null"> username, </if> <if test="pwd
!= null"> password, </if> <if test="photo != null"> photo </if> </trim> values
<trim prefix="(" suffix=")" suffixOverrides=","> <if test="username != null">
#{username}, </if> <if test="pwd != null"> #{pwd}, </if> <if test="photo !=
null"> #{photo} </if> </trim> </insert>
单元测试
@Transactional @Test void addUser3() { String username = "zhaoliu3"; String
password = "123456"; UserEntity user = new UserEntity();
user.setUsername(username); user.setPwd(password); int result =
userMapper.addUser2(user); System.out.println("修改行数: "+result); }
当不传photo时，上一个pwd后的，被去除了
JDBC Connection [HikariProxyConnection@1228603887 wrapping
com.mysql.cj.jdbc.ConnectionImpl@36c2d629] will be managed by Spring ==>
Preparing: insert into userinfo(username,password ) values(?,? ) ==>
Parameters: zhaoliu3(String), 123456(String) <== Updates: 1
用法二，解决多个非必传参数问题
List<ArticleInfoVO> getListByIdOrTitle(@Param("id")Integer id
,@Param("title")String title); <select id="getListByIdOrTitle"
resultType="com.example.demo.entity.vo.ArticleInfoVO"> select*from articleinfo
<trim prefix="where" suffixOverrides="and"> <if test="id!=null and id >0"> id =
#{id} and </if> <if test="title!=null and title!=''"> title like
concat('%',#{title},'%') </if> </trim> </select> @Test void
getListByIdOrTitle() { List<ArticleInfoVO> list =
articleMapper.getListByIdOrTitle(null,null); System.out.println(list.size()); }
执行结果：

传参都为空时，也就是trim中没有内容，那么prefix中的where也不会生产。就不用再使用

where 1=1 and ...的方式解决传参为空的问题了

传一个参数，前缀where就会执行

4.<where>标签

能更好的解决上述多个非必传参数问题

过使用<where>标签和<if>标签，可以根据用户传入的参数动态生成SQL语句的WHERE子句。如果参数不为空，则会包含相应的查询条件；否则，该查询条件会被忽略。

需要注意的是，由于<where>标签会自动处理WHERE子句中的AND和OR关键字，因此在使用时应该合理安排条件语句的位置，避免出现意外结果。

执行

非常方便

不传id 传title

将and前缀清除了

注意:<where>只能去除sql语句前缀的关键字,不能去除后缀关键字

<where>标签也可以用<trim prefix="where", prefixOverrides="and">替换

5.<set>标签

通过使用<set>标签和<if>标签，可以根据用户传入的参数动态生成SQL语句的SET子句。如果参数不为空，则会包含相应的更新内容；否则，该更新内容会被忽略

需要注意的是，由于<set>标签会自动处理SET子句中的逗号（,），因此在使用时应该合理安排更新内容的位置，避免出现意外结果

<set>标签通过将SET子句中的所有条件用逗号（,）连接起来，可以根据不同的更新情况动态生成满足需求的SQL语句

<set>标签也可以用<trim prefix="set", suffixOverrides=",">替换

示例:修改lisi->lis2,password->456789
mysql> select*from userinfo;
+----+----------+----------+---------+---------------------+---------------------+-------+
| id | username | password | photo | createtime | updatetime | state |
+----+----------+----------+---------+---------------------+---------------------+-------+
| 1 | lisi | 123456 | | 2022-12-06 17:10:48 | 2022-12-06 18:10:48 | 1 | | 3 |
zhangsan | 123456 | | 2023-05-18 17:21:49 | 2023-05-18 17:21:49 | 1 | | 4 |
wangwu | 123456 | | 2023-05-18 17:36:28 | 2023-05-18 17:36:28 | 1 | | 5 |
zhaoliu | 123456 | | 2023-05-20 09:22:08 | 2023-05-20 09:22:08 | 1 | | 6 |
zhaoliu2 | 123456 | dog.png | 2023-05-20 09:35:06 | 2023-05-20 09:35:06 | 1 |
+----+----------+----------+---------+---------------------+---------------------+-------+
代码
//修改 int updateUser(UserEntity user); <update id="updateUser"> update userinfo
<set> <if test="username!=null and username!=''"> username = #{username}, </if>
<if test="pwd!=null and pwd!=''"> password = #{pwd}, </if> </set> where id =
#{id} </update> @Test void updateUser() { UserEntity user = new UserEntity();
user.setUsername("lisi2"); user.setPwd("456789"); user.setId(1); int result =
userMapper.updateUser(user); System.out.println("修改后: "+result); }
执行结果
JDBC Connection [HikariProxyConnection@236002428 wrapping
com.mysql.cj.jdbc.ConnectionImpl@4d0e54e0] will not be managed by Spring ==>
Preparing: update userinfo SET username = ?, password = ? where id = ? ==>
Parameters: lisi2(String), 456789(String), 1(Integer) <== Updates: 1 Closing
non transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@95eb320] 修改后: 1 mysql>
select*from userinfo;
+----+----------+----------+---------+---------------------+---------------------+-------+
| id | username | password | photo | createtime | updatetime | state |
+----+----------+----------+---------+---------------------+---------------------+-------+
| 1 | lisi2 | 456789 | | 2022-12-06 17:10:48 | 2022-12-06 18:10:48 | 1 | | 3 |
zhangsan | 123456 | | 2023-05-18 17:21:49 | 2023-05-18 17:21:49 | 1 | | 4 |
wangwu | 123456 | | 2023-05-18 17:36:28 | 2023-05-18 17:36:28 | 1 | | 5 |
zhaoliu | 123456 | | 2023-05-20 09:22:08 | 2023-05-20 09:22:08 | 1 | | 6 |
zhaoliu2 | 123456 | dog.png | 2023-05-20 09:35:06 | 2023-05-20 09:35:06 | 1 |
+----+----------+----------+---------+---------------------+---------------------+-------+

不传参数pwd时

6.<foreach>标签

<foreach> 标签是 MyBatis 中的一个迭代标签，可以对集合对象进行遍历，并生成多条 SQL 语句（如 INSERT、UPDATE、DELETE
等）。

使用 <foreach> 标签，我们可以将一个集合对象的元素依次取出，作为 SQL 语句中的参数进行插入、更新或删除操作。常用的语法如下：
<foreach collection="collection" item="item" separator="separator" open="open"
close="close">  </foreach>
其中，各个属性和元素的含义如下：

* collection：指定要遍历的集合对象的属性名。
* item：指定在遍历过程中每个元素所对应的变量名。
* separator：指定在生成多条 SQL 语句时，不同语句之间的分隔符，默认为英文逗号。
* open：指定生成的 SQL 语句的头部。
* close：指定生成的 SQL 语句的尾部。
示例:删除文章表的数据
mysql> select*from articleinfo;
+----+-------+-----------+---------------------+---------------------+-----+--------+-------+
| id | title | content | createtime | updatetime | uid | rcount | state |
+----+-------+-----------+---------------------+---------------------+-----+--------+-------+
| 1 | Java | Java正文 | 2023-05-15 09:12:59 | 2023-05-15 09:12:59 | 1 | 1 | 1 | |
2 | mysql | mysql正文 | 2023-05-19 11:14:49 | 2023-05-19 11:14:49 | 1 | 1 | 1 |
+----+-------+-----------+---------------------+---------------------+-----+--------+-------+
2 rows in set (0.00 sec)
此处进行回滚,不真的删除

代码:
//根据id批量删除文章 int delByIdList(List<Integer> idList); <delete id="delByIdList">
 delete from articleinfo
where id in( <foreach collection="idList" item="id" separator=","> #{id}
</foreach> ) </delete> @Transactional @Test void delByIdList() { List<Integer>
idList = new ArrayList<>(); idList.add(1); idList.add(2); idList.add(3); //只有两条
int result = articleMapper.delByIdList(idList); System.out.println("删除:
"+result); }
执行单元测试
JDBC Connection [HikariProxyConnection@918738473 wrapping
com.mysql.cj.jdbc.ConnectionImpl@55fee662] will be managed by Spring ==>
Preparing: delete from articleinfo where id in( ? , ? , ? ) ==> Parameters:
1(Integer), 2(Integer), 3(Integer) <== Updates: 2 Releasing transactional
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4b960b5b] 删除: 2
上述代码没有使用open,close属性.将()写在了foreach标签外,这里加上

和刚才的写在标签外的方式作用是相同的

技术

Java1212 篇
Python927 篇
开发语言608 篇
c语言463 篇
算法461 篇
MySQL438 篇
数据库394 篇
前端387 篇
更多...