参考文章:
https://blog.csdn.net/xu1916659422/article/details/77971696/
https://blog.csdn.net/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>