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
            )