✅实现百万级数据从Excel导入到数据库的方式

高手答复

场景分析

这个案例现实上涉及到多个方面，必要我们体系地分析。让我们首先看看，从Excel中读取百万级数据并将其插入数据库时可能碰到的问题：

• 内存溢出风险
  

加载如此庞大的Excel数据可能导致内存溢出，必要注意内存管理。

• 性能瓶颈
  

处置惩罚百万级数据的读取和插入操作可能很耗时，性能优化至关重要。

• 异常处置惩罚策略
  

读取和导入过程中会有各种潜在问题，我们需妥善处置惩罚各类异常环境。
内存溢出问题

处置惩罚百万级数据，直接加载到内存中显然不现实。解决之道在于采用流式读取，分批处置惩罚数据。
在技术选型上，选择EasyExcel是明智之举。它专为处置惩罚大数据量和复杂Excel文件举行了优化。EasyExcel在解析Excel时，不会将整个文件一次性加载到内存中，而是按行从磁盘逐个读取数据并解析。
性能问题

针对百万级数据的处置惩罚，单线程显然效率低下。提拔性能的关键在于多线程处置惩罚。
多线程应用涉及两个场景：一是多线程读取文件，另一个是多线程实现数据插入。这涉及到生产者-消费者模式，多线程读取并多线程插入，以最大程度提拔团体性能。
在数据插入方面，除了利用多线程，还应当联合数据库的批量插入功能以进一步提拔速度。
错误处置惩罚

在文件读取和数据库写入过程中，可能碰到诸多问题，如数据格式错误、不同等性和重复数据等。
因此，应分两步处置惩罚。首先举行数据检查，在插入操作前检查数据格式等问题，然后在插入过程中处置惩罚异常环境。
处置惩罚方式多种多样，可通过事务回滚或记录日志。一般不推荐直接回滚操作，而是主动重试，若实验多次仍无效，则记录日志，随后重新插入数据。
别的，在这一过程中，需考虑数据重复问题，可在Excel中设定若干字段为数据库唯一束缚。碰到数据辩论时，可覆盖、跳过或报错处置惩罚。根据现实业务环境选择符合的处置惩罚方式，一般环境下，跳过并记录日志是相对合理的选择。
解决思绪

所以，总体方案如下：
利用EasyExcel举行Excel数据读取，因其逐行读取数据而非一次性加载整个文件至内存。为提高并发效率，将百万级数据分布在不同的工作表中，利用线程池和多线程同时读取各个工作表。在读取过程中，借助EasyExcel的ReadListener举行数据处置惩罚。
在处置惩罚过程中，并非每条数据都直接操作数据库，以免对数据库造成过大压力。设定一个批次巨细，比方每1000条数据，将从Excel中读取的数据临时存储在内存中（可使用List实现）。每读取1000条数据后，执行数据的批量插入操作，可简单地借助mybatis实现批量插入。
别的，在处置惩罚过程中，必要考虑并发问题，因此我们将使用线程安全的队列来存储内存中的临时数据，如ConcurrentLinkedQueue。
履历证，通过上述方案，读取并插入100万条数据的Excel所需时间约为100秒，不超过2分钟。
详细实现

为了提拔并发处置惩罚能力，我们将百万级数据存储在同一个Excel文件的不同工作表中，然后通过EasyExcel并发地读取这些工作表数据。
EasyExcel提供了ReadListener接口，答应在每批数据读取后举行自定义处置惩罚。我们可以基于这一功能实现文件的分批读取。
pom依赖

首先，必要添加以下依赖：

1. <dependencies>
2.     <!-- EasyExcel -->
3.     <dependency>
4.         <groupId>com.alibaba</groupId>
5.         <artifactId>easyexcel</artifactId>
6.         <version>latest_version</version>
7.     </dependency>
9.     <!-- 数据库连接和线程池 -->
10.     <dependency>
11.         <groupId>org.springframework.boot</groupId>
12.         <artifactId>spring-boot-starter-data-jpa</artifactId>
13.     </dependency>
14. </dependencies>

复制代码

并发读取多个sheet

然后实现并发读取多个sheet的代码：

1. @Service
2. public class ExcelImporterService {
4.     @Autowired
5.     private MyDataService myDataService;
6.    
7.     public void doImport() {
8.         // Excel文件的路径
9.         String filePath = "users/paidaxing/workspace/excel/test.xlsx";
11.         // 需要读取的sheet数量
12.         int numberOfSheets = 20;
14.         // 创建一个固定大小的线程池，大小与sheet数量相同
15.         ExecutorService executor = Executors.newFixedThreadPool(numberOfSheets);
17.         // 遍历所有sheets
18.         for (int sheetNo = 0; sheetNo < numberOfSheets; sheetNo++) {
19.             // 在Java lambda表达式中使用的变量需要是final
20.             int finalSheetNo = sheetNo;
22.             // 向线程池提交一个任务
23.             executor.submit(() -> {
24.                 // 使用EasyExcel读取指定的sheet
25.                 EasyExcel.read(filePath, MyDataModel.class, new MyDataModelListener(myDataService))
26.                          .sheet(finalSheetNo) // 指定sheet号
27.                          .doRead(); // 开始读取操作
28.             });
29.         }
31.         // 启动线程池的关闭序列
32.                 executor.shutdown();
34.         // 等待所有任务完成，或者在等待超时前被中断
35.         try {
36.             executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
37.         } catch (InterruptedException e) {
38.             // 如果等待过程中线程被中断，打印异常信息
39.             e.printStackTrace();
40.         }
41.     }
42. }

