Mybatis百万数据插入(含导出）,41. 如何在MyBatis-Plus中实现批量操作？批量插入和更新的最佳实践是什么？

1 一般一次性插入多条数据

传统的sql语句：

 
  1. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
  2. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
  3. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
  4. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
  5. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
  6. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
  7. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
  8. INSERT INTO `table1` ( `field1`, `field2` ) VALUES( "data1", "data2" );
 

 

在mybatis中，一次性插入多条数据的时候是用foreach循环实现的，mapper文件中的语句如下：

 
  1. <insert id="batchInsert" parameterType="java.util.List">
  2. insert into USER (id, name) values
  3. <foreach collection="list" item="model" index="index" separator=",">
  4. (#{model.id}, #{model.name})
  5. </foreach>
  6. </insert>
 

 

转化为：

 
  1. INSERT INTO `table1` ( `field1`, `field2` )
  2. VALUES
  3. ( "data1", "data2" ),
  4. ( "data1", "data2" ),
  5. ( "data1", "data2" ),
  6. ( "data1", "data2" ),
  7. ( "data1", "data2" );
 

 

这个用法看上去是没有问题的，功能也能够实现，但是在一些特殊场景这个用法是不合适的。

当表的列数较多（20+），一次性插入的行数较多（5000+）时，整个插入的耗时十分漫长，一般需要十几分钟，这是不能容忍的。

2 解决方法

在mybatis执行的流程中，用户通过 SqlSession 调用一个方法，SqlSession 通过 Executor 找到对应的 MappedStatement。这一步成为了插入大量数据的关键。

MyBatis Dynamic SQL

https://mybatis.org/mybatis-dynamic-sql/docs/insert.html

2.1 会话对象中执行器的类型

1 默认SIMPLE

每次调用 insert 方法时，MyBatis 都需要创建一个预编译语句 (PreparedStatement) 并执行它。这意味着对于每个单独的插入操作，都会有一定的开销，这就导致了消耗时长成倍的增长。

在MyBatis中，当你使用<foreach>标签来构建一个大的SQL插入语句时，实际上是在构造一个单条SQL语句，只是语句中有很多占位符，这就使得只需要创建一个预编译语句 (PreparedStatement) 并执行它就可以了，这就节省了大量时间。

2 RESUSE

此类型的执行器会重用预编译语句（PreparedStatement）。这意味着对于相同的 SQL 语句，它会重用之前的 PreparedStatement 对象，而不是每次都创建新的 PreparedStatement。这可以减少预编译 SQL 语句的开销，但在某些情况下可能会 引起性能问题，特别是当 SQL 语句频繁变化时。

3 BATCH

这是一种批处理执行器，用于执行批量更新操作。它会将多个 SQL 更新语句合并为一个批量执行的操作。这对于批量插入、更新或删除操作特别有用，因为它可以显著减少网络往返次数和事务提交次数，从而提高性能。

2.2 SIMPLE与BATCH的区别

SIMPLE

  • 单行处理：在这种模式下，每次只读取Excel文件中的一行数据，并立即处理。

  • 即时处理：数据读取后会立即交给相应的处理器进行处理，比如使用 BeanRowHandler 进行处理。

  • 内存占用低：由于每次只处理一行数据，因此对内存的需求较低。

BATCH

  • 批量处理：在这种模式下，数据会按照一定的批次读取和处理，而不是逐行处理，减少了I/O操作次数。

  • 批量提交：数据会被收集到一个批次中，然后一起处理，例如一次性将多行数据提交到数据库。

  • 内存占用较高：由于需要缓存一定数量的数据，因此相较于 SIMPLE 模式，内存占用可能会更高。

总结

  • SIMPLE 模式 更适合于数据量较小或需要立即处理每一行数据的场景。

  • BATCH 模式 更适合于数据量较大且可以批量处理的情况。

3 测试

3.1 准备工作

3.1.1 创建工程

 
  1. <dependencies>
  2. <dependency>
  3. <groupId>org.springframework.boot</groupId>
  4. <artifactId>spring-boot-starter-web</artifactId>
  5. </dependency>
  6.  
  7. <dependency>
  8. <groupId>mysql</groupId>
  9. <artifactId>mysql-connector-java</artifactId>
  10. <scope>runtime</scope>
  11. </dependency>
  12. <dependency>
  13. <groupId>org.mybatis.spring.boot</groupId>
  14. <artifactId>mybatis-spring-boot-starter</artifactId>
  15. <version>2.3.0</version>
  16. </dependency>
  17. <dependency>
  18. <groupId>org.projectlombok</groupId>
  19. <artifactId>lombok</artifactId>
  20. <optional>true</optional>
  21. </dependency>
  22. <dependency>
  23. <groupId>cn.hutool</groupId>
  24. <artifactId>hutool-all</artifactId>
  25. <version>5.8.26</version>
  26. </dependency>
  27. <dependency>
  28. <groupId>org.apache.poi</groupId>
  29. <artifactId>poi-ooxml</artifactId>
  30. <version>4.1.2</version>
  31. </dependency> <dependency>
  32. <groupId>com.alibaba</groupId>
  33. <artifactId>easyexcel</artifactId>
  34. <version>3.3.4</version>
  35. </dependency>
  36. </dependencies>
 

 

3.1.2 创建实体类

 
  1.  
  2.  
  3. @Data
  4. public class student {
  5. private String name;
  6. private int age;
  7. private String sex;
  8. private String address;
  9. private String phone;
  10. private String email;
  11. }
 

 

百万数据表格Excel

 

 

3.2 开始测试

3.2.1 创建”读“方法

 
  1. public static List<student> readExcel(String name){
  2. // 获取桌面路径
  3. String desktopPath = System.getProperty("user.home") + File.separator + "Desktop";
  4. String fileName = name;
  5. String filePath = desktopPath + File.separator + fileName;
  6. // 创建 Excel reader
  7. //运用trycatch确保关闭流并捕获异常
  8. try(ExcelReader reader = ExcelUtil.getReader(new File(filePath))){
  9. List<student> readAll =reader.readAll(student.class);
  10. reader.close();
  11. return readAll;
  12. }catch (Exception e){
  13. e.printStackTrace();
  14. }
  15. return null;
  16. }
 

 

3.2.2 普通插入

 
  1. @PostMapping("/insert")
  2. public Integer insert( String name){
  3. //StopWatch类是 Hutool 工具库中的类,用于测量代码执行时间
  4. StopWatch stopWatch = new StopWatch();
  5. stopWatch.start();
  6. for (student student : readExcel(name)) {
  7. studentService.insert(student);
  8. }
  9. stopWatch.stop();
  10. System.out.println(stopWatch.prettyPrint());
  11. return 0;
  12. }
 

 

这是插入一百条数据。

 

插入一千条数据，用时2.4秒左右

插入两万条数据可以看到因为数据量太大，通过 HTTP 请求读取数据时超时了，不过程序还在运行，耗时32秒左右。

可以看出这个插入方法在数据量比较小的时候，比如几十条，速度还是可以的，但是在数据表列数较多且数据量较大的时候是不适用的，更不要说百万级别的数据了。

3.2.3 foreach插入

 
  1. @PostMapping("/banch")
  2. public Integer insertBanch( String name){
  3. //StopWatch类是 Hutool 工具库中的类,用于测量代码执行时间
  4. StopWatch stopWatch = new StopWatch();
  5. stopWatch.start();
  6. studentService.insertBatch(readExcel(name));
  7. stopWatch.stop();
  8. System.out.println(stopWatch.prettyPrint());
  9. return 0;
  10. }
 

 

直接上强度，就不一个一个的试了，直接插入两万条数据。

可以看出两万条数据只需要4.02秒左右，因为我的excel文件的列很少，只有6列，所以foreach默认的执行器类型SIMPLE勉强还算够用，但还是没有资格越去插入百万条数据只有6列的数据。

3.2.4 BATCH插入

 
  1. @PostMapping("/add1")
  2. public Integer add1( String name){
  3. //StopWatch类是 Hutool 工具库中的类,用于测量代码执行时间
  4. StopWatch stopWatch = new StopWatch();
  5. stopWatch.start();
  6. //获取批量插入的数据
  7. SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
  8. try {
  9. studentDao mapper = session.getMapper(studentDao.class);
  10. // 批量插入数据
  11. // mapper.insertBatch(readExcel(name));
  12. for (student student : readExcel(name)) {
  13. mapper.insert(student);
  14. }
  15. // 提交事务
  16. session.commit();
  17. } catch (Exception e) {
  18. e.printStackTrace();
  19. session.rollback();
  20. }
  21. stopWatch.stop();
  22. System.out.println(stopWatch.prettyPrint());
  23. return 0;
  24. }
 

 

 

25.95，为什么会这样呢？为什么会和上面的普通插入的时间差不多呢？

因为两万条数据在插入时，虽然使用了batch，但是还是和普通插入是一样的，每次调用 insert 方法时，MyBatis 都需要创建一个预编译语句 (PreparedStatement) 并执行它。这意味着对于每个单独的插入操作，都会有一定的开销。

可以看到，insertBatch与BATCH结合使用时，插入两万条数据只需要3.6秒左右，比4.2秒快的并不是很多，那是因为数据量太小的原因，如果加大数据量那么就会拉开两者间的差距，将BATCH插入的优势体现出来。

就用一百万的数据量将两者之间的差距彻底体现出来吧。

那么上面一下子读取所有数据的”读“方法肯定不适用了，那就换一种读的方法。

 
  1. private RowHandler createRowHandler() {
  2. return new RowHandler() {
  3. @Override
  4. public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
  5. Console.log("[{}] [{}] {}", sheetIndex, rowIndex, rowlist);
  6. }
  7. };
  8. }
  9. ExcelUtil.readBySax("aaa.xls", 0, createRowHandler());
 

 

这是hutool工具读取大的Excel文件的方法，就把这个方法略作修改来进行验证试验吧。

 
  1.  
  2. @Service
  3. public class HutoolImportService {
  4.  
  5. @Autowired
  6. private SqlSessionFactory sqlSessionFactory;
  7. @Autowired
  8. private com.by.dao.studentDao studentDao;
  9.  
  10. private List<student> students = new ArrayList<>();
  11.  
  12. public void importExcel() {
  13. String filePath = "C:\\Users\\ljj\\Desktop\\export-20240808194933.xlsx";
  14. System.out.println("开始读取数据………………");
  15. System.out.println("开始插入");
  16. StopWatch stopWatch = new StopWatch();
  17. stopWatch.start();
  18. ExcelUtil.readBySax(filePath, 0, createRowHandler());
  19. stopWatch.stop();
  20. System.out.println(stopWatch.getTotalTimeMillis());
  21. System.out.println("插入数据成功");
  22. }
  23.  
  24. private RowHandler createRowHandler() {
  25. SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
  26. com.by.dao.studentDao mapper = session.getMapper(com.by.dao.studentDao.class);
  27. return new RowHandler() {
  28. @Override
  29. public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
  30. if (rowIndex > 0) {
  31. String name = (String) rowlist.get(0);
  32. Integer age = Integer.valueOf(rowlist.get(1).toString());
  33. String sex = (String) rowlist.get(2);
  34. String address = (String) rowlist.get(3);
  35. String phone = rowlist.get(4).toString();
  36. String email = rowlist.get(5).toString();
  37. student student = new student(name, age, sex, address, phone, email);
  38. students.add(student);
  39. if (students.size() >= 100000) {
  40. // studentDao.insertBatch(students);
  41. mapper.insertBatch(students);
  42. students.clear();
  43. }
  44. }
  45. }
  46. };
  47. }
  48. }
 

 

