【mybatis】mybatis中批量插入 批量更新 batch 进行insert 和 update,或者切割LIst进行批量操作
==================================================================
分别展示 mybatis 批量新增 和 批量更新 的操作:
controller层:
goodsService.batchInsert(insertGoodsList);
goodsService.batchUpdate(updateGoodsList);
service层:
切割List的方法【https://www.cnblogs.com/sxdcgaq8080/p/9376947.html】【建议分批次处理,每次处理1000条】【实际根据每条数据的大小,自行划分】
@Override @Transactional public int batchInsert(List<Goods> list) { int a = 0; List<List<Goods>> goodsAllList = ListUtils.splitListBycapacity(list,1000); for (List<Goods> goodsList : goodsAllList) { a += mapper.batchInsert(goodsList); } return a; } @Override @Transactional public int batchUpdate(List<Goods> list) { int a = 0; List<List<Goods>> goodsAllList = ListUtils.splitListBycapacity(list,1000); Map<String,Object> map = new HashMap<>(); for (List<Goods> goodsList : goodsAllList) { map.put("list",goodsList); a += mapper.batchUpdate(map); } return a; }
Mapper.java层
int batchInsert(List<Goods> list); int batchUpdate(Map<String,Object> map);
Mapper.xml层
【注意,batchUpdate的原理,是循环拼接sql,一次连接数据库,执行多条update语句】
<insert id="batchInsert"> INSERT INTO goods (create_date,update_date,create_id,update_id,enabled, tenement_id,uid,name,py_all,py_head, outer_id,outer_code,mnemonic_code,del_flag,enabled_flag, goods_type_uid,url,bar_cide,sale_price,integral, scan_name,brand_uid,en_name) VALUES <foreach collection="list" item="item" separator=","> (#{item.createDate},#{item.updateDate},#{item.createId},#{item.updateId},#{item.enabled}, #{item.tenementId},#{item.uid},#{item.name},#{item.pyAll},#{item.pyHead}, #{item.outerId},#{item.outerCode},#{item.mnemonicCode},#{item.delFlag},#{item.enabledFlag}, #{item.goodsTypeUid},#{item.url},#{item.barCide},#{item.salePrice},#{item.integral}, #{item.scanName},#{item.brandUid},#{item.enName}) </foreach> </insert> <update id="batchUpdate" parameterType="java.util.Map"> <foreach collection="list" separator=";" item="goods"> update goods SET update_date = #{goods.updateDate}, update_id = #{goods.updateId}, enabled = #{goods.enabled}, name = #{goods.name}, py_all = #{goods.pyAll}, py_head = #{goods.pyHead}, outer_code = #{goods.outerCode}, mnemonic_code = #{goods.mnemonicCode}, del_flag = #{goods.delFlag}, enabled_flag = #{goods.enabledFlag}, goods_type_uid = #{goods.goodsTypeUid}, url = #{goods.url}, bar_cide = #{goods.barCide}, sale_price = #{goods.salePrice}, integral = #{goods.integral}, scan_name = #{goods.scanName}, brand_uid = #{goods.brandUid}, en_name = #{goods.enName} where outer_id = #{goods.outerId} and tenement_id = #{goods.tenementId} </foreach> </update>
最后如果,批量插入可以成功,但是批量更新失败,可以参考:https://www.cnblogs.com/sxdcgaq8080/p/10565023.html
最后需要注意的是,如果解决了批量更新问题后,还想按照【https://www.cnblogs.com/sxdcgaq8080/p/9100178.html】打印sql,就失效了,sql就不会打印出来了。!!!!
===========================================================================