MyBatis - XML 操作动态 SQL

1. 前言

2. 动态插入

2.1 if 标签

2.2 trim 标签

2.2.1 注解完成动态 SQL

3. 动态查询

3.1 添加 1 = 1

3.2 where 标签

4. 动态更新

4.1 set 标签

5. foreach 标签

sql%20%E6%A0%87%E7%AD%BE%20%26%20include%20%E6%A0%87%E7%AD%BE-toc" name="tableOfContents" style="margin-left:0px">6. sql 标签 & include 标签

1. 前言

之前博文所讲的 MyBatis SQL 操作, 都必须按照注解或者标签中定义的 SQL 严格执行, 一个注解/标签, 对应的是不同的 SQL 操作. 比如: 一个 select 注解中, 定义了 3 个字段, 那这个注解只能插入这三个列的值, 如果想要插入更多的列, 必须重新再写一个注解.

而动态 SQL, 我们可以根据用户输入的值, 动态的进行字段的插入, 当用户输入这一字段的值时, 就插入这一字段. 当用户没有输入这一字段的值时, 就不插入这一字段. 能够完成不同条件下, 不同的 SQL 拼接.

注意: 本文主要通过 XML 完成动态 SQL!!

2. 动态插入

2.1 if 标签

通过 if 标签, 对传入的参数的值进行判断, 若未传入相关列的值, 则不拼接该列的 SQL 片段.

如下图所示, 只有当传入参数的值不为 null 时, 才会拼接该 SQL 片段.

注意: if 标签, 判断的是传入的参数的值(#{} 中的参数), 即 java 属性, 而不是数据库字段!!

因此, 我们就可以通过这一个 insert 标签对应的方法, 进行任意字段的插入:

但是, 当对最后一个字段传入的值为 null 时, 错误就发生了:

这是因为最后一个字段 gender 的值为 null, 导致 if 标签的判断为 false, 因此 gender 字段的 SQL 没有进行拼接, 导致倒数第二个字段 phone 后多了一个逗号(,), 拼接的内容是 "phone , "因此造成语法错误.

此时, 就需要 trim 标签出马了~~

2.2 trim 标签

trim 标签中有以下属性:

prefix: 表示整个语句块, 以 prefix 的值作为前缀
suffix: 表示整个语句块, 以suffix的值作为后缀
prefixOverrides: 表示整个语句块要去除掉的前缀
suffixOverrides: 表示整个语句块要去除掉的后缀

有 trim 标签, 就可以通过 prefixOverrides/suffixOverrides 属性解决上文中由于没有拼接最后一个字段, 导致倒数第二个字段后拼接了逗号(,)的问题, 并且还可以通过 prefix/suffix 属性将 SQL 中的 "(" 和 ")" 自动拼接到 SQL 中:

运行观察结果:

java"><insert id="insertUser4">insert into user_info<trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=","><if test="username != null">username,</if><if test="password != null">password,</if><if test="age != null">age,</if><if test="phone != null">phone,</if><if test="gender != null">gender</if></trim>values<trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=","><if test="username != null">#{username},</if><if test="password != null">#{password},</if><if test="age != null">#{age},</if><if test="phone != null">#{phone},</if><if test="gender != null">#{gender}</if></trim></insert>

2.2.1 注解操作动态 SQL

上文是通过 XML 进行的动态 SQL , 在注解中同样也是可以的, 并且方式相同.

若要通过注解完成动态 SQL, 只需将上文 XML 中动态 SQL 的代码, 以字符串的形式拼接到 <script> 标签中即可.

java">    @Insert("<script>" +"insert into user_info " +"<trim prefix=\"(\" suffix=\")\" prefixOverrides=\",\" suffixOverrides=\",\">" +"<if test=\"username != null\">username,</if>" +"<if test=\"password != null\">password,</if>" +"<if test=\"age != null\">age,</if>" +"<if test=\"phone != null\">phone,</if>" +"<if test=\"gender != null\">gender</if>" +"</trim> values " +"<trim prefix=\"(\" suffix=\")\" prefixOverrides=\",\" suffixOverrides=\",\">" +"<if test=\"username != null\">#{username},</if>" +"<if test=\"password != null\">#{password},</if>" +"<if test=\"age != null\">#{age},</if>" +"<if test=\"phone != null\">#{phone},</if>" +"<if test=\"gender != null\">#{gender}</if>" +"</trim>" +"</script>")int insertUser4(UserInfo userInfo);

3. 动态查询

进行 select 查询操作时, 常常会用到 where 关键字进行条件查询, 进行条件查询时, 同样也可以通过 if 标签进行动态查询:

