MyBatis 批量更新

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