动态SQL
参考链接:https://mybatis.org/mybatis-3/zh/dynamic-sql.html
概念
动态Sql就是动态的拼接Sql语句,MyBatis使动态Sql变得容易,只需要编写XML
涉及的标签
if
通过if标签可以动态的拼接where语句
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
choose(when、otherwise)
如果想从多个条件中选择一个使用可以使用choose标签,类似switch语句
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
trim(where、set)
下面代码生成的Sql是不正确的
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
SELECT * FROM BLOG
WHERE
如果只匹配第二个条件生成的SQL也是不对的
SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’
这种情况就可以使用where标签,可以自动的去除"前缀"where并去除多余的"AND"和"OR"
where标签等价于下面的trim标签:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
注意空格不可省。
set标签可以动态包含需要更新的列
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
相当于下面的trim标签:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
所以说可以通过trim标签来自定义行为
foreach
foreach标签用来对集合(可迭代对象)进行遍历,比如构建In语句
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
注解中使用动态SQL
要在带注解的映射器接口类中使用动态 SQL,可以使用 script 元素。比如:
@Update({"<script>",
"update Author",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" <if test='email != null'>email=#{email},</if>",
" <if test='bio != null'>bio=#{bio}</if>",
" </set>",
"where id=#{id}",
"</script>"})
void updateAuthorValues(Author author);