百万级数据导入实战：用EasyExcel和JDBC批量插入征服Excel海量数据
用EasyExcel和JDBC批量插入征服Excel海量数据
1. Excel操作工具对比
最近在工作中遇到了数百万条信息的大数据量导入导出。如果用传统的逐条插入方式，不仅效率低下，还可能导致内存溢出。那么，今天我们就一起来分析一下如何解决这个问题。
1.1 Apache POI系列
我在项目中比较常用的 Excel 操作工具就是 POI，作为一个传统的技术工具，我们先来看看 POI 的不同版本及其优缺点。
-
HSSFWorkbook：
这个实现类是早期使用最多的对象，可以操作 Excel 2003 及以前的版本，文件扩展名为.xls。-
缺点：最多只能导出 65535 行数据，超出此限制会报错。
-
优点：不会导致内存溢出。（数据量少于 7w 行时，内存一般足够，POI 先将数据加载到内存，再进行操作。）
-
-
XSSFWorkbook：
这个实现类在很多项目中仍在使用，适用于 Excel 2002 到 Excel 2007 版本，文件扩展名为.xlsx。-
优点：可以导出最多 1048576 行，16384 列，最多可导出 104 万条数据。
-
缺点：随着数据行数增加，内存使用也大幅上升，容易导致内存溢出。因为
book、sheet、row、cell等对象在写入 Excel 前，都是存放在内存中的。（不包括 Excel 样式等其他元素。）
-
-
SXSSFWorkbook：
这是 POI 3.8 之后的版本，支持操作 Excel 2007 及以后版本，文件扩展名为.xlsx。-
优点：一般不会出现内存溢出（通过将数据持久化到硬盘来节省内存，只有最新数据存放在内存中，适合存储百万条数据）。
-
缺点：
-
部分数据已持久化到硬盘，无法访问，因此只能访问内存中存储的部分数据。
-
sheet.clone()方法不再支持（由于持久化的原因）。 -
不再支持公式求值，因为硬盘中的数据无法被加载到内存中进行计算。
-
使用模板下载数据时，表头不能修改，因为数据已经写入硬盘。
-
-
1.2 EasyExcel（基于 POI 优化）
EasyExcel 采用了更加高效的流式读写技术，尤其适合处理大数据量的 Excel 导入导出。
-
流式读写技术：
EasyExcel 采用 SAX 模式，逐行读写 Excel 数据，而不是一次性将整个文件加载到内存中。这种设计优化了内存占用，避免了传统方式中内存线性增长的问题，即使是百万级数据也能保持较低的内存消耗。例如，在读取时，只解析当前行的数据并立即释放内存，避免内存堆积。 -
事件驱动模型：
基于观察者模式（Observer Pattern），EasyExcel 在解析过程中逐行触发事件（如AnalysisEventListener）。开发者可以通过监听器实时处理每行数据并释放资源，这种机制显著降低了内存压力。 -
Java NIO 技术：
EasyExcel 底层结合了 Java 非阻塞 I/O（NIO）技术，优化了文件的读写效率。NIO 的高效通道（Channel）和缓冲区（Buffer）机制减少了磁盘 I/O 操作的耗时，提升了处理速度。 -
内存管理优化：
-
逐行写入机制：写入时按行生成数据流，避免一次性生成全量数据的内存占用。
-
缓存策略：通过智能缓存管理，减少重复对象的创建和垃圾回收频率，例如复用单元格样式对象。
-
异步处理：支持多线程分片处理数据，结合及时资源释放。
-
2.遇到的核心问题是什么？
-
问题 1：数据量极大，如果用传统 POI 方式内存溢出，且效率低。
解决思路：只要使用对上面介绍的POI方式即可，主要问题就是原生的POI解决起来相当麻烦。EasyExcel可以很好的解决内存溢出的问题， -
问题 2：不能够循环一条条的进行数据插入；
解决思路：将 Excel 中读取的数据存储到集合中，到达一定数量后直接批量插入数据库。 -
问题 3：避免用 MyBatis 的批量插入，因为 MyBatis 的批量插入就是 SQL 循环；对于大数据量仍然非常慢。
解决思路：使用 JDBC 批量插入，配合事务管理来完成批量数据插入。即使用 Excel 分批读取 + JDBC 分批插入 + 事务。 -
问题 4：减少数据库交互次数。
解决思路：例如，在插入仓库数据时，需要根据编码字段检查数据是否已存在。如果逐条查询数据库，会导致频繁交互，降低性能。可以将Excel中一批数据的编码字段预先收集，通过一次数据库查询批量检查是否存在
技术选型对比
| 方案 | 内存占用 | 耗时（100万条） | 可靠性 | 复杂度 |
|---|---|---|---|---|
| POI DOM 解析 | 极高 | 30 分钟以上 | 低 | 高 |
| MyBatis 批量插入 | 中等 | 15 分钟 | 中 | 中 |
| EasyExcel + JDBC | 极低 | 3 分钟 | 高 | 低 |
3. 手把手实现百万数据导入
百万数据的导入解决思路
-
分批读取 Excel 中的数据：
EasyExcel 提供了很好的分批处理能力。通过调整批次参数（例如 3000 行），我选择了 5w 的批次。 -
批量插入数据库：
将读取到的 20w 条数据进行批量插入，避免逐条循环插入。MyBatis 的批量插入效率低，建议使用 JDBC 批量插入。可以参考 MyBatis 批量插入和 JDBC 批量插入的性能对比。 -
使用 JDBC + 事务进行批量插入：
最终通过分批读取 + JDBC 分批插入 + 手动事务控制的方式，将数据导入数据库。
3.1 环境准备
pom.xml关键依赖：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>Druid连接池配置：
# druid.properties
druid.url=jdbc:mysql://localhost:3306/warehouse?rewriteBatchedStatements=true
druid.username=root
druid.password=123456
druid.initialSize=5
druid.maxActive=20
druid.maxWait=60000
druid.validationQuery=SELECT 13.2 核心代码实现
1. Excel 数据映射对象
@Data
public class CangKuImportDTO {
@ExcelProperty(index = 0)
private String code;
@ExcelProperty(index = 1)
private String name;
@ExcelProperty(index = 2)
private Double areaZD;
@ExcelProperty(index = 3)
private Double areaJZ;
@DateTimeFormat("yyyy-MM-dd HH:mm")
@ExcelProperty(index = 4)
private Date cangKuDate;
// 其他字段...
}2. 数据监听器
/**
* 增强型仓库数据导入监听器（用于EasyExcel处理百万级数据导入）
* 功能：逐行读取Excel数据 → 校验 → 批量入库 → 错误处理 → 生成错误报告
*/
public class EnhancedCangKuListener extends AnalysisEventListener<CangKuImportDTO> {
private static final Logger logger = LoggerFactory.getLogger(EnhancedCangKuListener.class);
// 动态批次大小（默认初始值50000条，根据处理情况自动调整）
private int batchSize = 50000;
// 数据缓存列表（存储当前批次待处理的合法数据）
private List<CangKuImportDTO> cachedList = new ArrayList<>(batchSize);
// 仓库服务接口（用于调用数据库批量插入操作）
private final CangKuService cangKuService;
// 原子计数器（保证多线程安全）：
private final AtomicLong totalCounter = new AtomicLong(0); // 总处理数据量统计
private final AtomicLong successCounter = new AtomicLong(0); // 成功入库数据量统计
// 错误记录列表（线程安全的同步列表，存储所有校验失败或插入失败的数据）
private final List<CangKuImportDTO> errorRecords = Collections.synchronizedList(new ArrayList<>());
public EnhancedCangKuListener(CangKuService cangKuService) {
this.cangKuService = cangKuService;
}
/**
* 核心处理方法：每读取一行Excel数据时触发
* @param data 当前行的数据对象
* @param context EasyExcel解析上下文（包含工作表、行号等信息）
*/
@Override
public void invoke(CangKuImportDTO data, AnalysisContext context) {
if(validateData(data)) {
cachedList.add(data);
//当缓存数据量达到批次大小时触发批量插入
if (cachedList.size() >= batchSize) {
saveBatch();
adjustBatchSize(); // 动态调整批次大小
}
} else {
errorRecords.add(data);
}
//定期打印处理进度（每处理1万条打印一次）
printProgress();
}
private boolean validateData(CangKuImportDTO data) {
// 示例校验逻辑
return StringUtils.isNotBlank(data.getCode())
&& data.getAreaZD() != null
&& data.getCangKuDate() != null;
}
/**
* 批量保存数据到数据库（核心插入逻辑）
*/
private void saveBatch() {
if (!cachedList.isEmpty()) {
try {
cangKuService.batchInsert(cachedList);
// 成功计数累加
successCounter.addAndGet(cachedList.size());
} catch (DataAccessException e) {
logger.error("批次插入失败，失败数量：{}", cachedList.size(), e);
errorRecords.addAll(cachedList);
}
//// 清空缓存列表（无论成功与否）
cachedList.clear();
}
}
/**
* 动态调整批次大小（示例算法：根据处理情况自动优化）
* 说明：如果批次过小会增加数据库交互次数，过大会导致内存压力
*/
private void adjustBatchSize() {
// 动态调整逻辑（示例）
if (batchSize < 50000) {
batchSize = Math.min(batchSize * 2, 50000);
}
}
private void printProgress() {
long total = totalCounter.incrementAndGet();
if (total % 10000 == 0) {
logger.info("已处理：{} 条，成功：{} 条，失败：{} 条",
total, successCounter.get(), errorRecords.size());
}
}
/**
* 所有数据解析完成后触发的收尾操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveBatch(); // 处理最后一批数据
generateErrorReport(); // 生成错误报告
logger.info("导入完成！总计：{} 条，成功：{} 条，失败：{} 条",
totalCounter.get(), successCounter.get(), errorRecords.size());
}
private void generateErrorReport() {
if (!errorRecords.isEmpty()) {
String errorFilePath = "/tmp/import_errors_" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(errorFilePath, CangKuImportDTO.class)
.sheet("错误数据")
.doWrite(errorRecords);
logger.warn("错误数据已导出至：{}", errorFilePath);
}
}
}3. Service 层实现
@Service
@RequiredArgsConstructor
public class CangKuService {
private final JdbcTemplate jdbcTemplate;
@Value("${spring.datasource.druid.batch-size:50000}")
private int batchSize;
private static final String INSERT_SQL = """
INSERT INTO CangKu (
Code, Name, AreaZD, AreaJZ, CangKu_Date,
CreateDate, Province, ProvinceId,
City, CityId, County, CountyId
) VALUES (
?, ?, ?, ?, ?,
?, ?, ?,
?, ?, ?, ?
)
""";
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void batchInsert(List<CangKuImportDTO> list) {
jdbcTemplate.batchUpdate(INSERT_SQL, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
CangKuImportDTO item = list.get(i);
ps.setString(1, item.getCode());
ps.setString(2, item.getName());
ps.setDouble(3, item.getAreaZD());
ps.setDouble(4, item.getAreaJZ());
ps.setTimestamp(5, new Timestamp(item.getCangKuDate().getTime()));
ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
ps.setString(7, item.getProvince());
ps.setInt(8, item.getProvinceId());
ps.setString(9, item.getCity());
ps.setInt(10, item.getCityId());
ps.setString(11, item.getCounty());
ps.setInt(12, item.getCountyId());
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
}4. 启动类配置
@RestController
@SpringBootApplication
public class DataImportApplication {
public static void main(String[] args) {
SpringApplication.run(DataImportApplication.class, args);
}
@Autowired
private CangKuService cangKuService;
@PostMapping("/import")
public ResponseEntity<?> importData(@RequestParam("file") MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(), CangKuImportDTO.class,
new EnhancedCangKuListener(cangKuService))
.sheet()
.doRead();
return ResponseEntity.ok("导入请求已接受");
} catch (IOException e) {
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
.body("文件读取失败：" + e.getMessage());
}
}
}5. 增强版连接池配置（application.yml）
spring:
datasource:
druid:
url: jdbc:oracle:thin:@localhost:1521:ORCL
username: mrkay
password: ******
initial-size: 5
min-idle: 5
max-active: 50
max-wait: 60000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
filters: stat,wall
connect-properties:
oracle.jdbc.J2EE13Compliant: true
# 批量插入优化参数
connection-properties:
defaultExecuteBatch: 10000
rewriteBatchedStatements: true
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
8
0- 0
已为社区贡献1条内容
所有评论(0)