Java实现Mysql批量插入与更新

第一、批量插入语句

    @Insert({"<script>","INSERT INTO TABLE_NAME ("+ "ID,"+ "IS_DELETE,"+ "GMT_CREATE,"+ "GMT_MODIFIED"+ ")VALUES","<foreach collection = 'list' item = 'item' separator = ','>","(" + "#{item.id},"+ "0,"+ "now(),"+ "now()"+ ")","</foreach>","</script>"})

第二、批量更新语句

    @Update("<script>"+ " UPDATE TABLE_NAME SET "+ " <trim prefix='OWNER_ID =case' suffix='end,'> "+" <foreach collection='list' item='item'> "+ " when ID = #{item.id} then #{item.ownerId} "+ " </foreach> "+" </trim> "+ " GMT_MODIFIED = NOW() "+ " WHERE ID in "+" <foreach collection='list' index='index' item='item' separator=',' open='(' close=')'> "+" #{item.id} "+" </foreach> "+ " </script>")

第三，工具类

public final class BatchHandler {private static final int BATCH_OPERATE_SIZE = 500;public static <T> void doBatch(List<T> coll,Consumer<Collection<T>> consumer) {doBatch(coll, BATCH_OPERATE_SIZE, consumer);}/*** @param coll:             分片集合* @param batchOperateSize: 分片size* @param consumer:         批量消费过程* @description 集合分片处理*/public static <T> void doBatch(List<T> coll,Integer batchOperateSize,Consumer<Collection<T>> consumer) {for (int i = 0; i < coll.size(); i += batchOperateSize) {if (i + batchOperateSize > coll.size()) {consumer.accept(coll.subList(i, coll.size()));} else {consumer.accept(coll.subList(i, i + batchOperateSize));}}}
}

第四、调用方式

BatchHandler.doBatch(list, paramList -> service.batchAdd((List<Object>) paramList));

欢迎大家积极留言交流学习心得，点赞的人最美丽！