复制代码

这段代码通过创建一个固定巨细的线程池来并发读取一个包罗多个sheets的Excel文件。每个sheet的读取作为一个单独的任务提交给线程池。
我们在代码中用了一个MyDataModelListener，这个类是ReadListener的一个实现类。当EasyExcel读取每一行数据时，它会主动调用我们传入的这个ReadListener实例的invoke方法。在这个方法中，我们就可以定义如何处置惩罚这些数据。
MyDataModelListener还包罗doAfterAllAnalysed方法，这个方法在全部数据都读取完毕后被调用。这里可以执行一些整理工作，或处置惩罚剩余的数据。
ReadListener

接下来，我们来实现这个我们的ReadListener：

1. import com.alibaba.excel.context.AnalysisContext;
2. import com.alibaba.excel.read.listener.ReadListener;
3. import org.springframework.transaction.annotation.Transactional;
4. import java.util.ArrayList;
5. import java.util.List;
7. // 自定义的ReadListener，用于处理从Excel读取的数据
8. public class MyDataModelListener implements ReadListener<MyDataModel> {
9.     // 设置批量处理的数据大小
10.     private static final int BATCH_SIZE = 1000;
11.     // 用于暂存读取的数据，直到达到批量大小
12.     private List<MyDataModel> batch = new ArrayList<>();
14.    
15.     private MyDataService myDataService;
17.     // 构造函数，注入MyBatis的Mapper
18.     public MyDataModelListener(MyDataService myDataService) {
19.         this.myDataService = myDataService;
20.     }
22.     // 每读取一行数据都会调用此方法
23.     @Override
24.     public void invoke(MyDataModel data, AnalysisContext context) {
25.         //检查数据的合法性及有效性
26.         if (validateData(data)) {
27.             //有效数据添加到list中
28.             batch.add(data);
29.         } else {
30.             // 处理无效数据，例如记录日志或跳过
31.         }
32.         
33.         // 当达到批量大小时，处理这批数据
34.         if (batch.size() >= BATCH_SIZE) {
35.             processBatch();
36.         }
37.     }
39.    
40.     private boolean validateData(MyDataModel data) {
41.         // 调用mapper方法来检查数据库中是否已存在该数据
42.         int count = myDataService.countByColumn1(data.getColumn1());
43.         // 如果count为0，表示数据不存在，返回true；否则返回false
44.         if(count == 0){
45.                 return true;
46.         }
47.         
48.         // 在这里实现数据验证逻辑
49.         return false;
50.     }
53.     // 所有数据读取完成后调用此方法
54.     @Override
55.     public void doAfterAllAnalysed(AnalysisContext context) {
56.         // 如果还有未处理的数据，进行处理
57.         if (!batch.isEmpty()) {
58.             processBatch();
59.         }
60.     }
62.     // 处理一批数据的方法
63.     private void processBatch() {
64.         int retryCount = 0;
65.         // 重试逻辑
66.         while (retryCount < 3) {
67.             try {
68.                 // 尝试批量插入
69.                 myDataService.batchInsert(batch);
70.                 // 清空批量数据，以便下一次批量处理
71.                 batch.clear();
72.                 break;
73.             } catch (Exception e) {
74.                 // 重试计数增加
75.                 retryCount++;
76.                 // 如果重试3次都失败，记录错误日志
77.                 if (retryCount >= 3) {
78.                     logError(e, batch);
79.                 }
80. }

复制代码

通过自定义MyDataModelListener，在读取Excel文件过程中可实现数据处置惩罚。每读取一条数据后，将其参加列表，在列表累积到达1000条时，执行一次数据库批量插入操作。若插入失败，则举行重试；若多次实验仍失败，则记录错误日志。
批量插入

这里批量插入，用到了MyBatis的批量插入，代码实现如下：

1. import org.apache.ibatis.annotations.Mapper;
2. import java.util.List;
4. @Mapper
5. public interface MyDataMapper {
6.     void batchInsert(List<MyDataModel> dataList);
8.     int countByColumn1(String column1);
9. }

复制代码

mapper.xml文件：

1. <insert id="batchInsert" parameterType="list">
2.     INSERT INTO paidaxing_test_table_name (column1, column2, ...)
3.     VALUES
4.     <foreach collection="list" item="item" index="index" separator=",">
5.         (#{item.column1}, #{item.column2}, ...)
6.     </foreach>
7. </insert>
9. <select id="countByColumn1" resultType="int">
10.     SELECT COUNT(*) FROM your_table WHERE column1 = #{column1}
11. </select>

复制代码

  如有问题，欢迎加微信交换：w714771310，备注- 技术交换  。或微信搜刮【码上遇见你】。
  免费的Chat GPT可微信搜刮【AI贝塔】举行体验，无限使用。
好了，本章节到此告一段落。渴望对你有所帮助，祝学习顺利。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。