简介这份资源面向Java后端初学者与需要处理数据迁移的开发者提供了一套完整的Excel与MySQL双向数据同步示例。项目基于Apache POI解析xls/xlsx文件通过JDBC连接MySQL实现Excel数据导入数据库并在检测到重复数据时执行更新同时支持将库中数据反向导出为Excel表格覆盖文件操作、数据解析、SQL编写与事务处理等核心技能。压缩包共20个文件约1.31MB包含6个java源码与6个class编译文件、2个依赖jarmysql驱动与jxl、1个建表sql脚本以及Eclipse工程配置与说明文档结构清晰便于直接导入运行。目前已有1737人学习下载适合作为课程设计、毕业项目或日常数据导入导出工具的参考实现帮助读者快速理解批量插入、条件更新与结果集写回Excel的完整链路。1. Java 把 Excel 灌进 MySQL一条被低估的脏活链路电商后台的运营同事丢过来一个 12 万行的 Excel说「导一下库存」你打开一看合并单元格、日期列混着文本、手机号被存成科学计数法、还有三行是空行夹在中间。这时候你才意识到Excel 导入 MySQL 这件事从来不是read → insert两行代码能收场的。它是一条完整的脏活链路文件解析、类型推断、批量写入、事务边界、失败重试每一环都能让你加班到凌晨。这个方向适合谁做后台管理系统的 Java 工程师、需要给业务方做数据迁移的开发者、以及正在准备java 面试题里「大文件导入怎么优化」这类问题的同学。它不需要你上大数据栈spring boot mybatis或者纯 JDBC 就能跑通但要把 10 万行级别的导入做到「不 OOM、不超时、可回滚、能定位坏行」里面全是血泪经验。下面我按自己实际落地的顺序把选型、代码、参数和翻车点一次讲透。2. 选型先立住POI、EasyExcel 还是流式 SAX2.1 三种解析路线的内存账很多人一上来就new XSSFWorkbook(inputStream)本地跑 5000 行没问题生产上 8 万行直接OutOfMemoryError: Java heap space。原因很简单XSSF 会把整个 xlsx 解压后的 XML 树全部加载进内存一个 10 万行、20 列的文件堆里轻松吃掉 1.5G 以上。所以选型的第一原则是——先看内存模型再看 API 好不好用。方案内存模型10 万行大致堆占用适用场景Apache POI XSSF全量 DOM1.5G ~ 3G小文件、需要随机读写单元格POI SXSSF写/ SAX读滑动窗口 / 事件流100M 以内大文件只读或只写EasyExcel基于 SAX 封装80M ~ 200M业务导入要监听器回调纯 CSV 中转逐行读极低允许业务方先另存为 CSV我一般会文件超过 2 万行直接上 EasyExcel 或 POI 的 SAX 事件模型。EasyExcel 的优势是把 SAX 那套ContentHandler的样板代码封成了ReadListener你只关心「读到一行怎么处理」不用手写startElement / endElement的状态机。代价是它对复杂公式、图表、宏的支持有限——但导入场景本来也不需要这些。2.2 依赖怎么引版本别乱跳!-- pom.xml 片段 -- dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version !-- 3.x 与 2.x 的监听器 API 不兼容别混用 -- /dependency dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency这里有个容易翻车的点EasyExcel 3.x 把AnalysisEventListener的泛型回调签名做了调整网上大量 2.x 的示例代码直接抄过来会编译不过。如果你项目里已经有别的模块在用 2.x要么统一升到 3.x要么给导入模块单独做依赖隔离别让两个版本在同一个 classpath 里打架。2.3 数据库侧的前置准备导入性能有一半不在 Java 代码里而在 MySQL 的配置和表结构上。建表时几个必须确认的点CREATE TABLE product_import ( id BIGINT NOT NULL AUTO_INCREMENT, sku VARCHAR(64) NOT NULL COMMENT 商品编码, name VARCHAR(255) DEFAULT NULL, price DECIMAL(10,2) DEFAULT NULL, stock INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_sku (sku) -- 唯一索引是幂等导入的关键 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;uk_sku这个唯一索引不是可选项。没有它你重复导入同一个文件就会产生重复数据而业务方几乎一定会重复点「导入」按钮。有了它配合INSERT ... ON DUPLICATE KEY UPDATE或者INSERT IGNORE导入天然具备幂等性。另外utf8mb4别省Excel 里一个 emoji 就能让utf8的列插入报Incorrect string value。3. 从 Excel 到 MySQL 的最小可跑通实现3.1 定义实体和监听器先定义一个和表结构对应的实体字段上用注解标好列头名称。EasyExcel 默认按列顺序读但生产上列顺序经常被业务方调整所以强烈建议按列头名称匹配而不是按 index。Data public class ProductRow { ExcelProperty(商品编码) private String sku; ExcelProperty(商品名称) private String name; ExcelProperty(价格) private BigDecimal price; ExcelProperty(库存) private Integer stock; }监听器是核心它决定「每读一行做什么」。关键设计是攒批不要读一行插一次那样 10 万行就是 10 万次网络往返慢到怀疑人生。攒够 1000 行批量写一次性能能差出两个数量级。public class ProductImportListener extends AnalysisEventListenerProductRow { private static final int BATCH_SIZE 1000; private final ListProductRow buffer new ArrayList(BATCH_SIZE); private final ProductMapper mapper; private int successCount 0; public ProductImportListener(ProductMapper mapper) { this.mapper mapper; } Override public void invoke(ProductRow row, AnalysisContext context) { // 基础校验编码为空直接跳过并记录行号 if (row.getSku() null || row.getSku().trim().isEmpty()) { return; } buffer.add(row); if (buffer.size() BATCH_SIZE) { flush(); } } private void flush() { if (buffer.isEmpty()) return; successCount mapper.batchInsert(buffer); buffer.clear(); // 必须清空否则内存持续增长 } Override public void doAfterAllAnalysed(AnalysisContext context) { flush(); // 处理最后不足一批的尾巴数据 } public int getSuccessCount() { return successCount; } }invoke里做校验、flush里做批量写、doAfterAllAnalysed里收尾——这三个方法的职责要分清。最容易犯的错是忘了在doAfterAllAnalysed里再 flush 一次结果最后几百行永远进不了库而且不报错属于典型的「静默丢数据」。3.2 Mapper 的批量插入 SQLMyBatis 的foreach批量插入写法insert idbatchInsert parameterTypejava.util.List INSERT INTO product_import (sku, name, price, stock) VALUES foreach collectionlist itemitem separator, (#{item.sku}, #{item.name}, #{item.price}, #{item.stock}) /foreach ON DUPLICATE KEY UPDATE name VALUES(name), price VALUES(price), stock VALUES(stock) /insertON DUPLICATE KEY UPDATE让重复的 sku 走更新而不是报错这就是幂等。但要注意单条 SQL 的max_allowed_packet限制。1000 行 × 4 列拼出来的 SQL 可能超过默认的 4M报Packet for query is too large。解决办法有两个把BATCH_SIZE降到 500或者调大 MySQL 的max_allowed_packetSET GLOBAL max_allowed_packet 33554432;。我一般两个都做双保险。3.3 入口方法事务和异常边界PostMapping(/import) public Result importExcel(RequestParam(file) MultipartFile file) throws IOException { ProductImportListener listener new ProductImportListener(productMapper); EasyExcel.read(file.getInputStream(), ProductRow.class, listener) .sheet() .doRead(); return Result.ok(成功导入 listener.getSuccessCount() 行); }这里有个必须想清楚的问题要不要把整个导入包在一个事务里我的经验是——不要。10 万行一个大事务undo log 撑爆、锁持有时间过长、失败回滚代价巨大。正确做法是按批提交每 1000 行一个独立事务失败的那一批单独记录其余照常入库。这样即使中间有坏数据也不会让整个文件前功尽弃。如果业务强要求「全成功或全失败」那就得引入导入任务表 状态机而不是靠数据库大事务硬扛。4. 参数调优与坏数据排查4.1 三个必调参数JDBC 的rewriteBatchedStatementstrue。这是 MySQL 驱动里最容易被忽略、收益又最大的参数。不开它你的foreach批量插入会被驱动拆成一条条单行 insert 发给服务端开了它驱动会把它们重写成真正的多值 INSERT。实测 10 万行导入开启前后能差 3~5 倍。spring.datasource.urljdbc:mysql://localhost:3306/demo?\ rewriteBatchedStatementstrue\ useServerPrepStmtstrue\ characterEncodingutf8mb4BATCH_SIZE。太小网络往返多太大 SQL 超包且内存占用高。1000 是甜点区但如果你单行字段特别多比如 30 列以上降到 300~500 更稳。max_allowed_packet。前面提过配合批量大小一起调。服务端和客户端都要确认别只改一边。4.2 坏数据怎么定位到具体行EasyExcel 的AnalysisContext里能拿到当前行号但默认是 0 基的物理行号和用户在 Excel 里看到的行号差 1表头占一行。定位坏行时把行号、原始单元格值、异常信息一起记下来返回给用户一个「错误报告」比一句「导入失败」有用得多。Override public void onException(Exception exception, AnalysisContext context) { if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException e (ExcelDataConvertException) exception; // 物理行号 1 才是用户在 Excel 里看到的行号 log.warn(第 {} 行第 {} 列转换失败原始值{}, e.getRowIndex() 1, e.getColumnIndex(), e.getCellData().getStringValue()); } }ExcelDataConvertException专门处理「单元格是文本但实体字段是数字/日期」这类转换失败。重写onException后默认行为是抛异常中断整个导入你可以改成「记录并跳过」让好数据先入库。4.3 日期和数字的经典陷阱Excel 里的日期本质是一个浮点数从 1900-01-01 起的天数POI 读出来是double。如果实体字段是String你会拿到45123这种鬼东西。解决办法是字段用Date类型或者自定义Converter。数字列同理手机号、身份证号这种长数字Excel 默认按科学计数法显示读出来可能是1.38E10必须在导入前让业务方把列格式设成文本或者在代码里做还原。5. 避坑与常见问题排查现象导入 3 万行后 OOM堆内存持续上涨。原因监听器里的buffer在 flush 后没clear()或者把每一行都塞进了一个成员变量 List 里做「统计」。EasyExcel 的监听器是单例复用的任何成员集合都必须及时清理。 解决flush 后立即buffer.clear()统计只存计数不存对象用jmap -histo:live确认是不是ProductRow实例堆积。现象报Packet for query is too large。原因批量 SQL 拼接后超过了max_allowed_packet。 解决调小BATCH_SIZE到 500同时SET GLOBAL max_allowed_packet 33554432重启连接池让新连接生效。现象中文列头匹配不上所有字段都是 null。原因Excel 文件编码或列头有前后空格、全角空格。EasyExcel 按字符串精确匹配列头。 解决读之前先 trim或者用headRowNumber(1)明确表头行必要时自定义HeadNameMatcher做模糊匹配。现象重复导入产生重复数据。原因表上没有唯一索引或者用了普通INSERT而非ON DUPLICATE KEY UPDATE。 解决加唯一索引SQL 改成 upsert如果历史数据已经有重复先清洗再加索引否则建索引会失败。现象导入成功但部分行丢失且无报错。原因doAfterAllAnalysed里没 flush 最后一批或者invoke里的校验return掉了却没记录。 解决收尾必 flush所有跳过逻辑都要计数并输出别让数据静默消失。6. 进阶把导入做成可观测、可重试的任务到这一步基本功能已经能跑了。但生产环境真正需要的是可观测和可重试。我现在的习惯是给每次导入建一条任务记录任务 ID、文件名、总行数、成功数、失败数、状态、错误详情。导入过程中每批更新一次进度前端就能显示进度条失败的行单独存到一张import_error表用户可以下载错误报告改完只重传失败部分。验证导入是否真的成功别只看接口返回的「成功 N 行」。我会做三件事一是SELECT COUNT(*)对比源文件行数减去表头和空行二是抽查几个 sku 的字段值确认没有类型错位三是用CHECKSUM TABLE或者对关键列做SUM聚合和 Excel 里用公式算出来的结果对一遍。这三步能挡住 90% 的「看起来成功其实数据错了」。一个具体技巧用LOAD DATA LOCAL INFILE做兜底。当 Excel 特别大50 万行以上且格式规整时先用脚本把 xlsx 转成 CSV再用 MySQL 原生的LOAD DATA导入速度比任何 Java 方案都快一个数量级。代价是它绕过了应用层的校验所以只适合内部可信数据源。我一般把它作为「应急通道」平时还是走 Java 链路因为校验、日志、幂等这些能力都在应用层。最后说个我踩过的坑有次为了图快把BATCH_SIZE设成 5000本地测试飞快上线后遇到一个字段特别长的商品描述直接触发max_allowed_packet整个导入挂掉。从那以后我养成了一个习惯——批量大小永远留余量宁可 500 不赌 5000。参数这东西稳比快重要。希望帮到你。本文还有配套的精品资源点击获取