1. CASE WHEN
文档 https://dev.mysql.com/doc/refman/8.0/en/case.html
1.1 方式一
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE default_result
END;
示例：
select id, quantity,
CASE
WHEN quantity > 5 THEN "gt"
WHEN quantity = 5 THEN "eq"
ELSE "lt"
END
from boot_order;
1.2 方式二
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END
示例：
select id, tpCode,
CASE tpCode
WHEN 'STO' THEN "申通"
WHEN 'SF' THEN "顺丰"
ELSE "其他"
END as name
from boot_order;
2. 更新
2.1 Mapper 接口
public interface OrderMapper {
Integer update(OrderRequest request);
Integer updateVersion(OrderRequest request);
Integer updateBatch(List<OrderRequest> list);
Integer updateBatch2(List<OrderRequest> list);
}
2.2 单条更新
<update id="update" parameterType="OrderRequest">
update boot_order
<set>
<if test="tpCode != null">
tpCode=#{tpCode},
</if>
<if test="quantity != null">
quantity=#{quantity},
</if>
</set>
WHERE id = #{id};
</update>
<set> 标签用于生成动态的UPDATE语句中的SET部分，并且它会自动处理逗号，只在需要的地方添加
打印日志：
==> Preparing: update boot_order SET quantity=? WHERE id = ?;
==> Parameters: 10(Integer), 1(Integer)
<== Updates: 1
3. 批量更新
3.1 SQL 语句
update boot_order
set quantity =
case id
when 1 then 10
when 2 then 20
end
where id in (1, 2);
3.2 更新单字段
<update id="updateBatch" parameterType="java.util.List">
update boot_order
set quantity =
<foreach collection="list" item="item" index="index" open="case id" separator=" " close="end">
when #{item.id} then #{item.quantity}
</foreach>
where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>
打印日志：
==> Preparing: update boot_order set quantity = case id when ? then ? when ? then ? end where id in ( ? , ? )
==> Parameters: 1(Integer), 10(Integer), 2(Integer), 20(Integer), 1(Integer), 2(Integer)
<== Updates: 2
3.3 更新多字段
<update id="updateBatch" parameterType="java.util.List">
update boot_order
<set>
<trim prefix="quantity = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.quantity != null">
when id=#{item.id} then #{item.quantity}
</if>
</foreach>
</trim>
<trim prefix="tpCode = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.tpCode != null">
when id=#{item.id} then #{item.tpCode}
</if>
</foreach>
</trim>
</set>
where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>
打印日志：
==> Preparing: update boot_order SET quantity = case when id=? then ? when id=? then ? end, tpCode = case when id=? then ? when id=? then ? end where id in ( ? , ? )
==> Parameters: 1(Integer), 10(Integer), 2(Integer), 20(Integer), 1(Integer), STO(String), 2(Integer), SF(String), 1(Integer), 2(Integer)
<== Updates: 2
update boot_order
SET quantity = case
when id=? then ?
when id=? then ?
end,
tpCode = case
when id=? then ?
when id=? then ?
end
where id in ( ? , ? )
4. 批量更新使用 <trim> 标签
4.1 SQL 语句
update boot_order
set quantity = case
when id = 1 then 1
when id = 2 then 2
end
where id in (1, 2);
4.2 示例
<update id="updateBatch2" parameterType="java.util.List">
update boot_order
<trim prefix="set" suffixOverrides=",">
<trim prefix="quantity = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.quantity != null">
when id=#{item.id} then #{item.quantity}
</if>
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>
<trim> 标签用于动态调整SQL语句，主要为了方便地添加或删除字段前后的逗号、WHERE关键字以及其他一些前缀或后缀。
属性：
prefix：在包裹的内容前面添加的字符串。
prefixOverrides：去除内容前面多余的字符串，常用于去除多余的逗号等。
suffix：在包裹内容后面添加的字符串。
suffixOverrides：去除内容后面多余的字符串，常用于去除多余的逗号等。
打印日志：
==> Preparing: update boot_order set quantity = case when id=? then ? when id=? then ? end where id in ( ? , ? )
==> Parameters: 1(Integer), 10(Integer), 2(Integer), 20(Integer), 1(Integer), 2(Integer)
<== Updates: 2
