近来多次用到批量操作,整理记录一下
单个条件批量更新:
<update id="updateBatch" parameterType="java.util.List">
update mydata_table
<trim prefix="set" suffixOverrides=",">
<trim prefix="status =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.status !=null ">
when id=#{item.id} then #{item.status}
</if>
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
多条件批量更新:
<update id="updateBatch" parameterType="java.util.List">
update demo_table
<trim prefix="set" suffixOverrides=",">
status=
<foreach collection="list" item="item" open="case " close=" end,">
when field2=#{item.field2} and company_id=#{item.field3} then #{item.status}
</foreach>
create_time =
<foreach collection="list" item="item" open="case " close=" end,">
when field2=#{item.field2} and company_id=#{item.field3} then
<choose>
<when test="item.createTime!=null">
#{item.createTime}
</when>
<otherwise>now()</otherwise>
</choose>
</foreach>
</trim>
WHERE
<foreach collection="list" item="item" open="( " separator=") or (" close=" )">
device_num=#{item.field2} and company_id=#{item.field3}
</foreach>
</update>