Java 批量导入 Excel 到 MySQL 实战：EasyExcel 与 JDBC 优化

简介这份资源面向具备一定Java基础、需要处理数据批量迁移的开发者聚焦Excel与MySQL之间的双向数据流转问题。项目基于Apache POI解析xls与xlsx文件通过JDBC建立数据库连接实现Excel数据导入MySQL并在检测到重复记录时执行更新同时支持将库中数据反向导出为Excel表格覆盖文件操作、单元格类型解析、SQL条件判断与批量事务等核心环节。压缩包共20个文件约1.31MB包含6个java源码与6个class编译文件、2个jar依赖库、1个sql建表脚本及Eclipse工程配置源码与依赖齐全可直接导入IDE运行调试。目前已有1737人学习下载适合作为JDBC与POI综合练习的参考案例帮助读者理解导入导出策略、重复数据更新逻辑与项目目录组织方式。1. Java 把 Excel 灌进 MySQL一条被低估的脏活链路电商后台导商品、教务系统导成绩、财务导流水只要业务方手里还攥着 ExcelJava 后端就绕不开「把 Excel 数据导入 MySQL」这件事。很多人第一反应是写个for循环一行行insert本地跑 200 行没问题上线遇到 5 万行直接超时事务一挂全表锁死。这个标题真正要解决的不是「怎么读 Excel」而是「怎么把一份格式不可控、量级不确定、字段还可能对不上的表格稳定、可回滚、可观测地落进 MySQL」。适合谁看写过 JDBC 但没处理过批量导入的 Java 后端、被业务方 Excel 折磨过的数据开发、以及正在准备 Java 面试题里「大数据量插入怎么优化」这类八股的人。下面按「选型 → 读表 → 写库 → 避坑 → 进阶」把这条链路拆开每一步都给能直接抄的代码和参数。2. 选型与建表POI、EasyExcel 还是 CSV 中转2.1 三种读表方案的边界在哪Java 读 Excel 主流就三条路Apache POI、阿里 EasyExcel、以及先转 CSV 再读。选错方案后面全是坑。POI 是最底层的XSSFWorkbook处理.xlsxHSSFWorkbook处理.xls。它的模型是把整个工作簿加载进内存一个 10 万行、20 列的 xlsx 轻松吃掉 1G 以上堆内存线上直接 OOM。优点是 API 全单元格样式、公式、合并单元格都能拿到。EasyExcel 基于 POI 的 SAX 解析重写核心是逐行回调内存占用和行数基本无关常驻几十 MB。代价是它对复杂样式、公式结果的支持弱一些读的是「值」不是「格式」。CSV 中转适合超大批量或者异构系统对接先用工具把 Excel 另存为 CSVJava 侧用BufferedReader按行读性能最高但会丢格式、丢多 sheet、编码还容易翻车。我的判断标准很简单行数 1 万以内、要读样式或公式用 POI行数上万、只关心数据本身用 EasyExcel行数十万以上或要跨系统走 CSV。2.2 依赖怎么引版本别乱跳Maven 里引 EasyExcel 和 POI 的坐标如下。注意 EasyExcel 内部依赖了 POI不要再手动引一个版本冲突的 POI否则运行时报NoSuchMethodError是家常便饭。dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency参数说明EasyExcel 3.x 要求 JDK 8 以上MySQL 驱动 8.x 对应com.mysql.cj.jdbc.Driver连接串要带时区和useSSL参数否则控制台一直刷警告。如果你项目里已经有 POI用mvn dependency:tree看一眼有没有版本打架有就exclusions排掉。2.3 目标表怎么建才扛得住导入导入场景的表设计有两个原则字段留冗余、加唯一约束。业务方给的 Excel 列名千奇百怪但落库字段要固定。建表时给业务主键比如订单号、学号加唯一索引这样重复导入时可以用INSERT ... ON DUPLICATE KEY UPDATE做幂等而不是先delete再insert。CREATE TABLE t_import_order ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL COMMENT 业务唯一键, customer_name VARCHAR(128) DEFAULT NULL, amount DECIMAL(12,2) DEFAULT 0.00, import_batch VARCHAR(32) DEFAULT NULL COMMENT 批次号便于回滚, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_batch (import_batch) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;import_batch这个字段是后悔药一旦发现这批数据有问题DELETE FROM t_import_order WHERE import_batch ?就能整批撤掉不用逐条对。字符集统一utf8mb4否则业务方 Excel 里的 emoji 或生僻字入库直接报Incorrect string value。3. 读表用 EasyExcel 把行变成对象3.1 定义实体和表头映射EasyExcel 的用法是「实体类 注解」描述表头。ExcelProperty的value对应 Excel 表头文字index对应列序号从 0 开始。两者选一个用混用容易错位。import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; Data public class OrderRow { ExcelProperty(订单号) private String orderNo; ExcelProperty(客户名称) private String customerName; ExcelProperty(金额) private String amount; // 先收字符串后面自己转避免格式异常直接抛错 }这里amount故意用String接。业务方的 Excel 里金额可能是1,234.00、1234、空单元格直接映射成BigDecimal会在解析阶段抛ExcelDataConvertException整批导入中断。先收字符串在业务层做清洗容错率高得多。3.2 监听器里做校验和攒批EasyExcel 的核心是ReadListener每读一行回调一次invoke。不要在这个方法里直接写库一行一次insert就是性能杀手。正确做法是攒够一批再提交。import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.read.listener.ReadListener; import java.util.ArrayList; import java.util.List; public class OrderReadListener implements ReadListenerOrderRow { private static final int BATCH_SIZE 1000; private final ListOrderRow buffer new ArrayList(BATCH_SIZE); private final OrderService orderService; private int successCount 0; private int failCount 0; public OrderReadListener(OrderService orderService) { this.orderService orderService; } Override public void invoke(OrderRow row, AnalysisContext context) { // 行级校验订单号为空直接跳过并计数 if (row.getOrderNo() null || row.getOrderNo().trim().isEmpty()) { failCount; return; } buffer.add(row); if (buffer.size() BATCH_SIZE) { flush(); } } private void flush() { if (buffer.isEmpty()) return; successCount orderService.batchInsert(buffer); buffer.clear(); } Override public void doAfterAllAnalysed(AnalysisContext context) { flush(); // 收尾别漏掉最后不足一批的数据 } public int getSuccessCount() { return successCount; } public int getFailCount() { return failCount; } }逻辑说明invoke只做轻量校验和入缓冲flush才触发真正的批量写库。doAfterAllAnalysed是 EasyExcel 读完整个 sheet 后的回调必须在这里再flush一次否则最后不满 1000 条的数据永远进不了库——这是新手最常翻的车。参数说明BATCH_SIZE设 1000 是经验值。太小网络往返次数多太大单条 SQL 过长可能超过max_allowed_packetMySQL 默认 4MB也会让事务持有时间变长。1000 行、每行 200 字节左右SQL 大概 200KB安全。3.3 触发读取的入口public ImportResult importExcel(MultipartFile file) { String batch UUID.randomUUID().toString().replace(-, ); OrderReadListener listener new OrderReadListener(orderService); EasyExcel.read(file.getInputStream(), OrderRow.class, listener) .sheet() // 默认第一个 sheet .headRowNumber(1) // 表头占 1 行 .doRead(); return new ImportResult(batch, listener.getSuccessCount(), listener.getFailCount()); }headRowNumber(1)表示第一行是表头从第二行开始读数据。如果业务方的表前两行是标题和说明就改成2。sheet()不传参数读第一个 sheet多 sheet 场景用sheet(0)、sheet(1)指定。文件流记得在 finally 里关或者用 try-with-resources 包住。4. 写库批量插入和事务边界4.1 用 JDBC 批量插入而不是 MyBatis 逐条MyBatis 的foreach拼批量 SQL 也能用但拼接长度不可控且每次都要走 SQL 解析。数据导入这种场景直接用PreparedStatement.addBatch()更稳。public int batchInsert(ListOrderRow rows) { String sql INSERT INTO t_import_order(order_no, customer_name, amount, import_batch) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE customer_nameVALUES(customer_name), amountVALUES(amount); try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); for (OrderRow row : rows) { ps.setString(1, row.getOrderNo().trim()); ps.setString(2, row.getCustomerName()); ps.setBigDecimal(3, parseAmount(row.getAmount())); ps.setString(4, currentBatch); ps.addBatch(); } int[] result ps.executeBatch(); conn.commit(); return result.length; } catch (SQLException e) { throw new ImportException(批量写入失败, e); } }逻辑说明ON DUPLICATE KEY UPDATE依赖order_no上的唯一索引重复订单号会更新而不是报错天然幂等。executeBatch返回的int[]里成功是 1更新是 2失败是Statement.EXECUTE_FAILED可以据此统计。参数说明连接串上要加rewriteBatchedStatementstrue这是 MySQL 驱动的一个关键开关。不加addBatch会被拆成一条条发加了驱动会把它们合并成一条多值INSERT实测 1 万行插入能从 8 秒降到 1 秒以内。jdbc:mysql://127.0.0.1:3306/demo?useUnicodetruecharacterEncodingutf8mb4useSSLfalseserverTimezoneAsia/ShanghairewriteBatchedStatementstrue4.2 事务该包多大事务边界是导入场景最容易出事的地方。包太大锁持有时间长其他业务写同一张表会被阻塞包太小每条都提交性能又回去了。我的做法是按批提交也就是上面batchInsert里每 1000 行一个事务。这样单次事务持有时间在百毫秒级失败也只丢一批前面成功的批次已经落库。如果业务要求「全成功或全回滚」那就把整个导入包在一个大事务里但要接受两个后果一是导入期间表被锁二是失败时回滚日志可能撑爆 undo 空间。折中方案是导入到临时表全部成功后再INSERT INTO ... SELECT换表这个在最后一章展开。4.3 字段清洗的常见转换Excel 里的值落到 MySQL 前至少要做这几类清洗金额去掉千分位和货币符号、日期统一成yyyy-MM-dd HH:mm:ss、手机号去掉空格和横线、字符串trim掉首尾空白。这些逻辑放在parseAmount这类方法里不要塞进监听器保持监听器只做调度。private BigDecimal parseAmount(String raw) { if (raw null || raw.trim().isEmpty()) return BigDecimal.ZERO; String cleaned raw.replace(,, ).replace(, ).trim(); try { return new BigDecimal(cleaned); } catch (NumberFormatException e) { return BigDecimal.ZERO; // 脏数据兜底同时记日志 } }5. 避坑导入链路上最容易翻车的 5 个点5.1 现象导入 3 万行后 OOM堆内存直线上升原因用了 POI 的XSSFWorkbook一次性加载整个文件或者 EasyExcel 的监听器里把每行都塞进一个List攒着不释放。前者是模型问题后者是写法问题。解决读表换 EasyExcel 的 SAX 模式监听器里的缓冲必须设上限攒够就flush并clear。如果必须用 POI改用SXSSFWorkbook写或XSSFReader读的流式 API。5.2 现象中文表头读出来是乱码或者ExcelProperty匹配不上原因.xls老格式默认编码可能是 GBK或者表头里有看不见的空格、全角字符。EasyExcel 按字符串精确匹配表头差一个空格就映射不上字段全是 null。解决优先让业务方提供.xlsx读的时候用headRowNumber确认表头行号对表头做trim和全角转半角预处理。实在对不上改用index按列号映射放弃按名称匹配。5.3 现象批量插入报Packet for query is too large原因单批数据拼出来的 SQL 超过了 MySQL 的max_allowed_packet默认 4MB。行数多、字段长的时候很容易撞上。解决两条路。一是调大 MySQL 参数SET GLOBAL max_allowed_packet 64*1024*1024需重启或动态生效看版本二是把BATCH_SIZE从 1000 降到 500 或 200。生产环境我更倾向调小批次不动数据库全局参数影响面可控。5.4 现象导入到一半失败前面成功的批次留在库里数据半截原因按批提交时某一批因为脏数据或约束冲突抛异常但前面的批次已经 commit没有整体回滚机制。解决给每批数据打同一个import_batch失败时执行DELETE FROM t_import_order WHERE import_batch ?清理。或者导入前先写临时表全部成功再换表。前者简单后者彻底按业务对一致性的要求选。5.5 现象并发导入同一张表时死锁日志里全是Deadlock found原因多个导入任务同时按不同顺序更新同一批order_noInnoDB 行锁互相等待形成环。或者唯一索引冲突时加锁顺序不一致。解决导入任务加分布式锁或数据库层面的串行化同一张表的导入排队执行ON DUPLICATE KEY UPDATE场景下保证每批数据内部按order_no排序后再插入让加锁顺序一致能大幅降低死锁概率。6. 进阶临时表换表 导入结果可观测6.1 用临时表做「全成功才生效」的导入如果业务要求导入要么全成、要么全不动按批提交就不够了。稳妥做法是建一张影子表数据先灌影子表全部校验通过后一条RENAME TABLE原子换名。-- 1. 建影子表结构同正式表 CREATE TABLE t_import_order_tmp LIKE t_import_order; -- 2. 数据全部导入影子表Java 侧照常批量插入只是目标表换成 _tmp -- 3. 校验行数、关键字段 SELECT COUNT(*) FROM t_import_order_tmp WHERE import_batch xxx; -- 4. 原子换表毫秒级完成业务无感知 RENAME TABLE t_import_order TO t_import_order_bak, t_import_order_tmp TO t_import_order;RENAME TABLE在 InnoDB 下是原子的换表瞬间完成比INSERT INTO ... SELECT快几个数量级也不会长时间锁表。代价是需要额外磁盘空间放影子表以及换表后要处理旧表的清理。这套方案适合「导入频率低、数据量大、一致性要求高」的场景比如每月一次的对账数据导入。6.2 把导入过程变成可观测的导入失败最怕的是「不知道哪一行错了」。我的习惯是让监听器收集错误行号和原因导入结束后返回一个结果对象前端能直接展示。public class ImportResult { private String batch; private int successCount; private int failCount; private ListString errors new ArrayList(); // 格式第 15 行订单号为空 public void addError(int rowIndex, String reason) { if (errors.size() 100) { // 只留前 100 条避免结果对象过大 errors.add(第 rowIndex 行 reason); } } }在invoke里通过context.readRowHolder().getRowIndex()拿到当前行号从 0 开始表头是 0数据从 1 开始校验失败就addError。这样业务方拿到结果能自己定位问题行不用来回扯皮。6.3 一个我踩过的坑别在监听器里开事务早期我把Transactional加在监听器的invoke上想让它自动提交结果 EasyExcel 的回调不在 Spring 代理范围内注解根本不生效数据一条没进库还查不出原因。后来改成在flush里手动管理Connection的autoCommit问题才解决。教训是框架的回调方法不走 Spring AOP事务注解在这里是摆设要么手动控制连接要么把写库逻辑抽到独立的 Service 方法里通过代理调用。导入这件事代码量不大但每个环节都有边界条件。我现在的习惯是拿到需求先问清楚「最大多少行、要不要幂等、失败能不能重来」这三个答案决定了选型、事务和回滚方案。希望帮到你。本文还有配套的精品资源点击获取