一、数据库表准备
CREATE TABLE `student` ( `id` bigint NOT NULL COMMENT '用户id', `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名', `address` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
二、持久层代码实现
###Mapper方法 /** * @Project * @Description * @Author songwp * @Date 2023/9/8 9:29 **/ @Mapper public interface StudentMapper extends BaseMapper<Student> { int insertSplice(List<Student> students); } ###Mapper.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.songwp.mapper.StudentMapper"> <insert id="insertSplice"> insert into student (id,name,address) values <foreach collection="students" item="entity" separator=","> (#{entity.id}, #{entity.name}, #{entity.address}) </foreach> </insert> </mapper>
三、业务层代码实现
###批量插入的具体方法 /** * 批量插入方法 * @param list 需要处理的数据 */ public void batchData(List<Student> list) { int count = list.size() - 1; int pageSize = 1000; // 每批次插入的数据量 int threadNum = count / pageSize + 1; // 线程数 CountDownLatch countDownLatch = new CountDownLatch(threadNum); for (int i = 0; i < threadNum; i++) { int startIndex = i * pageSize; int endIndex = Math.min(count, (i + 1) * pageSize); List<Student> subList = list.subList(startIndex, endIndex); threadPoolTaskExecutor.execute(() -> { DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition(); TransactionStatus status = transactionManager.getTransaction(transactionDefinition); try { studentMapper.insertSplice(subList); transactionManager.commit(status); } catch (Exception e) { transactionManager.rollback(status); throw e; } finally { countDownLatch.countDown(); } }); } try { countDownLatch.await(); } catch (InterruptedException e) { e.printStackTrace(); } } ###控制层业务方法 @GetMapping("/batchInsert") @ApiOperation("批量插入大数据量的方法验证") public void batchInsert2() { List<Student> arrayList = new ArrayList<>(); // 模拟数据 for (int i = 0; i < 1000000; i++){ Student student = new Student(i+1,"张三"+i,"陕西西安"+i); arrayList.add(student); } long startTime = System.currentTimeMillis(); batchData(arrayList); long endTime = System.currentTimeMillis(); System.out.println("模拟插入: "+arrayList.size()+" 条学生数据,总共耗时:" + (endTime - startTime)+"毫秒"); }
四、插入的结果耗时
该测试项目已集成Swagger,通过Swagger调用该方法结果如下:
标签:SpringBoot,pageSize,int,list,1000000,插入,万条,id,### From: https://www.cnblogs.com/songweipeng/p/17834119.html