簡(jiǎn)介這是一份面向Java初學(xué)者與后端開(kāi)發(fā)者的實(shí)戰(zhàn)型項(xiàng)目源碼聚焦Excel表格數(shù)據(jù)與MySQL數(shù)據(jù)庫(kù)之間的雙向流轉(zhuǎn)解決批量導(dǎo)入、重復(fù)數(shù)據(jù)更新及反向?qū)С龅瘸Ｒ?jiàn)業(yè)務(wù)需求。資源包共20個(gè)文件約1.31MB包含6個(gè)java源文件與6個(gè)class編譯文件、2個(gè)jar依賴庫(kù)如mysql-connector與jxl、1個(gè)sql建表腳本以及Eclipse工程配置與說(shuō)明文檔結(jié)構(gòu)完整可直接導(dǎo)入運(yùn)行。項(xiàng)目覆蓋Java文件操作、Apache POI解析單元格、JDBC連接MySQL、INSERT與UPDATE條件判斷、批量插入與事務(wù)處理等核心知識(shí)點(diǎn)并演示從數(shù)據(jù)庫(kù)查詢結(jié)果寫(xiě)回Excel的完整鏈路。目前已有1737人學(xué)習(xí)下載適合希望通過(guò)一個(gè)可運(yùn)行案例打通數(shù)據(jù)導(dǎo)入導(dǎo)出、鞏固JDBC與SQL實(shí)操能力的學(xué)習(xí)者參考。1. Java 把 Excel 灌進(jìn) MySQL一條被低估的批量導(dǎo)入鏈路電商后臺(tái)導(dǎo)商品、教務(wù)系統(tǒng)導(dǎo)成績(jī)、財(cái)務(wù)導(dǎo)流水幾乎每個(gè) Java 項(xiàng)目都會(huì)撞上「Excel 數(shù)據(jù)導(dǎo)入到 MySQL」這件事。標(biāo)題里三個(gè)詞——Java、Excel、MySQL——看著簡(jiǎn)單真做起來(lái)翻車點(diǎn)全在細(xì)節(jié)里幾萬(wàn)行數(shù)據(jù)用 POI 的XSSFWorkbook直接 OOM日期列讀出來(lái)變成一串?dāng)?shù)字手機(jī)號(hào)被 Excel 自動(dòng)轉(zhuǎn)成科學(xué)計(jì)數(shù)法導(dǎo)入一半報(bào)主鍵沖突整批回滾。我見(jiàn)過(guò)太多人第一版寫(xiě)完能跑通 500 行上線遇到 5 萬(wàn)行就崩。這篇筆記就圍繞「Java 實(shí)現(xiàn) Excel 數(shù)據(jù)導(dǎo)入到 MySQL」這條鏈路從選型、讀文件、批量寫(xiě)庫(kù)、事務(wù)邊界一路講到避坑。適合正在做數(shù)據(jù)導(dǎo)入功能的后端開(kāi)發(fā)也適合被excel加載項(xiàng)被禁用、excel ctrl v 失效這類問(wèn)題折騰過(guò)、想徹底搞明白數(shù)據(jù)怎么從表格進(jìn)數(shù)據(jù)庫(kù)的人。目標(biāo)很明確給你一套能直接抄、能扛住幾萬(wàn)行、參數(shù)知道怎么調(diào)的方案。2. 選型先定死POI、EasyExcel 還是流式讀動(dòng)手前先把「用什么讀 Excel」定下來(lái)這一步選錯(cuò)后面全是補(bǔ)丁。Java 生態(tài)里讀 Excel 主流就三樣Apache POI 原生、阿里 EasyExcel、以及 POI 的流式 APISXSSF/事件模型。選型不看誰(shuí)名氣大看你的數(shù)據(jù)量和內(nèi)存預(yù)算。2.1 三種讀法的內(nèi)存賬要算清楚POI 的XSSFWorkbook是 DOM 模式把整個(gè) xlsx 一次性加載進(jìn)內(nèi)存建對(duì)象樹(shù)。一個(gè) 10 萬(wàn)行、20 列的 xlsx堆內(nèi)存輕松吃掉 1G 以上OutOfMemoryError: Java heap space是標(biāo)配。它的好處是 API 直觀getRow、getCell隨手就取適合幾千行以內(nèi)的小文件。EasyExcel 是阿里開(kāi)源的封裝底層做了 SAX 事件解析一行一行回調(diào)內(nèi)存占用基本恒定跟文件行數(shù)無(wú)關(guān)。它的注解模型ExcelProperty對(duì)業(yè)務(wù)開(kāi)發(fā)很友好讀出來(lái)的對(duì)象直接映射成實(shí)體。代價(jià)是遇到復(fù)雜合并單元格、公式單元格時(shí)行為不如 POI 原生可控。POI 的XSSFReaderSheetContentsHandler事件模型是最底層的流式讀法內(nèi)存最省但代碼量大每個(gè)單元格要自己判斷類型、自己拼行寫(xiě)起來(lái)啰嗦。適合對(duì)性能極致敏感、又不想引第三方庫(kù)的場(chǎng)景。我的默認(rèn)選擇是 EasyExcel中小項(xiàng)目省事大數(shù)據(jù)量也能扛。只有在需要精細(xì)控制公式求值、或者公司禁止引第三方依賴時(shí)才退回 POI 事件模型。方案內(nèi)存占用開(kāi)發(fā)成本適用行數(shù)復(fù)雜單元格XSSFWorkbook高隨行數(shù)線性漲低幾千行支持好EasyExcel低基本恒定低幾萬(wàn)到百萬(wàn)一般POI 事件模型最低高百萬(wàn)級(jí)需自己處理2.2 依賴怎么引版本別亂跳Maven 里引 EasyExcel注意它內(nèi)部依賴 POI別自己再引一個(gè)沖突版本。常見(jiàn)做法是只引 EasyExcel讓它帶 POI 進(jìn)來(lái)dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency如果你項(xiàng)目里已經(jīng)有 POI比如做導(dǎo)出功能要檢查版本對(duì)齊。EasyExcel 3.x 一般配 POI 4.1.x 或 5.x混用容易出現(xiàn)NoSuchMethodError。排查方法很簡(jiǎn)單mvn dependency:tree | grep poi看最終生效的是哪個(gè)版本沖突就exclusions排掉舊的。提示xls老格式和 xlsx新格式底層實(shí)現(xiàn)不同EasyExcel 會(huì)自動(dòng)識(shí)別但 xls 單表上限 65536 行超了直接報(bào)錯(cuò)導(dǎo)入前最好校驗(yàn)文件后綴和行數(shù)。2.3 數(shù)據(jù)庫(kù)側(cè)的準(zhǔn)備表結(jié)構(gòu)和字符集MySQL 這邊導(dǎo)入表建議單獨(dú)建別直接往業(yè)務(wù)主表灌。字符集統(tǒng)一用utf8mb4否則中文和 emoji 會(huì)出問(wèn)題。一個(gè)典型的導(dǎo)入臨時(shí)表CREATE TABLE t_import_staging ( id BIGINT PRIMARY KEY AUTO_INCREMENT, row_no INT COMMENT Excel行號(hào)用于定位錯(cuò)誤, phone VARCHAR(20), name VARCHAR(64), amount DECIMAL(12,2), import_batch VARCHAR(32) COMMENT 批次號(hào), create_time DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;row_no這個(gè)字段很多人不加等出錯(cuò)要定位「第幾行數(shù)據(jù)有問(wèn)題」時(shí)就抓瞎了。import_batch用于批次回滾導(dǎo)入失敗按批次刪數(shù)據(jù)比逐行刪干凈。字段類型要和 Excel 列語(yǔ)義對(duì)齊金額用DECIMAL不用FLOAT避免精度丟失手機(jī)號(hào)用VARCHAR不用BIGINT防止前導(dǎo)零丟失。3. 讀 Excel 的實(shí)操?gòu)淖⒔庥成涞筋愋娃D(zhuǎn)換選型定了進(jìn)入讀文件環(huán)節(jié)。這一章把「怎么把一行 Excel 變成 Java 對(duì)象」講透重點(diǎn)在類型轉(zhuǎn)換和校驗(yàn)因?yàn)?90% 的導(dǎo)入 bug 都出在這里。3.1 用注解把列映射成實(shí)體EasyExcel 的核心是實(shí)體類加注解。假設(shè) Excel 有「手機(jī)號(hào)、姓名、金額」三列Data public class ImportRow { ExcelProperty(index 0) private String phone; ExcelProperty(index 1) private String name; ExcelProperty(index 2) private BigDecimal amount; }index按列順序從 0 開(kāi)始比用value 手機(jī)號(hào)匹配表頭更穩(wěn)——表頭文字改一個(gè)字按名字匹配就全錯(cuò)位按 index 不受影響。代價(jià)是列順序變了要改代碼所以導(dǎo)入模板要固定別讓用戶隨便調(diào)列。讀的時(shí)候用監(jiān)聽(tīng)器模式別用EasyExcel.read(...).doReadSync()一次性讀全部那個(gè)方法會(huì)把所有行攢在內(nèi)存里等于白用流式EasyExcel.read(inputStream, ImportRow.class, new ImportListener()) .sheet() .doRead();3.2 監(jiān)聽(tīng)器里做校驗(yàn)和攢批監(jiān)聽(tīng)器是流式讀的核心invoke每讀一行調(diào)一次doAfterAllAnalysed全部讀完調(diào)一次。攢批寫(xiě)庫(kù)就靠這兩個(gè)方法配合public class ImportListener extends AnalysisEventListenerImportRow { private static final int BATCH_SIZE 1000; private final ListImportRow buffer new ArrayList(BATCH_SIZE); private final ImportService service; public ImportListener(ImportService service) { this.service service; } Override public void invoke(ImportRow row, AnalysisContext ctx) { int rowNo ctx.readRowHolder().getRowIndex() 1; // 校驗(yàn)手機(jī)號(hào)非空且為11位數(shù)字 if (row.getPhone() null || !row.getPhone().matches(\\d{11})) { throw new IllegalArgumentException(第 rowNo 行手機(jī)號(hào)非法); } buffer.add(row); if (buffer.size() BATCH_SIZE) { service.batchInsert(buffer); buffer.clear(); } } Override public void doAfterAllAnalysed(AnalysisContext ctx) { if (!buffer.isEmpty()) { service.batchInsert(buffer); buffer.clear(); } } }BATCH_SIZE設(shè) 1000 是個(gè)經(jīng)驗(yàn)值太小則 SQL 次數(shù)多、網(wǎng)絡(luò)往返開(kāi)銷大太大則單條 SQL 過(guò)長(zhǎng)可能撞max_allowed_packet。1000 行、每行幾個(gè)字段拼出來(lái)的INSERT一般幾十 KB安全。ctx.readRowHolder().getRowIndex()拿的是 0 基行號(hào)加 1 才是用戶看到的行號(hào)報(bào)錯(cuò)信息里帶上它用戶能直接定位。3.3 類型轉(zhuǎn)換的坑日期、數(shù)字、科學(xué)計(jì)數(shù)法Excel 單元格類型是玄學(xué)重災(zāi)區(qū)。日期在 xlsx 里存的是數(shù)字從 1900-01-01 起的天數(shù)POI 讀出來(lái)是double直接toString會(huì)得到45123這種鬼東西。EasyExcel 用DateTimeFormat能自動(dòng)轉(zhuǎn)ExcelProperty(index 3) DateTimeFormat(yyyy-MM-dd) private Date orderDate;但前提是單元格本身是日期格式。如果用戶把日期填成了文本「2024/1/5」注解轉(zhuǎn)換會(huì)失敗。穩(wěn)妥做法是讀成String自己在業(yè)務(wù)層用DateTimeFormatter多格式嘗試解析兼容yyyy-MM-dd、yyyy/MM/dd兩種寫(xiě)法。手機(jī)號(hào)、身份證這類長(zhǎng)數(shù)字Excel 默認(rèn)按數(shù)值處理超過(guò) 11 位就變科學(xué)計(jì)數(shù)法1.38E10。解決辦法有兩個(gè)一是要求用戶導(dǎo)入前把該列設(shè)成文本格式二是讀的時(shí)候統(tǒng)一按String接EasyExcel 對(duì)數(shù)值型單元格轉(zhuǎn) String 時(shí)會(huì)盡量保留原值但科學(xué)計(jì)數(shù)法已經(jīng)發(fā)生的救不回來(lái)。所以模板里最好把手機(jī)號(hào)列預(yù)設(shè)成文本這是最省心的。注意金額列如果 Excel 里帶千分位逗號(hào)1,234.56直接轉(zhuǎn)BigDecimal會(huì)拋NumberFormatException。讀成 String 后先replace(,, )再轉(zhuǎn)。4. 批量寫(xiě) MySQLJDBC 批處理與事務(wù)邊界數(shù)據(jù)讀進(jìn)來(lái)了怎么寫(xiě)庫(kù)是另一半。逐條insert在幾萬(wàn)行面前就是災(zāi)難必須走批處理。這一章講清楚 JDBC batch、事務(wù)邊界、以及和 MyBatis 的配合。4.1 JDBC 原生批處理怎么寫(xiě)不用 ORM 的話PreparedStatement.addBatch()executeBatch()是最直接的批量插入public void batchInsert(ListImportRow rows) { String sql INSERT INTO t_import_staging(phone,name,amount,import_batch) VALUES(?,?,?,?); try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); for (ImportRow row : rows) { ps.setString(1, row.getPhone()); ps.setString(2, row.getName()); ps.setBigDecimal(3, row.getAmount()); ps.setString(4, currentBatchNo); ps.addBatch(); } ps.executeBatch(); conn.commit(); } catch (SQLException e) { throw new RuntimeException(批量插入失敗, e); } }關(guān)鍵在連接串上要開(kāi)rewriteBatchedStatementstrue否則 MySQL 驅(qū)動(dòng)會(huì)把 batch 拆成一條條發(fā)批量等于沒(méi)批jdbc:mysql://localhost:3306/db?rewriteBatchedStatementstrueuseServerPrepStmtstruerewriteBatchedStatementstrue讓驅(qū)動(dòng)把多條INSERT重寫(xiě)成INSERT INTO ... VALUES (...),(...),(...)一條語(yǔ)句性能能差好幾倍。useServerPrepStmtstrue讓預(yù)處理在服務(wù)端做配合使用效果更好。這兩個(gè)參數(shù)是批量導(dǎo)入的必調(diào)項(xiàng)很多人不知道寫(xiě)完發(fā)現(xiàn)慢還以為是數(shù)據(jù)庫(kù)問(wèn)題。4.2 事務(wù)邊界整批一個(gè)事務(wù)還是分批提交事務(wù)粒度是個(gè)權(quán)衡。整批一個(gè)事務(wù)幾萬(wàn)行一次 commit的好處是失敗全回滾數(shù)據(jù)一致壞處是事務(wù)日志大、鎖持有時(shí)間長(zhǎng)、失敗重試成本高。分批提交每 1000 行一個(gè)事務(wù)吞吐高但中途失敗會(huì)留下半截?cái)?shù)據(jù)。我的做法是導(dǎo)入到臨時(shí)表用import_batch標(biāo)記批次每批一個(gè)事務(wù)。全部導(dǎo)完后再用一條INSERT INTO 業(yè)務(wù)表 SELECT ... FROM 臨時(shí)表 WHERE import_batch?做最終落庫(kù)。這樣即使中途失敗按批次號(hào)刪掉臨時(shí)數(shù)據(jù)重來(lái)即可業(yè)務(wù)表始終干凈。這個(gè)模式在數(shù)據(jù)量大的場(chǎng)景下比單事務(wù)更可控。4.3 和 MyBatis 配合時(shí)的寫(xiě)法項(xiàng)目用 MyBatis 的話別在 XML 里foreach拼幾萬(wàn)行VALUESSQL 會(huì)超長(zhǎng)。正確姿勢(shì)是 Mapper 方法接收List用ExecutorType.BATCHAutowired private SqlSessionFactory sqlSessionFactory; public void batchInsert(ListImportRow rows) { try (SqlSession session sqlSessionFactory.openSession(ExecutorType.BATCH)) { ImportMapper mapper session.getMapper(ImportMapper.class); for (ImportRow row : rows) { mapper.insert(row); } session.commit(); } }ExecutorType.BATCH會(huì)把多次insert攢起來(lái)到 commit 時(shí)一起發(fā)。注意它和一級(jí)緩存、自增主鍵回填有交互如果業(yè)務(wù)依賴插入后拿自增 IDBATCH 模式下拿不到得改用其他方式。這是踩過(guò)的坑別等主鍵為 null 才發(fā)現(xiàn)。5. 避坑與排查導(dǎo)入功能最常見(jiàn)的 5 個(gè)翻車現(xiàn)場(chǎng)功能能跑通不代表能上線。這一章列 5 個(gè)我真實(shí)遇到過(guò)的坑按「現(xiàn)象 → 原因 → 解決」寫(xiě)都是血淚經(jīng)驗(yàn)。5.1 現(xiàn)象幾萬(wàn)行導(dǎo)入直接 OOM原因用了XSSFWorkbook或doReadSync()整個(gè)文件加載進(jìn)內(nèi)存。解決換 EasyExcel 監(jiān)聽(tīng)器模式或 POI 事件模型確保讀的時(shí)候不攢全量數(shù)據(jù)。檢查方法把 JVM 堆調(diào)小到 256M 跑一次能過(guò)說(shuō)明是真流式。5.2 現(xiàn)象導(dǎo)入后中文變問(wèn)號(hào)原因數(shù)據(jù)庫(kù)連接串沒(méi)指定字符集或表字符集是latin1。解決連接串加characterEncodingutf8表統(tǒng)一utf8mb4。已經(jīng)亂碼的數(shù)據(jù)救不回來(lái)只能重導(dǎo)。排查時(shí)先SHOW CREATE TABLE看表字符集再看連接串。5.3 現(xiàn)象日期列讀出來(lái)是 45123 這種數(shù)字原因單元格是日期格式POI 讀成數(shù)值型。解決實(shí)體字段加DateTimeFormat或讀成 String 后自己解析。如果用戶填的是文本日期注解會(huì)失效所以業(yè)務(wù)層要有兜底解析邏輯。5.4 現(xiàn)象批量插入慢幾萬(wàn)行要幾分鐘原因連接串沒(méi)開(kāi)rewriteBatchedStatementstruebatch 被拆成單條。解決加上這個(gè)參數(shù)配合BATCH_SIZE1000 左右。驗(yàn)證方法開(kāi) MySQL 的general_log看實(shí)際發(fā)出的 SQL如果是一條條INSERT就是沒(méi)生效。5.5 現(xiàn)象導(dǎo)入一半報(bào)主鍵沖突整批回滾原因Excel 里有重復(fù)數(shù)據(jù)或和已有數(shù)據(jù)沖突。解決導(dǎo)入前用臨時(shí)表 批次號(hào)沖突在臨時(shí)表階段暴露業(yè)務(wù)表不受影響對(duì)重復(fù)行做去重或標(biāo)記跳過(guò)。別用INSERT IGNORE掩蓋問(wèn)題該報(bào)的錯(cuò)要報(bào)出來(lái)讓用戶改。6. 進(jìn)階把導(dǎo)入做成可復(fù)用、可回滾的組件前面講的是一條能跑通的鏈路但真實(shí)項(xiàng)目里導(dǎo)入功能會(huì)被反復(fù)用值得把它抽成組件。我一般會(huì)做三件事統(tǒng)一模板校驗(yàn)、批次回滾、異步化。模板校驗(yàn)放在讀之前校驗(yàn)表頭列名和順序是否匹配不匹配直接拒絕避免讀到一半才發(fā)現(xiàn)列錯(cuò)位。批次回滾靠import_batch字段提供一個(gè)「撤銷本次導(dǎo)入」的接口按批次號(hào)刪臨時(shí)表數(shù)據(jù)。異步化則是把導(dǎo)入任務(wù)丟進(jìn)線程池或消息隊(duì)列接口立即返回任務(wù) ID前端輪詢進(jìn)度避免大文件導(dǎo)入把 HTTP 請(qǐng)求拖超時(shí)。進(jìn)度反饋有個(gè)小技巧EasyExcel 的監(jiān)聽(tīng)器里能拿到AnalysisContext但拿不到總行數(shù)?？梢栽谧x之前先用事件模型掃一遍 sheet 拿總行數(shù)或者干脆按已處理行數(shù)報(bào)進(jìn)度前端顯示「已處理 N 行」。別為了精確百分比再讀一遍文件得不償失。驗(yàn)證導(dǎo)入結(jié)果我習(xí)慣寫(xiě)一個(gè)對(duì)賬 SQL臨時(shí)表行數(shù)、業(yè)務(wù)表新增行數(shù)、Excel 原始行數(shù)三者對(duì)齊差一行都要查。這個(gè)習(xí)慣幫我抓到過(guò)好幾次「監(jiān)聽(tīng)器漏了最后一批 buffer」的 bug——doAfterAllAnalysed里忘了 flush 剩余數(shù)據(jù)前面全對(duì)就差最后不足 1000 行的那批。最后說(shuō)個(gè)我自己的教訓(xùn)導(dǎo)入功能一定要在測(cè)試環(huán)境用真實(shí)量級(jí)的數(shù)據(jù)壓一遍別拿 100 行測(cè)完就上線。我吃過(guò)一次虧500 行跑得飛快生產(chǎn) 8 萬(wàn)行直接把數(shù)據(jù)庫(kù)連接池打滿因?yàn)槊颗夹麻_(kāi)連接沒(méi)復(fù)用。后來(lái)改成批量方法內(nèi)復(fù)用連接、控制并發(fā)才穩(wěn)住。數(shù)據(jù)導(dǎo)入這活兒細(xì)節(jié)決定成敗慢一點(diǎn)、穩(wěn)一點(diǎn)比快更重要。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取