MyBatis-批量update

通过循环，依次执行update语句，效率较低

MyBatis写法：

<update id="updateCarrierWayStatus" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" separator=";">
        UPDATE order_main_info
        <set>
            <if test="item != null">
                carrier_way_flag = #{item.carrierWayFlag},
                remarks = #{item.remarks}
            </if>
        </set>
        WHERE customer_order_no = #{item.customerOrderNo}
    </foreach>
</update>

这个方式需要注意两点：

1. 数据连接需要加一个允许批量执行的配置 &allowMultiQueries=true
   

2. list不可以为空，会报空语句错误

2、利用Case When

利用case when 只有一条update执行语句

MyBatis写法：

<update id="updateCarrierWayStatus" parameterType="java.util.List">
        update order_main_info
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="carrier_way_flag = case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.carrierWayFlag != null">
                        when customer_order_no = #{item.customerOrderNo} then #{item.carrierWayFlag}
                    </if>
                </foreach>
            </trim>
            <trim prefix="remarks = case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.remarks != null">
                        when customer_order_no = #{item.customerOrderNo} then #{item.remarks}
                    </if>
                </foreach>
            </trim>
        </trim>
        where customer_order_no in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.customerOrderNo,jdbcType=VARCHAR}
        </foreach>
    </update>

执行效果

update order_main_info set carrier_way_flag = case when customer_order_no = ? then ? end, remarks = case when customer_order_no = ? then ? end where customer_order_no in ( ? )