SIMPLE插入

BATCH插入

可以看到SIMPLE插入所用的时间是79.4秒，BATCH插入所用的时间是66.1秒，当把数据量提升到一百万的时候，BATCH插入比SIMPLE插入快了13.3秒，这样就可以看出在进行大数据量的插入时BATCH的优势所在了。

4 多线程插入百万条数据(优化)

4.1 hutool 多线程插入

4.1.1 创建合理线程池

 
  1. @Configuration
  2. public class DynamicThreadPool {
  3.  
  4. @Bean(name = "threadPoolExecutor")
  5. public ThreadPoolExecutor easyExcelStudentImportThreadPool() {
  6. int processors = Runtime.getRuntime().availableProcessors();//获取系统处理器数量
  7. return new ThreadPoolExecutor(
  8. processors + 1,//最小线程数：系统处理器数量 + 1
  9. processors * 2 + 1,//最大线程数：系统处理器数量 * 2 + 1
  10. 10 * 60,//线程空闲时间：10分钟
  11. TimeUnit.SECONDS,//单位：秒
  12. new LinkedBlockingQueue<>(1000000));//队列长度：1000000
  13. }
  14.  
  15. }
 

 

先给线程池的类命名，这个名字随意些，用线程池的时候用不到。

然后开始配置线程池，运用@Bean注解给线程池命名，name一定要注意，运用线程池的时候需要用到。

