mybatis 批量更新

参考文章：

https://blog.youkuaiyun.com/xu1916659422/article/details/77971696/

https://blog.youkuaiyun.com/xyjawq1/article/details/74129316

最近用到了批量更新操作，网络了一下，具体采用了下面的方式。

目前没有考虑执行效率。

 示例一

 <update id="checkBatchUpdate" parameterType="java.util.List">
        update warehouse_inventory
        <trim prefix="set" suffixOverrides=",">
            <!-- 总库存-->
            <trim prefix="total_store =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.totalStore != null">
                        when id=#{item.id} then #{item.totalStore,jdbcType=DECIMAL}
                    </if>
                </foreach>
            </trim>
            <!-- 可用库存-->
            <trim prefix="apply_store =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.applyStore != null">
                        when id=#{item.id} then #{item.applyStore,jdbcType=DECIMAL}
                    </if>
                </foreach>
            </trim>
            <!-- 占用库存-->
            <trim prefix="occ_store =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.occStore != null">
                        when id=#{item.id} then #{item.occStore,jdbcType=DECIMAL}
                    </if>
                </foreach>
            </trim>
            <!-- 冻结库存-->
            <trim prefix="frozen_store =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.frozenStore != null">
                        when id=#{item.id} then #{item.frozenStore,jdbcType=DECIMAL}
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_user =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.updateUser != null">
                        when id=#{item.id} then #{item.updateUser,jdbcType=BIGINT}
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_time =case" suffix="end,">
                <foreach collection="list" item="item">
                        when id=#{item.id} then now()
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="item">
                id = #{item.id}
            </foreach>
        </where>
    </update>

mapper接口层：

Integer checkBatchUpdate(@Param("list") List<WarehouseInventory> list);

我这里list设置两条数据ids【354910763363991553，354910763368185857】，最后转换后的sql：

2019-08-15 19:04:24 [main] DEBUG c.r.s.w.m.W.checkBatchUpdate - ==>  Preparing: update warehouse_inventory set total_store =case when id=? then ? when id=? then ? end, apply_store =case when id=? then ? when id=? then ? end, occ_store =case when id=? then ? when id=? then ? end, frozen_store =case when id=? then ? when id=? then ? end, update_time =case when id=? then now() when id=? then now() end WHERE id = ? or id = ?  
2019-08-15 19:04:24 [main] DEBUG c.r.s.w.m.W.checkBatchUpdate - ==> Parameters: 354910763363991553(Long), 100(BigDecimal), 354910763368185857(Long), 100(BigDecimal), 354910763363991553(Long), 100(BigDecimal), 354910763368185857(Long), 100(BigDecimal), 354910763363991553(Long), 0.00(BigDecimal), 354910763368185857(Long), 0.00(BigDecimal), 354910763363991553(Long), 0.00(BigDecimal), 354910763368185857(Long), 0.00(BigDecimal), 354910763363991553(Long), 354910763368185857(Long), 354910763363991553(Long), 354910763368185857(Long) 
2019-08-15 19:04:24 [main] DEBUG c.r.s.w.m.W.checkBatchUpdate - <==    Updates: 2 

示例二

这里还用到了ON DUPLICATE KEY UPDATE，作用：没有新增，有则更新。

<!--更新暂存区库存-->
    <insert id="updateInventoryInTemp" parameterType="java.util.List">

        insert into warehouse_inventory
        values
        <foreach collection="list" index="index" separator="," item="item">
            (#{item.id,jdbcType=BIGINT},
            #{item.storeId,jdbcType=BIGINT},
            #{item.locationId,jdbcType=BIGINT},
            #{item.skuId,jdbcType=BIGINT},
            #{item.totalStore,jdbcType=DECIMAL},
            #{item.applyStore,jdbcType=DECIMAL},
            #{item.occStore,jdbcType=DECIMAL},
            #{item.frozenStore,jdbcType=DECIMAL},
            #{item.customerId,jdbcType=BIGINT},
            #{item.qualityType,jdbcType=TINYINT},
            #{item.batchNo,jdbcType=BIGINT},
            now(),
            #{item.createUser,jdbcType=BIGINT},
            #{item.updateTime,jdbcType=TIMESTAMP},
            #{item.updateUser,jdbcType=BIGINT}
            )
        </foreach>
        on duplicate key update frozen_store = (case
        <foreach collection="list" item="item" separator=" " index="index" open=" " close="">
            when id = #{item.id,jdbcType=BIGINT} then
            frozen_store + #{item.frozenStore,jdbcType=DECIMAL}
        </foreach>
        end
        ),
        total_store = (case
        <foreach collection="list" item="item" separator=" " index="index" open=" " close="">
            when id = #{item.id,jdbcType=BIGINT} then
            total_store + #{item.totalStore,jdbcType=DECIMAL}
        </foreach>
        END
        ),
        update_user = (case
        <foreach collection="list" item="item" separator=" " index="index" open=" " close="">
            when id = #{item.id,jdbcType=BIGINT} then
            #{item.updateUser,jdbcType=BIGINT}
        </foreach>
        end),
        update_time = (case
        <foreach collection="list" item="item" separator=" " index="index" open=" " close="">
            when id = #{item.id,jdbcType=BIGINT} then
            now()
        </foreach>
        end)
    </insert>