mybatis 批量更新语句

最新推荐文章于 2024-12-10 07:30:00 发布

土豆牛肉盖浇面

最新推荐文章于 2024-12-10 07:30:00 发布

阅读量358

点赞数

分类专栏： mybatis

本文链接：https://blog.youkuaiyun.com/qq_35732831/article/details/87933083

版权

mybatis 专栏收录该内容

1 篇文章

订阅专栏

本文介绍如何使用MyBatis进行批量更新，通过案例展示了针对ID为1、2、3的记录，更新course表中name和title字段的方法，利用CASE语句实现条件赋值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

dao层写法：(因为我的表名是外部拼接的所以需要传参数)
Integer updateBatch(@Param(value = "orderTableName") String orderTableName,
                    @Param(value = "list") List<OrderInfo> list);

语句实现：
<update id="updateBatch" parameterType="java.util.List">
    UPDATE ${orderTableName}
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="order_no =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderNo !=null">
                    when id=#{item.id} then #{item.orderNo}
                </if>
            </foreach>
        </trim>
        <trim prefix="evaluate_score =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.evaluateScore != null">
                    when id=#{item.id} then #{item.evaluateScore}
                </if>
            </foreach>
        </trim>
        <trim prefix="user_receive_time =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.userReceiveTime !=null">
                    when id=#{item.id} then #{item.userReceiveTime}
                </if>
            </foreach>
        </trim>
        <trim prefix="message_evaluate_result =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.messageEvaluateResult !=null">
                    when id=#{item.id} then #{item.messageEvaluateResult}
                </if>
            </foreach>
        </trim>
        <trim prefix="order_status =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderStatus != null">
                    when id=#{item.id} then #{item.orderStatus}
                </if>
            </foreach>
        </trim>
        <trim prefix="call_receive_result =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.callReceiveResult !=null">
                    when id=#{item.id} then #{item.callReceiveResult}
                </if>
            </foreach>
        </trim>
        <trim prefix="is_pay_call =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.isPayCall != null">
                    when id=#{item.id} then #{item.isPayCall}
                </if>
            </foreach>
        </trim>
        <trim prefix="is_evaluate_message =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.isEvaluateMessage !=null">
                    when id=#{item.id} then #{item.isEvaluateMessage}
                </if>
            </foreach>
        </trim>
        <trim prefix="order_type =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderType != null">
                    when id=#{item.id} then #{item.orderType}
                </if>
            </foreach>
        </trim>
        <trim prefix="call_pay_result =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.callPayResult !=null">
                    when id=#{item.id} then #{item.callPayResult}
                </if>
            </foreach>
        </trim>
        <trim prefix="message_pay_result =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.messagePayResult != null">
                    when id=#{item.id} then #{item.messagePayResult}
                </if>
            </foreach>
        </trim>
        <trim prefix="mobile =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.mobile !=null">
                    when id=#{item.id} then #{item.mobile}
                </if>
            </foreach>
        </trim>
        <trim prefix="order_send_time =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderSendTime != null">
                    when id=#{item.id} then #{item.orderSendTime}
                </if>
            </foreach>
        </trim>
        <trim prefix="call_evaluate_result =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.callEvaluateResult !=null">
                    when id=#{item.id} then #{item.callEvaluateResult}
                </if>
            </foreach>
        </trim>
        <trim prefix="is_pay_message =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.isPayMessage !=null">
                    when id=#{item.id} then #{item.isPayMessage}
                </if>
            </foreach>
        </trim>
        <trim prefix="order_add_time =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderAddTime !=null">
                    when id=#{item.id} then #{item.orderAddTime}
                </if>
            </foreach>
        </trim>
        <trim prefix="shop_id =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.shopId !=null">
                    when id=#{item.id} then #{item.shopId}
                </if>
            </foreach>
        </trim>
        <trim prefix="message_receive_result =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.messageReceiveResult !=null">
                    when id=#{item.id} then #{item.messageReceiveResult}
                </if>
            </foreach>
        </trim>
        <trim prefix="order_pay_time =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderPayTime !=null">
                    when id=#{item.id} then #{item.orderPayTime}
                </if>
            </foreach>
        </trim>
        <trim prefix="user_id =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.userId !=null">
                    when id=#{item.id} then #{item.userId}
                </if>
            </foreach>
        </trim>
        <trim prefix="is_evaluate_call =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.isEvaluateCall !=null">
                    when id=#{item.id} then #{item.isEvaluateCall}
                </if>
            </foreach>
        </trim>
        <trim prefix="admin_id =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.adminId !=null">
                    when id=#{item.id} then #{item.adminId}
                </if>
            </foreach>
        </trim>
        <trim prefix="order_fee =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderFee !=null">
                    when id=#{item.id} then #{item.orderFee}
                </if>
            </foreach>
        </trim>
        <trim prefix="logistics_receive_time =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.logisticsReceiveTime !=null">
                    when id=#{item.id} then #{item.logisticsReceiveTime}
                </if>
            </foreach>
        </trim>
        <trim prefix="order_evaluate_time =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.orderEvaluateTime !=null">
                    when id=#{item.id} then #{item.orderEvaluateTime}
                </if>

            </foreach>
        </trim>
    </trim>
    WHERE id IN
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
        #{item.id,jdbcType=BIGINT}
    </foreach>
</update>
注： 
   case 为字段名称

其结果相当于：

UPDATE course
SET name = CASE id
WHEN 1 THEN 'name1'
WHEN 2 THEN 'name2'
WHEN 3 THEN 'name3'
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)