本文概要：介绍了批量更新和批量插入的实现。批量更新通过case-when来实现；批量插入通过insert into table values (…),(….);
1 批量更新 case-when
1. case -when使用
|
1 2 3 4 5 6 7 |
UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END WHERE id IN (1,2,3) |
2. 对应mapper.xml的设置
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<update id="batchUpdateAttribute" parameterType="java.util.List"> UPDATE ct_package SET attribute = CASE id <foreach collection="list" item="item" index="index" separator=" "> WHEN #{item.id} THEN #{item.attribute} </foreach> END WHERE id IN <foreach collection="list" item="item" index="index" open="(" close=")" separator=","> #{item.id} </foreach> </update> |
2 使用
（1）使用时要注意两点
要用异常将sql操作包起来。这样就好记录批处理失败的操作。
调用这个dao操作时，首先验证参数list是否为空。
（2）如下代码：
|
1 2 3 4 5 6 7 8 9 10 11 |
if (CollectionUtils.isNotEmpty(packageEntities)) { try { packageDao.batchUpdateAttribute(packageEntities); } catch (Exception e) { StringBuilder packageIds = new StringBuilder(); for (PackageEntity packageEntity : packageEntities) { packageIds.append(packageEntity.getId()).append(","); } logger.error("批量更新package的赠品字段失败，packageIds={},reason:{}", packageIds, e.getMessage(), e); } } |
2 批量增加
1. mapper的配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<insert id="insertOrUpdateRoomDetail" parameterType="java.util.List"> insert into room_detail ( room_id, date_time, price, room_count, room_status, room_consume, cash_back, ext, create_time, price_cut ) values <foreach collection="list" item="item" index="index" separator="," open="(" close=")"> #{item.roomId}, #{item.dateTime}, #{item.price}, #{item.count}, #{item.status}, #{item.consume}, #{item.cashBack}, #{item.ext}, #{item.createTime}, #{item.priceCut} </foreach> on duplicate key update price=VALUES(price), room_count=VALUES(room_count), room_status=VALUES(room_status), cash_back=VALUES(cash_back), ext=VALUES(ext), price_cut=VALUES(price_cut) </insert> |
3 相关问题
1. 批量操作时要注意sql的长度是有限制的，默认为1m.
