MyBatis-批量update
1、循环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>
这个方式需要注意两点:
-
数据连接需要加一个允许批量执行的配置 &allowMultiQueries=true
-
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 ( ? )
更多推荐
MyBatis-批量update
发布评论