<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>
如果这些条件没有一个能匹配上会发生什么?最终这条 SQL 会变成这样:
SELECT * FROM BLOG
WHERE
这会导致查询失败。如果仅仅第二个条件匹配又会怎样?这条 SQL 最终会是这样:
SELECT * FROM BLOG
WHERE
AND title like ‘someTitle'
使用 trim标签可以完成where标签相同的功能
<trim prefix="WHERE" prefixOverrides="AND">
<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>
</trim>
使用trim标签结合case when实现批量更新
Demo1
@Update({
<script>+
UPDATE xxxxxx
//去掉最后的,
<trim prefix='set', suffixOverrides=','>+
//以end,结尾
<trim prefix='MGNIP = case', suffix='end,'>+
<foreach collection="list" index="index" item="item"> +
<if test='item.mgntIp' != null and item.mgntIp != \"\"> +
when ID = #{item.id}+
then #{item.mgntIp}
</if> +
</foreach> +
</trim> +
<trim prefix='DEVICENAME= case', suffix='end,'>+
<foreach collection="list" index="index" item="item"> +
<if test='item.deviceName' != null and item.deviceName != \"\"> +
when ID = #{item.id}+
then #{item.deviceName}
</if> +
</foreach> +
</trim>
<where> +
<foreach collection="list" index="index" item="item" separator = " or "> +
ID = #{item.id} and TENANTID = #{item.tenantId}
</foreach> +
</where>
</script>
})
int updateUnMgntDevices(List<UnMgntDevice> UnMgntDevices);
Demo2
@Update({
UPDATE hr_emp_employee
SET `on_job` = CASE
WHEN `status` IN
<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
#{id}
</foreach> THEN 0
ELSE 1
END
WHERE 1=1
})
void updateEmp(@Param("list") List<String> list);