设置线程池的最小线程数量是当前系统的处理器核心数，并在此基础上加1来设置核心线程数。

好处：核心线程数通常设置为处理器核心数加上一定的增量，这样可以充分利用系统的处理器资源，提高并行处理能力。增加一个额外的线程是为了处理突发性的任务，以避免线程池立即创建更多的线程。

设置线程池的最大线程数量是处理器核心数的两倍再加上1。

好处：这种设置允许线程池在高负载下扩展更多线程，以处理更多的并发任务。这对于处理短暂的高峰负载特别有用，同时避免了过多线程带来的开销。

空闲线程的存活时间，我谨慎一点设置成10分钟，其实几分钟就够用了，因为我不会让插入的时间超过一分钟。

4.1.2 测试

controller

 
  1. @RequestMapping("/import")
  2. public void importExcel(){
  3. System.out.println("开始导入步骤！");
  4. hutoolImportService.importExcel();
  5. }
 

 

service

 
  1. public void importExcel() {
  2. String filePath = "C:\\Users\\ljj\\Desktop\\export-20240808194933.xlsx";
  3. System.out.println("开始读取数据………………");
  4. System.out.println("开始插入");
  5. StopWatch stopWatch = new StopWatch();
  6. stopWatch.start();
  7. SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
  8.  
  9. // ExcelUtil.readBySax(filePath, 0, new StudentRowHandler(0, 1, 1000000, student.class));
  10.  
  11. ExcelUtil.readBySax(filePath, 0, createRowHandler());
  12.  
  13. stopWatch.stop();
  14. System.out.println(stopWatch.getTotalTimeMillis());
  15. System.out.println("插入数据成功");
  16. }
  17.  
  18. private RowHandler createRowHandler() {
  19. return new RowHandler() {
  20. @Override
  21. public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
  22. if (rowIndex > 0) {
  23. String name = (String) rowlist.get(0);
  24. Integer age = Integer.valueOf(rowlist.get(1).toString());
  25. String sex = (String) rowlist.get(2);
  26. String address = (String) rowlist.get(3);
  27. String phone = rowlist.get(4).toString();
  28. String email = rowlist.get(5).toString();
  29. student student = new student(name, age, sex, address, phone, email);
  30. students.add(student);
  31. if (students.size() >= 100000) {
  32. List<List<student>> lists = ListUtil.split(students, 10000);//将successList分割成多个子列表，每个子列表最多包含10000条记录
  33. CountDownLatch countDownLatch = new CountDownLatch(lists.size());
  34. SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
  35. com.by.dao.studentDao mapper = session.getMapper(com.by.dao.studentDao.class);
  36. for (List<student> list : lists) {
  37. threadPoolExecutor.execute(() -> {//提交任务到线程池中执行
  38. try {
  39. mapper.insertBatch(list);//
  40. } catch (Exception e) {
  41. System.out.println("启动线程失败,错误信息:" + e.getMessage());
  42. } finally {
  43. //执行完一个线程减1,直到执行完
  44. countDownLatch.countDown();
  45. }
  46. });
  47. }
  48.  
  49. try {
  50. //使用 countDownLatch.await() 方法等待所有子任务完成。
  51. //如果当前线程被中断，await 方法将抛出 InterruptedException。
  52. countDownLatch.await();
  53. session.commit();
  54. } catch (Exception e) {
  55. System.out.println("等待所有线程执行完异常,e:" + e);
  56. }
  57. // 提前将不再使用的集合清空，释放资源
  58. students.clear();
  59. lists.clear();
  60. }
  61. }
  62. }
  63. };
  64. }
 

 