但是, 当参数 phone 为 null 时, 就发生错误了:

出错的原因很简单: 因为 phone 属性的值为 null, 导致 where 关键字后跟的是 "and gender", 导致 SQL 语法错误.

解决办法很简单, 使用 trim 标签, 将 and 关键字去除即可:

但是, 当 phone 和 gender 两个属性都为 null 时, 问题又出现了:

这里错误的原因是: phone 和 gender 都为 null, 导致出现了多余的 where 关键字, 导致 SQL 语法错误.

解决方法有两种:

添加 1 = 1, 保证 where 后有条件
使用 <where>, 去除 where 关键字

3.1 添加 1 = 1

在 where 关键字后添加 1 = 1, 保证 where 后有条件. 并在每条拼接的 SQL 片段前添加 and, 保证 SQL 片段可以进行拼接:

java"><select id="selectByCondition" resultType="com.study.mybatis.model.UserInfo">select * from user_infowhere 1 = 1<if test="phone != null">and phone = #{phone}</if><if test="gender != null">and gender = #{gender}</if></select>

3.2 where 标签

使用 where 标签代替 where 关键字:

where 标签有两个作用:

当 where 标签语块内没有内容时, 不会生成 where 关键字
当 where 标签语块内有查询条件时, 会自动生成 where 关键字, 并且会去除最前面的 and 关键字

因此, 即使查询条件全为 null, 或者第一个查询条件前有 and 关键字, where 标签都会帮我们解决这些问题:

但是, 在实际工作中, 要避免使用 where 标签, 因为当我们遗漏传入条件查询的参数时, 程序也会正确执行, 而无任何条件限制的 select 是非常严重的!!

java"><select id="selectByCondition" resultType="com.study.mybatis.model.UserInfo">select * from user_info<where><if test="phone != null">phone = #{phone}</if><if test="gender != null">and gender = #{gender}</if></where>
</select>

4. 动态更新

update 语句也可以结合 if 和 trim 标签, 完成动态更新操作:

java"><update id="updateUser2">update user_infoset<trim suffixOverrides=","><if test="username != null">username = #{username},</if><if test="password != null">password = #{password},</if><if test="age != null">age = #{age}</if></trim>where id = #{id}</update>

4.1 set 标签

除了上文将 if 和 trim 标签结合完成动态的 update 操作外, 也可以使用 set 标签来完成.

set 标签的作用:

生成 set 关键字
去除多余的逗号(只能去除最后一个逗号)

java"><update id="updateUser3">update user_info<set><if test="username != null">username = #{username},</if><if test="password != null">password = #{password},</if><if test="age != null">age = #{age}</if></set>where id = #{id}</update>

5. foreach 标签

在进行数据库操作时, 我们常会在一个条 SQL 语句中, 对数据批量的进行操作, 如下:

sql">SELECT * FROM user_info where id = 1 or id = 2 or id = 3;INSERT into user_info (username, `password`) VALUES ('1', '1'), ('2', '2');UPDATE user_info set delete_flag = 1 WHERE id IN(1, 2, 3);DELETE from user_info WHERE id IN(4, 5, 6);

如果要通过 MyBatis 对数据进行批量处理, 就需要使用 foreach 标签. \

使用 foreach 批量操作数据, 传入的参数必定是一个集合(包括 Map).

foreach 标签中有以下几个关键属性:

collection (必须): 指定要迭代的集合. (如果方法参数是 List，collection 就是 List 的参数名)
item (必须): 指定迭代过程中元素的名称.
index (可选): 表示集合中每个元素的索引
separator (可选): 指定每个元素之间的分隔符
open (可选): 循环开始前要添加的字符串. 通常用于在 IN 语句中添加左括号 (
close (可选): 循环结束后要添加的字符串. 通常用于在 IN 语句中添加右括号 )

java"><!--    DELETE from user_info WHERE id IN(1, 2, 3);--><delete id="deleteUser2">delete from user_info whereid in<foreach collection="ids" open="(" close=")" item="id" separator=",">#{id}</foreach></delete>

java"><!--    delete FROM user_info where id = 1 or id = 2 or id = 3;--><delete id="deleteUser3">delete from user_info where<foreach collection="ids" item="id" separator="or">id = #{id}</foreach></delete>

java"><!--    insert into user_info (username, `password`) VALUES ('1', '1'), ('2', '2');--><insert id="insertUser1">insert into user_info (username, password) values<foreach collection="userInfos" item="user" separator=",">(#{user.username}, #{user.password})</foreach></insert>