动态 SQL
MyBatis 允许在运行时动态拼接 sql 语句,主要包括以下元素:
- if
- choose(when,otherwise)
- trim(where,set)
- foreach
if
就像字面意思一样,<if>
标签就是一个条件判断,常常使用在 where 子句中,例如:
<select id="selectByExample" resultType="Article">
select *
from article
where title like #{title}
<if test="author!=null">
and author = #{author}
</if>
</select>
就像代码显示的那样,<if>
标签按照 test
属性表示的语句进行判断,如果满足条件就执行标签内的语句,否则就不执行
上面的语句表示的意思就是*首先模糊搜索 title 列,如果有传入 author 参数,就搜索 author 列
如果有多个条件判断,也可以使用多个 <if>
:
<select id="selectByExample" resultType="Article">
select *
from article
where title like #{title}
<if test="author!=null">
and author = #{author}
</if>
<if test="createTime!=null">
and create_time = #{createTime}
</if>
<if test="updateTime!=null">
and update_time = #{updateTime}
</if>
</select>
MyBatis 会根据条件拼接 sql,例如如果传入 title 和 createTime 参数:
==> Preparing: select * from article where title like ? and create_time = ?
choose
choose 类似于 Java 中的 switch 语句,也就是从多条语句中,选择一条执行,它的子元素包括 when 和 otherwise
<select id="selectByLike" resultType="Article">
select * from article where title like #{title}
<choose>
<when test="author!=null">
and author like #{author}
</when>
<when test="createTime!=null">
and create_time > #{createTime}
</when>
<otherwise>
and create_time > "2021-01-01"
</otherwise>
</choose>
</select>
choose 依次执行每一条 when 的判断,如果满足就执行这条 when 的语句并结束,否则执行下一条 when 子句,如果都不满足,就执行 otherwise 内的语句
例:
Article example = new Article();
example.setTitle("%t%");
example.setAuthor("%a%");
example.setCreateTime(Date.valueOf("2021-09-08"));
List<Article> articles = mapper.selectByLike(example);
sql:
==> Preparing: select * from article where title like ? and author like ?
就像显示的那样,虽然传入了 createTime 参数,但是它并没有出现在 sql 语句中
trim
<if>
标签实现了条件判断,但是它存在一个问题,比如如果我们想把所有的 where 子句都设为动态的:
<select id="selectByExample" resultType="Article">
select *
from article
where
<if test="title!=null">
title like #{title}
</if>
<if test="author!=null">
and author = #{author}
</if>
<if test="createTime!=null">
and create_time = #{createTime}
</if>
<if test="updateTime!=null">
and update_time = #{updateTime}
</if>
</select>
此时如果没有传入 titie 参数,sql 语句就会变成这样:
==> Preparing: select * from article where and create_time = ? // 只传入createTime参数
或者这样:
==> Preparing: select * from article where // 不传入任何参数
这毫无疑问会导致查询出错,为了解决这个问题,可以使用 <where>
标签
where
<where>
标签的使用很简单,只需要用它代替 where 关键字,将动态 sql 放在 <where>
标签内即可
<select id="selectByExample" resultType="Article">
select *
from article
<where>
<if test="title!=null">
title like #{title}
</if>
<if test="author!=null">
and author = #{author}
</if>
<if test="createTime!=null">
and create_time = #{createTime}
</if>
<if test="updateTime!=null">
and update_time = #{updateTime}
</if>
</where>
</select>
如果 <where>
内有语句要执行,它才会在前面加上 where 关键字,另外如果拼接的 sql 语句前有多余的 and 或者 or 关键字,<where>
会将其删掉
set
<set>
和 <where>
类似,它会添加一个 set 关键字并去掉多余的 ,
<update id="updateByExample" parameterType="Article">
update article
<set>
<if test="title!=null">
title = #{title},
</if>
<if test="author!=null">
author = #{author},
</if>
<if test="createTime!=null">
create_time = #{createTime},
</if>
<if test="updateTime!=null">
update_time = #{updateTime}
</if>
</set>
where id=#{id}
</update>
trim
如果 <where>
和 <set>
不足以完成需求,还可以使用自定义的 <trim>
,它有四个属性:
- prefix:前缀,在语句的最前面添加
- prefixOverride:前缀溢出,移除拼接完成的 sql 语句的指定前缀
- suffix:后缀,在语句的最前面添加
- suffixOverride:后缀溢出,移除拼接完成的 sql 语句的指定后缀
例如:
<where>
等价于
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
<set>
等价于
<trim prefix="SET" suffixOverrides=",">
...
</trim>
这些属性其实不一定是 sql 中会使用的,例如:
<trim prefix="abc" prefixOverrides="and |or " suffix=")" suffixOverrides="bc">
...
</trim>
这样的语句一样可以,它移除语句前多余的 and 或 or 关键字,移除语句后多余的 bc 关键字,并在语句前添加 abc,在语句后添加 bc,最后的效果大概是这样:
==> Preparing: select * from article abc author = ? )
当然,这样的语句没什么实际意义,也不能正常执行
foreach
<if>
和 <choose>
实现了条件判断,而 <foreach>
就实现了循环遍历,常用于 in 语句,例如:
<select id="selectAuthorIn" resultType="Article">
select * from article
where author in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item},#{index}
</foreach>
</select>
它有 6 个属性:
- collection:需要遍历的参数名
- item:迭代集合时元素的别名
- index:元素的序号,如果传入的是 map 则是 key
- open:拼接出的 sql 的起始字符
- close:拼接出的 sql 的结束字符
- separator:拼接 sql 时元素之间的分隔符
例:
List<String> list = new ArrayList<>();
list.add("a1");
list.add("a2");
mapper.selectAuthorIn(list);
拼接出的 sql 语句如下:
==> Preparing: select * from article where author in ( ?,? , ?,? )
==> Parameters: a1(String), 0(Integer), a2(String), 1(Integer)
除了 collection 和 item 以外其他的都是可选项
collection 属性
foreach 最容易出错的属性就是 collection,它的值为需要遍历的参数名而非类型,这个参数必须是个可迭代对象,通常就是各种集合类、数组
根据参数类型和数量的不同,collection 的值也不同:
-
当传入参数只有一项且为 List 时,collection 的值默认为 list
-
当传入参数只有一项且为 数组时,collection 的值默认为 array
-
传入参数是其他类型或者传入多个参数时,可以在参数上加
@Param("keyName")
注解:<foreach collection="map" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach>
List<Article> selectAuthorIn(@Param("map") Map<Integer,String> authorMap);
此时 keyName 就是 collection 的值,另外需要注意如果使用了
@Param("keyName")
注解,默认的 list 或 array 就会失效或者将参数封装在一个 map 中
List<Article> selectAuthorIn(Map<String,Map<Integer,String>> param);
Map<Integer,String> map = new HashMap<>(); map.put(1,"a1"); map.put(2,"a2"); Map<String,Map<Integer,String>> param = new HashMap<>(); param.put("map",map); // 此时 param 中的键就是 collection 的值 mapper.selectAuthorIn(param);
或者封装在一个对象中,通过对象的字段名取参数,此时 collection 就是字段名。
总的来说和 MyBatis 传入多个参数时的方法类似