4.2 easyExcel 多线程插入

4.2.1 创建线程池

4.2.2 "监听器"实现分片”读“和插入

 
  1. @Servicepublic class ReadListener implements com.alibaba.excel.read.listener.ReadListener<student> {
  2. //成功的集合 private final List<student> successList = new ArrayList<>();
  3. //每次读取100000条 private final static int BATCH_COUNT = 100000;
  4. //注入sqlSessionFactory @Autowired private SqlSessionFactory sqlSessionFactory;
  5. //线程池 @Resource private ThreadPoolExecutor threadPoolExecutor;
  6. //注入dao @Autowired private studentDao studentDao;
  7. /** * 读取数据 * * @param student * @param analysisContext */ @Override public void invoke(student student, AnalysisContext analysisContext) { if (StringUtils.isNotBlank(student.getName())) { successList.add(student); return; } //size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入 if (successList.size() >= BATCH_COUNT) { System.out.println("读取数据：" + successList.size()); saveData(); //清理集合便于GC回收 successList.clear(); } }
  8. /** * 多线程插入 */ private void saveData() {
  9. List<List<student>> lists = ListUtil.split(successList, 10000);//将successList分割成多个子列表，每个子列表最多包含10000条记录
  10. CountDownLatch countDownLatch = new CountDownLatch(lists.size());//使用计数器控制线程同步。创建一个CountDownLatch对象，用于控制多个线程之间的同步和等待。
  11. for (List<student> list : lists) {
  12. threadPoolExecutor.execute(() -> {//提交任务到线程池中执行
  13. SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
  14. com.by.dao.studentDao mapper = session.getMapper(studentDao.class);
  15. StopWatch stopWatch = new StopWatch();
  16. stopWatch.start();
  17. try {
  18. mapper.insertBatch(list);//
  19. stopWatch.stop();
  20. session.commit();
  21. System.out.println("插入数据：" + list.size() + "条,耗时：" + stopWatch.getTotalTimeMillis() + "纳秒。");
  22.  
  23. } catch (Exception e) {
  24. System.out.println("启动线程失败,错误信息:" + e.getMessage());
  25. } finally {
  26. //执行完一个线程减1,直到执行完
  27. countDownLatch.countDown();
  28. }
  29. });
  30. }
  31. try {
  32. //使用 countDownLatch.await() 方法等待所有子任务完成。
  33. //如果当前线程被中断，await 方法将抛出 InterruptedException。
  34. countDownLatch.await();
  35. // session.commit();
  36. } catch (Exception e) {
  37. System.out.println("等待所有线程执行完异常,e:" + e);
  38. }
  39. // 提前将不再使用的集合清空，释放资源
  40. successList.clear();
  41. lists.clear();
  42. }
  43.  
  44. @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { //读取剩余数据 if (CollectionUtils.isNotEmpty(successList)) { System.out.println("读取数据：" + successList.size() + "条。"); saveData(); } }}
 

 

首先设置需要的变量，其中要将线程池注入进来，因为是自己设置的线程池，而且放入了容器，其中加入了Bean注解定义了name，Autowired注解是会报错的，要用Resource注解，通过name注入。

重写ReadListener的invoke方法，这个方法在每次读取一行数据时被调用，从头开始读取数据，首先判断对象返回的name值是否为null，如果不为null放到list集合里面，然后判断集合的大小是否超过了十万，超过了就保存起来放入大集合里面进行保存。

使用 ListUtil.split 方法将 successList 分割成多个子列表，每个子列表最多包含10000条记录。ListUtil.split 用于将列表分割成指定大小的子列表。这种分批处理可以显著提高数据插入数据库的效率，因为它减少了与数据库的交互次数。

使用 CountDownLatch 来同步所有子列表的插入操作完成。lists.size() 指定了计数器的初始计数值，表示有多少个子任务需要完成。

将List<List<student>>循环插入到数据库，对于每个子列表list<student>，向线程池 threadPoolExecutor 提交一个 lambda 表达式作为任务，每个任务将执行数据插入操作。

使用countDownLatch.await() 方法等待所有子任务完成如果当前线程被中断，await 方法将抛出 InterruptedException运用try……catch处理。

在所有任务完成后，清空 successList 和 lists 集合，释放内存资源。

这段代码实现了将从Excel文件中读取的数据分批插入数据库的功能。通过使用批处理和多线程技术，它可以有效地提高数据处理的速度和效率。同时，通过使用 CountDownLatch 控制线程同步，确保所有数据插入操作完成后才继续执行后续操作。

doAfterAllAnalysed方法是ReadListener中的方法，表示在所有数据读取完成后被调用的方法。判断successList是否为空，如果 successList 非空，则说明还有未处理的数据，继续执行saveData方法。

4.2.3 测试

controller

 
  1. @RestController@RequestMapping("/excel")public class ExprotController {
  2. @Autowired private EasyExcelImportService easyExcelImportService;
  3. @RequestMapping("/import") public void importExcel(String name){ easyExcelImportService.importExcel(name); }}
 

 

service

 
  1. @Service
  2. public class EasyExcelImportService {
  3. @Autowired
  4. private com.by.dao.studentDao studentDao;
  5.  
  6. @Autowired
  7. private ReadListener readListener;
  8.  
  9.  
  10. public void importExcel() {
  11. try {
  12.  
  13. String filePath = "C:\\Users\\ljj\\Desktop\\export-20240808194933.xlsx";
  14. FileInputStream inputStream = new FileInputStream(filePath);
  15. long beginTime = System.currentTimeMillis();
  16. //加载文件读取监听器
  17. //easyexcel的read方法进行数据读取和插入
  18. EasyExcel.read(inputStream, student.class, readListener).sheet().doRead();
  19. System.out.println("读取文件耗时：" + (System.currentTimeMillis() - beginTime) / 1000 + "秒");
  20. } catch (Exception e) {
  21. System.out.println("导入异常" + e.getMessage());
  22. }
  23. }
  24.  
  25. }
 

 

通过构造注入自定义的监听器，

  • 使用 EasyExcel.read 方法开始读取操作。

  • 第一个参数是输入流 inputStream，用于读取 Excel 文件。

  • 第二个参数是数据模型的类 student.class，用于表示 Excel 中的数据。

  • 第三个参数是 readListener，用于处理读取的数据。

  • 调用 .sheet().doRead() 方法来指定读取的第一个工作表，并开始读取操作。

  • 记录读取操作开始的时间戳 beginTime。

  • 在读取完成后，计算并输出读取耗时。

  • 捕获可能发生的异常，并输出错误信息。

插入一百万条数据

经过测试插入一百万条数据的6列Excel大概需要31秒左右，可以看到插入几乎是不消耗时间的，消耗时间的地方还是”读“这一步。

这个速度虽然勉强还算能够接受，但是阅读超时了，那就是说还不够好，还有优化的空间。

 

5 再优化（失败了）

运用原生的jdbc进行分批操作，也许就能快点了。

想的很好，但现实给了我一巴掌，优化宣告失败。但是这种方法也是可行，也可以把数据插入到数据库，代码写在下面了。

util工具类

 
  1. package com.by.util;
  2.  
  3. import org.springframework.context.annotation.Configuration;
  4.  
  5. import java.io.IOException;
  6. import java.sql.*;
  7. import java.util.Properties;
  8. @Configuration
  9. public class propertyUtil {
  10. private static String driver;
  11. private static String url;
  12. private static String name;
  13. private static String password;
  14.  
  15.  
  16. static{
  17. Properties properties = new Properties();
  18. try {
  19. properties.load(propertyUtil.class.getClassLoader().getResourceAsStream("application.properties"));
  20. driver = properties.getProperty("spring.datasource.driver-class-name");
  21. url = properties.getProperty("spring.datasource.url");
  22. name = properties.getProperty("spring.datasource.username");
  23. password = properties.getProperty("spring.datasource.password");
  24. Class.forName(driver);
  25. } catch (IOException | ClassNotFoundException e) {
  26. e.printStackTrace();
  27. }
  28. }
  29.  
  30.  
  31.  
  32. /**
  33. * 获取数据库连接对象
  34. * @return
  35. * @throws Exception
  36. */
  37. public static Connection getConnect() throws Exception {
  38. return DriverManager.getConnection(url, name, password);
  39. }
  40.  
  41. /**
  42. * 关闭数据库相关资源
  43. * @param conn
  44. * @param ps
  45. * @param rs
  46. */
  47. public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
  48. try {
  49. if (conn != null) conn.close();
  50. if (ps != null) ps.close();
  51. if (rs != null) rs.close();
  52. } catch (SQLException e) {
  53. throw new RuntimeException(e);
  54. }
  55. }
  56. public static void close(Connection conn, PreparedStatement ps) {
  57. close(conn, ps, null);
  58. }
  59. public static void close(Connection conn, ResultSet rs) {
  60. close(conn, null, rs);
  61. }
  62. }
  63.  
 

 

然后用下列代码代替工具类中的saveData方法就可以了。

 
  1. public void import4Jdbc(){
  2.  
  3. //分批读取+JDBC分批插入+手动事务控制
  4. Connection conn = null;
  5. //JDBC存储过程
  6. PreparedStatement ps = null;
  7. try {
  8. //建立jdbc数据库连接
  9. conn = propertyUtil.getConnect();
  10. //关闭事务默认提交
  11. conn.setAutoCommit(false);
  12. String sql = "insert into student (name,age,sex,address,email,phone) values";
  13. sql += "(?,?,?,?,?,?)";
  14. ps = conn.prepareStatement(sql);
  15. for (int i = 0; i < successList.size(); i++) {
  16. student user = new student();
  17. ps.setString(1,successList.get(i).getName());
  18. ps.setInt(2,successList.get(i).getAge());
  19. ps.setString(3,successList.get(i).getSex());
  20. ps.setString(4,successList.get(i).getAddress());
  21. ps.setString(5,successList.get(i).getEmail());
  22. ps.setString(6,successList.get(i).getPhone());
  23. //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
  24. ps.addBatch();
  25. }
  26. //执行批处理
  27. ps.executeBatch();
  28. //手动提交事务
  29. conn.commit();
  30. } catch (Exception e) {
  31. e.printStackTrace();
  32. } finally {
  33. //记得关闭连接
  34. com.by.util.propertyUtil.close(conn,ps);
  35. }
  36. }
 

 

如图所见，用了两分半还多一点。

在 MyBatis-Plus 中，实现批量操作（如批量插入、批量更新）是非常常见的需求。MyBatis-Plus 提供了对批量操作的良好支持，可以通过多种方式实现高效的批量处理。下面详细介绍批量操作的实现方式以及最佳实践。

 

1. 批量插入

批量插入是指一次性插入多条记录，而不是逐条插入。MyBatis-Plus 提供了多种方式来实现批量插入。

 

1.1 使用 insertBatchSomeColumn 方法

MyBatis-Plus 提供了 insertBatchSomeColumn 方法，可以直接插入一个集合的所有元素。这个方法通常用于插入时可以选择性地忽略一些不需要的字段（如主键自增的场景）。

示例：

 
  1. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
  2. import org.springframework.stereotype.Service;
  3. ​
  4. @Service
  5. public class UserService extends ServiceImpl<UserMapper, User> {
  6. ​
  7.   public boolean batchInsert(List<User> userList) {
  8.       return this.saveBatch(userList);
  9.   }
  10. }
 

调用示例：

 
  1. List<User> userList = new ArrayList<>();
  2. userList.add(new User("Alice", 25));
  3. userList.add(new User("Bob", 30));
  4. ​
  5. userService.batchInsert(userList);
 

  • saveBatch：MyBatis-Plus 提供的 saveBatch 方法可以一次性插入多个记录。该方法默认使用了批量插入的 SQL 优化，可以在一定程度上减少数据库的连接开销。

 

1.2 使用 Mapper 接口的批量插入

你也可以通过在 Mapper 接口中自定义批量插入的 SQL 语句来实现批量插入操作。

自定义批量插入 SQL：

 
  1. import org.apache.ibatis.annotations.Insert;
  2. import org.apache.ibatis.annotations.Param;
  3. import java.util.List;
  4. ​
  5. public interface UserMapper extends BaseMapper<User> {
  6. ​
  7.   @Insert("<script>" +
  8.           "INSERT INTO user (name, age) VALUES " +
  9.           "<foreach collection='list' item='user' separator=','>" +
  10.           "(#{user.name}, #{user.age})" +
  11.           "</foreach>" +
  12.           "</script>")
  13.   int batchInsert(@Param("list") List<User> userList);
  14. }
 

调用示例：

 
  1. List<User> userList = new ArrayList<>();
  2. userList.add(new User("Alice", 25));
  3. userList.add(new User("Bob", 30));
  4. ​
  5. userMapper.batchInsert(userList);
 

  • <foreach> 标签：在 MyBatis 的 XML 中使用 <foreach> 标签遍历集合，并生成批量插入的 SQL 语句。

 

2. 批量更新

批量更新指的是一次性更新多条记录。与批量插入类似，MyBatis-Plus 也提供了多种方式实现批量更新。

 

2.1 使用 updateBatchById 方法

MyBatis-Plus 提供了 updateBatchById 方法，支持根据 ID 批量更新多个实体。

示例：

 
  1. @Service
  2. public class UserService extends ServiceImpl<UserMapper, User> {
  3. ​
  4.   public boolean batchUpdate(List<User> userList) {
  5.       return this.updateBatchById(userList);
  6.   }
  7. }
 

调用示例：

 
  1. List<User> userList = new ArrayList<>();
  2. userList.add(new User(1L, "Alice", 26)); // 更新ID为1的用户
  3. userList.add(new User(2L, "Bob", 31)); // 更新ID为2的用户
  4. ​
  5. userService.batchUpdate(userList);
 

  • updateBatchById：该方法会根据传入的实体集合中的 ID，依次更新对应的记录。每个实体只更新有变动的字段。

 

2.2 自定义批量更新 SQL

你也可以通过在 Mapper 接口中自定义批量更新的 SQL 语句来实现批量更新操作。

自定义批量更新 SQL：

 
  1. import org.apache.ibatis.annotations.Update;
  2. import org.apache.ibatis.annotations.Param;
  3. import java.util.List;
  4. ​
  5. public interface UserMapper extends BaseMapper<User> {
  6. ​
  7.   @Update("<script>" +
  8.           "<foreach collection='list' item='user' separator=';'>" +
  9.           "UPDATE user " +
  10.           "SET name = #{user.name}, age = #{user.age} " +
  11.           "WHERE id = #{user.id}" +
  12.           "</foreach>" +
  13.           "</script>")
  14.   int batchUpdate(@Param("list") List<User> userList);
  15. }
 

调用示例：

 
  1. List<User> userList = new ArrayList<>();
  2. userList.add(new User(1L, "Alice", 26));
  3. userList.add(new User(2L, "Bob", 31));
  4. ​
  5. userMapper.batchUpdate(userList);
 

  • <foreach> 标签：与批量插入类似，使用 <foreach> 标签遍历集合并生成批量更新的 SQL 语句。

3. 批量操作的最佳实践

  1. 使用批量操作方法：MyBatis-Plus 提供的 saveBatch 和 updateBatchById 方法已经优化了 SQL 执行的效率，建议优先使用这些方法。

  2. 控制批量操作的大小：在批量插入或更新时，最好控制单次操作的批量大小（例如每次操作 1000 条记录），以避免 SQL 语句过长或数据库连接超时问题。

  3. 考虑使用数据库事务：批量操作通常需要涉及多条 SQL 语句的执行，为了保证操作的原子性，可以考虑在批量操作时使用数据库事务。

  4. 使用乐观锁：如果批量更新操作涉及并发写入，建议使用乐观锁来避免数据冲突，MyBatis-Plus 支持通过 @Version 注解实现乐观锁机制。

  5. 合理配置 MyBatis-Plus 插件：在高并发场景下，合理配置 MyBatis-Plus 的分页、乐观锁、SQL 性能分析等插件，可以提高应用的性能和稳定性。

 

总结

  • 批量插入：可以通过 MyBatis-Plus 的 saveBatch 方法实现，或者通过自定义 Mapper 接口的批量插入 SQL 语句实现。

  • 批量更新：可以通过 MyBatis-Plus 的 updateBatchById 方法实现，或者通过自定义 Mapper 接口的批量更新 SQL 语句实现。

  • 最佳实践：在批量操作中，合理控制批量大小、使用事务、应用乐观锁，以及配置好插件，可以确保批量操作的高效和稳定。

MyBatis-Plus 为批量操作提供了简便的接口和优化手段，使得开发者可以更加高效地处理大批量数据的插入和更新操作。

posted @ 2025-01-02 14:38  CharyGao  阅读(156)  评论(0)    收藏  举报