Mysql Mybatis 批量修改数据

Mysql Mybatis 批量修改数据

Mapper

Integer updateListPO(List<ProjectQuotationItemPO> upateList);

方法一：

<update id="updateListPO">
<foreach collection="list" separator=";" item="item">
UPDATE project_quotation_item
SET product_num = #{item.productNum},
product_price_total = #{item.productPriceTotal},
product_price_wttax = #{item.productPriceWttax},
certificate = #{item.certificate},
deliver_date = #{item.deliverDate},
product_brand = #{item.productBrand},
producer = #{item.producer},
exp = #{item.exp},
is_deleted = #{item.isDeleted},
remark = #{item.remark},
substitute_type = #{item.substituteType},
quotation_status = #{item.quotationStatus}
WHERE
id = #{item.id}
</foreach>
</update>

需要在SQL连接的URL添加 allowMultiQueries=true 但是根据不同的Mysql版本或者是其他环境因素，可能还是会执行失败。

方法二：

<update id="updateListPO">
<!-- 优化后的逻辑 -->
update project_quotation_item
<set>
<trim prefix="product_num = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.productNum}
</foreach>
</trim>
<trim prefix="product_price_total = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.productPriceTotal}
</foreach>
</trim>
<trim prefix="product_price_wttax = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.productPriceWttax}
</foreach>
</trim>
<trim prefix="certificate = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.certificate}
</foreach>
</trim>
<trim prefix="deliver_date = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.deliverDate}
</foreach>
</trim>
<trim prefix="product_brand = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.productBrand}
</foreach>
</trim>
<trim prefix="producer = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.producer}
</foreach>
</trim>
<trim prefix="exp = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.exp}
</foreach>
</trim>
<trim prefix="is_deleted = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.isDeleted}
</foreach>
</trim>
<trim prefix="remark = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.remark}
</foreach>
</trim>
<trim prefix="substitute_type = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.substituteType}
</foreach>
</trim>
<trim prefix="quotation_status = case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.quotationStatus}
</foreach>
</trim>
</set>
<where>
id in
<foreach collection="list" separator="," item="item" open="(" close=")">
#{item.id}
</foreach>
</where>
</update>

拼接处的SQL语句

update project_quotation_item
SET product_num = case when id=2203220000003867 then 101

when id=2203220000003868 then 10

when id=2203220000003869 then 20 end,
product_price_total = case when id=2203220000003867 then 1111

when id=2203220000003868 then 220

when id=2203220000003869 then 660 end,
product_price_wttax = case when id=2203220000003867 then 11

when id=2203220000003868 then 22

when id=2203220000003869 then 33 end,
certificate = case when id=2203220000003867 then "11"

when id=2203220000003868 then "22"

when id=2203220000003869 then "33" end,
deliver_date = case when id=2203220000003867 then 11

when id=2203220000003868 then 22

when id=2203220000003869 then 33 end,
product_brand = case when id=2203220000003867 then "11"

when id=2203220000003868 then "22"

when id=2203220000003869 then "33" end,
producer = case when id=2203220000003867 then "11"

when id=2203220000003868 then "22"

when id=2203220000003869 then "33" end,
exp = case when id=2203220000003867 then 11

when id=2203220000003868 then 22

when id=2203220000003869 then 33 end,
is_deleted = case when id=2203220000003867 then 0

when id=2203220000003868 then 0

when id=2203220000003869 then 0 end,
remark = case when id=2203220000003867 then null

when id=2203220000003868 then null

when id=2203220000003869 then null end,
substitute_type = case when id=2203220000003867 then "11"

when id=2203220000003868 then "22"

when id=2203220000003869 then "33" end,
quotation_status = case when id=2203220000003867 then 2

when id=2203220000003868 then 2

when id=2203220000003869 then 2 end
WHERE id in
(
2203220000003867
,
2203220000003868
,
2203220000003869
)