Spring Boot大Excel转JSON导入MySQL遇连接超时求高效方案

一、优化Excel读取逻辑 #

使用SXSSFWorkbook（POI流式处理）替代XSSFWorkbook，避免全量加载Excel到内存导致的内存溢出和长时间占用数据库连接。SXSSF会将超出内存阈值的行写入临时文件，仅保留指定行数在内存中，适合处理十万级以上数据量。

示例代码：

try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) { // 仅保留100行在内存，其余写入临时文件
    FileInputStream fis = new FileInputStream(new File("large_excel.xlsx"));
    workbook.setCompressTempFiles(true); // 压缩临时文件，减少磁盘占用

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        SXSSFSheet sheet = workbook.getSheetAt(i);
        Iterator<Row> rowIterator = sheet.iterator();
        List<Map<String, Object>> sheetData = new ArrayList<>();
        
        // 读取表头
        Row headerRow = rowIterator.next();
        List<String> headers = new ArrayList<>();
        for (Cell cell : headerRow) {
            headers.add(cell.getStringCellValue());
        }

        // 逐行读取数据
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Map<String, Object> rowData = new HashMap<>();
            for (int j = 0; j < headers.size(); j++) {
                Cell cell = row.getCell(j);
                rowData.put(headers.get(j), getCellValue(cell)); // 自定义方法处理不同类型单元格
            }
            sheetData.add(rowData);

            // 每5000行批量写入一次，避免内存堆积
            if (sheetData.size() % 5000 == 0) {
                appendSheetDataToDB(sheet.getSheetName(), sheetData);
                sheetData.clear();
            }
        }
        // 处理剩余未批量提交的数据
        if (!sheetData.isEmpty()) {
            appendSheetDataToDB(sheet.getSheetName(), sheetData);
        }
        sheet.dispose(); // 清理当前工作表的临时数据，释放内存
    }
} catch (IOException e) {
    e.printStackTrace();
}

二、数据库连接与写入优化 #

1. 调整连接池参数 #

在application.yml中配置Hikari连接池，延长连接超时时间，避免连接被提前回收：

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      connection-timeout: 300000 # 5分钟，根据实际处理时长调整
      idle-timeout: 600000
      max-lifetime: 1800000
      validation-timeout: 3000
      leak-detection-threshold: 60000 # 检测连接泄漏

2. 优化JSON写入逻辑 #

由于每个工作表对应数据库一行，直接构建大JSON数组会占用大量内存，建议使用Jackson流式API生成JSON，减少内存占用：

@Autowired
private JdbcTemplate jdbcTemplate;

private void appendSheetDataToDB(String sheetName, List<Map<String, Object>> dataBatch) {
    try {
        // 检查是否是当前工作表的第一批次数据，决定是插入还是更新JSON字段
        String checkSql = "SELECT COUNT(*) FROM Import_Table WHERE Sheet_Name = ?";
        int count = jdbcTemplate.queryForObject(checkSql, Integer.class, sheetName);
        
        ObjectMapper mapper = new ObjectMapper();
        String batchJson = mapper.writeValueAsString(dataBatch);
        String sql;
        if (count == 0) {
            sql = "INSERT INTO Import_Table (Sheet_Name, Request_JSON) VALUES (?, ?)";
            jdbcTemplate.update(sql, sheetName, batchJson);
        } else {
            sql = "UPDATE Import_Table SET Request_JSON = CONCAT(Request_JSON, SUBSTRING(?, 2)) WHERE Sheet_Name = ?";
            // 拼接JSON数组，去掉第二个数组的开头'['
            jdbcTemplate.update(sql, batchJson, sheetName);
        }
    } catch (JsonProcessingException e) {
        e.printStackTrace();
    }
}

3. 数据库端优化 #

• 数据库URL添加rewriteBatchedStatements=true，开启MySQL批量语句重写，提升写入性能：

  jdbc:mysql://localhost:3306/your_db?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8
  

• 将Request_JSON字段类型设为LONGTEXT，避免大JSON长度超出限制；导入期间可临时关闭Sheet_Name字段的索引，导入完成后再重建，提升写入速度。

三、异步拆分处理 #

将每个工作表的处理拆分为独立异步任务，避免单线程长时间占用连接，提升并行处理效率：

1. 配置异步线程池：

@Configuration
@EnableAsync
public class ExcelAsyncConfig {
    @Bean(name = "excelExecutor")
    public Executor excelProcessingExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(5);
        executor.setMaxPoolSize(10);
        executor.setQueueCapacity(20);
        executor.setThreadNamePrefix("Excel-Handler-");
        executor.initialize();
        return executor;
    }
}

2. 提交异步任务处理工作表：

@Autowired
@Qualifier("excelExecutor")
private Executor excelExecutor;

// 在Excel读取循环中提交任务
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
    SXSSFSheet sheet = workbook.getSheetAt(i);
    excelExecutor.execute(() -> processSingleSheet(sheet));
}

四、其他细节优化 #

• 关闭Excel公式自动计算：设置workbook.setForceFormulaRecalculation(false)，避免POI耗时计算单元格公式。
• 增加日志监控：记录每个工作表的处理开始/结束时间、当前处理行数，快速定位超时节点。

内容的提问来源于stack exchange，提问作者Mohammed Tauseef