現(xiàn)數(shù)據(jù)遷移提效)
簡介這是一份面向Java開發(fā)者與數(shù)據(jù)庫運(yùn)維人員的數(shù)據(jù)處理工具源碼包用于將Excel表格數(shù)據(jù)自動解析并生成可執(zhí)行的SQL腳本再通過JDBC連接寫入目標(biāo)數(shù)據(jù)庫適用于數(shù)據(jù)遷移、批量導(dǎo)入與數(shù)據(jù)分析等場景。壓縮包共56個文件約24.48MB包含21個jar依賴、20個class與11個java源碼另有classpath配置、project工程文件、fatjar打包產(chǎn)物及一份xls測試數(shù)據(jù)模板結(jié)構(gòu)完整便于直接運(yùn)行與二次開發(fā)。資源核心圍繞Apache POI解析Excel、構(gòu)造INSERT等SQL語句、配置數(shù)據(jù)庫驅(qū)動并管理連接執(zhí)行源碼可幫助讀者理解從表格字段映射到語句生成、再到JDBC執(zhí)行與異常處理的完整鏈路。目前已有979人學(xué)習(xí)下載適合希望掌握Excel到數(shù)據(jù)庫自動化轉(zhuǎn)換思路的開發(fā)者參考借鑒。1. 從手工拼 SQL 到批量生成一個被低估的提效工具做數(shù)據(jù)遷移或者后臺初始化的時候最煩的不是寫 SQL而是把 Excel 里幾百上千行配置數(shù)據(jù)一行行拼成 INSERT 語句。字段一多引號、逗號、NULL 值、日期格式隨便哪個地方手滑就是一條報錯。更別提產(chǎn)品經(jīng)理隔三差五丟過來一個「補(bǔ)充數(shù)據(jù).xlsx」你還得重新對一遍字段順序。我最近拆的一個小工具就是干這件事的讀取 Excel按模板生成可直接執(zhí)行的 SQL 腳本同時支持通過 JDBC 連接數(shù)據(jù)庫做校驗或直接寫入。它解決的核心問題很具體——把「人肉拼 SQL」變成「配置一次、批量產(chǎn)出」。適合誰做數(shù)據(jù)初始化、配置表導(dǎo)入、測試數(shù)據(jù)構(gòu)造的后端和運(yùn)維同學(xué)尤其是那種表結(jié)構(gòu)固定、數(shù)據(jù)來源是 Excel 的場景。下面按「它怎么工作 → 怎么跑起來 → 坑在哪 → 怎么用得更順」的順序拆開講。2. 解析 Excel 生成 SQL 的底層邏輯POI、JDBC 與模板三件套2.1 為什么是 POI JDBC 這套組合Excel 解析這塊Java 生態(tài)里最穩(wěn)的還是 Apache POI。它同時支持.xlsHSSF和.xlsxXSSF能拿到單元格的類型、格式、公式結(jié)果這對生成 SQL 很關(guān)鍵——因為你要判斷一個格子到底是數(shù)字、字符串還是日期不能一律當(dāng)字符串處理。JDBC 在這里有兩個用途一是生成腳本前先連庫校驗表結(jié)構(gòu)字段名、類型對不對二是小批量數(shù)據(jù)可以直接走PreparedStatement寫入省去生成腳本再手動執(zhí)行的步驟。常見做法是大批量走「生成 .sql 文件 命令行執(zhí)行」小批量或需要即時反饋的走 JDBC 直連。模板引擎的選擇上簡單場景用字符串拼接加占位符就夠了復(fù)雜場景多表、條件字段可以上 FreeMarker 或 Velocity。我一般建議先用最樸素的String.format或StringBuilder因為 SQL 生成的可讀性比「優(yōu)雅」重要——出問題時你得能一眼看出哪條語句拼錯了。2.2 一次完整的生成流程拆解整個流程可以拆成五步每一步都有對應(yīng)的代碼落點(diǎn)讀取 Excel 文件定位 sheet 和表頭行逐行讀取單元格按列映射到字段對每個值做類型轉(zhuǎn)換和轉(zhuǎn)義重點(diǎn)字符串單引號、日期格式、NULL套用 INSERT 模板生成 SQL 語句輸出到文件或通過 JDBC 執(zhí)行先看讀取和映射的核心代碼// 讀取 Excel 并映射為字段-值對 public ListMapString, String readExcel(String filePath, int headerRow) throws IOException { ListMapString, String rows new ArrayList(); try (InputStream is new FileInputStream(filePath); Workbook workbook WorkbookFactory.create(is)) { // 自動識別 xls/xlsx Sheet sheet workbook.getSheetAt(0); Row header sheet.getRow(headerRow); ListString columns new ArrayList(); for (Cell cell : header) { columns.add(cell.getStringCellValue().trim()); // 表頭即字段名 } // 從表頭下一行開始讀數(shù)據(jù) for (int i headerRow 1; i sheet.getLastRowNum(); i) { Row row sheet.getRow(i); if (row null) continue; MapString, String record new LinkedHashMap(); for (int j 0; j columns.size(); j) { Cell cell row.getCell(j); record.put(columns.get(j), getCellValue(cell)); // 統(tǒng)一轉(zhuǎn)字符串 } rows.add(record); } } return rows; }這段代碼的關(guān)鍵點(diǎn)WorkbookFactory.create能自動判斷文件格式省去手動區(qū)分 HSSF 和 XSSF用LinkedHashMap保證字段順序和 Excel 列順序一致生成 SQL 時字段和值的順序不會錯位表頭直接當(dāng)字段名意味著 Excel 第一行必須寫數(shù)據(jù)庫真實(shí)字段名這是約定也是約束。getCellValue是容易翻車的地方單獨(dú)拎出來// 單元格取值處理數(shù)字、日期、布爾、公式等類型 private String getCellValue(Cell cell) { if (cell null) return null; switch (cell.getCellType()) { case STRING: return cell.getStringCellValue().trim(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // 日期統(tǒng)一轉(zhuǎn)成 yyyy-MM-dd HH:mm:ss return new SimpleDateFormat(yyyy-MM-dd HH:mm:ss) .format(cell.getDateCellValue()); } // 避免 1.0 這種科學(xué)計數(shù)法尾巴 double d cell.getNumericCellValue(); if (d Math.floor(d)) return String.valueOf((long) d); return String.valueOf(d); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); // 或按需取計算結(jié)果 default: return null; } }參數(shù)說明DateUtil.isCellDateFormatted判斷是否是日期格式這一步不能省否則日期會變成一串?dāng)?shù)字序列號數(shù)字處理里判斷d Math.floor(d)是為了把100.0還原成100不然生成的 SQL 里 ID 字段會變成浮點(diǎn)數(shù)插入整型列直接報錯。2.3 生成 SQL 時的轉(zhuǎn)義與模板拿到字段-值對之后生成 INSERT 語句// 根據(jù)字段映射生成 INSERT 語句 public String buildInsert(String tableName, MapString, String record) { StringBuilder cols new StringBuilder(); StringBuilder vals new StringBuilder(); for (Map.EntryString, String e : record.entrySet()) { cols.append(e.getKey()).append(,); vals.append(toSqlValue(e.getValue())).append(,); } cols.setLength(cols.length() - 1); // 去掉末尾逗號 vals.setLength(vals.length() - 1); return String.format(INSERT INTO %s (%s) VALUES (%s);, tableName, cols, vals); } // 值轉(zhuǎn) SQL 字面量字符串加引號并轉(zhuǎn)義null 直接寫 NULL private String toSqlValue(String value) { if (value null || value.isEmpty()) return NULL; // 單引號轉(zhuǎn)義防止 SQL 注入和語法錯誤 return value.replace(, ) ; }邏輯說明toSqlValue里把單引號替換成兩個單引號這是 SQL 標(biāo)準(zhǔn)轉(zhuǎn)義方式能避免「OBrien」這類數(shù)據(jù)把語句截斷?？兆址?NULL 處理是個策略選擇——如果你的表不允許 NULL就得改成寫這個要按實(shí)際表結(jié)構(gòu)定。提示如果字段里有純數(shù)字但數(shù)據(jù)庫列是 varchar上面的寫法會帶上引號沒問題但如果列是 int 而 Excel 里混進(jìn)了空值NULL 是合法的空字符串就會報錯。類型邊界一定要提前對齊。3. 把工具跑起來環(huán)境、依賴與 JDBC 直連配置3.1 依賴引入與最小可運(yùn)行工程Maven 項目里引入 POI 和數(shù)據(jù)庫驅(qū)動即可。POI 建議用 5.x 版本對.xlsx支持更完整dependencies !-- Excel 解析 -- dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version5.2.3/version /dependency !-- JDBC 驅(qū)動按實(shí)際數(shù)據(jù)庫替換 -- dependency groupIdcom.mysql/groupId artifactIdmysql-connector-j/artifactId version8.0.33/version /dependency /dependencies參數(shù)說明poi-ooxml已經(jīng)傳遞依賴了poi核心包不用重復(fù)引驅(qū)動版本要和數(shù)據(jù)庫服務(wù)端匹配MySQL 8 用com.mysql.cj.jdbc.Driver5.x 用com.mysql.jdbc.Driver寫錯會報ClassNotFoundException。3.2 JDBC 連接與批量寫入如果選擇直連寫入而不是生成腳本核心是用PreparedStatement加批處理// JDBC 批量寫入避免逐條提交 public void batchInsert(ListMapString, String rows) throws SQLException { String sql INSERT INTO config_table (id, name, create_time) VALUES (?, ?, ?); try (Connection conn DriverManager.getConnection(URL, USER, PWD); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); // 關(guān)閉自動提交 for (MapString, String row : rows) { ps.setLong(1, Long.parseLong(row.get(id))); ps.setString(2, row.get(name)); ps.setTimestamp(3, Timestamp.valueOf(row.get(create_time))); ps.addBatch(); // 加入批次 } ps.executeBatch(); // 一次性提交 conn.commit(); } }邏輯說明setAutoCommit(false)配合executeBatch能把上千條插入合并成一次網(wǎng)絡(luò)往返性能差距在數(shù)據(jù)量大時非常明顯。addBatch只是攢著executeBatch才真正發(fā)送。注意Timestamp.valueOf要求字符串格式嚴(yán)格是yyyy-MM-dd HH:mm:ss和前面 Excel 日期轉(zhuǎn)換的格式必須一致否則拋IllegalArgumentException。3.3 生成腳本 vs JDBC 直連怎么選維度生成 .sql 文件JDBC 直連寫入適用數(shù)據(jù)量大批量、可分批執(zhí)行中小批量、即時反饋可審查性高腳本可人工檢查低直接進(jìn)庫回滾難度低腳本可留存高需事務(wù)控制環(huán)境依賴只需文件需網(wǎng)絡(luò)和驅(qū)動典型場景生產(chǎn)初始化、交付腳本測試數(shù)據(jù)、臨時導(dǎo)入我一般建議生產(chǎn)環(huán)境走生成腳本先人工過一遍再執(zhí)行測試環(huán)境走 JDBC 直連圖快。兩者不是互斥的同一個工具里可以都實(shí)現(xiàn)用參數(shù)切換。4. 避坑與排查那些讓腳本跑不通的細(xì)節(jié)4.1 日期變成 45000 這種數(shù)字現(xiàn)象Excel 里明明是2024-01-01生成的 SQL 里卻是45292。原因POI 把日期存為數(shù)字序列號如果沒調(diào)DateUtil.isCellDateFormatted判斷就會按普通數(shù)字取值。解決在getCellValue里先判斷日期格式用cell.getDateCellValue()取值再格式化。另外注意 Excel 單元格格式本身要是「日期」類型如果只是手動輸入了看起來像日期的文本POI 會當(dāng)字符串處理這種反而不會出問題。4.2 數(shù)字精度丟失或多了小數(shù)點(diǎn)現(xiàn)象ID 是10000000000000001生成后變成1.0E16或10000000000000000。原因Excel 對超過 15 位的數(shù)字本身就會丟精度POI 讀出來是 double再轉(zhuǎn)字符串就變形了。解決長數(shù)字如訂單號、身份證號在 Excel 里必須存成文本格式或者讀取時用DataFormatter按顯示值取。這是 Excel 的鍋不是工具的鍋但排查時容易誤判。4.3 表頭字段名和數(shù)據(jù)庫對不上現(xiàn)象生成的 SQL 執(zhí)行報Unknown column xxx。原因Excel 表頭寫了中文名或別名和數(shù)據(jù)庫真實(shí)字段名不一致。解決約定表頭必須寫真實(shí)字段名或者在工具里加一層字段映射配置Excel 列名 → 數(shù)據(jù)庫字段名。我傾向于加映射表因為業(yè)務(wù)同學(xué)填 Excel 時更愿意寫中文。4.4 空行和合并單元格導(dǎo)致錯位現(xiàn)象生成的 SQL 里某些行字段值整體偏移或者多出空 INSERT。原因Excel 里有空行、合并單元格getLastRowNum會把空行也算進(jìn)去getCell遇到合并單元格只有左上角有值。解決讀取時跳過row null和所有單元格都為空的行合并單元格場景建議在 Excel 側(cè)拆開工具側(cè)處理合并單元格成本高且容易出錯。4.5 批量執(zhí)行時事務(wù)太大撐爆內(nèi)存現(xiàn)象JDBC 批量寫入幾萬條時 OOM 或超時。原因一次性addBatch太多或者沒分批提交。解決每 500 到 1000 條執(zhí)行一次executeBatch并commit然后清空批次。這個閾值按數(shù)據(jù)庫和網(wǎng)絡(luò)情況調(diào)沒有萬能值。5. 進(jìn)階技巧用 DataFormatter 兜底與生成前的結(jié)構(gòu)校驗5.1 用 DataFormatter 拿到「所見即所得」的值前面getCellValue是按類型手動轉(zhuǎn)換遇到復(fù)雜格式百分比、貨幣、自定義格式還是會翻車。更穩(wěn)的做法是用 POI 的DataFormatter它按單元格顯示格式輸出字符串// 用 DataFormatter 按顯示格式取值兜底復(fù)雜格式 DataFormatter formatter new DataFormatter(); FormulaEvaluator evaluator workbook.getCreationHelper().createFormulaEvaluator(); for (int j 0; j columns.size(); j) { Cell cell row.getCell(j); // 第二個參數(shù)傳 evaluator公式單元格也能取到計算結(jié)果 String value formatter.formatCellValue(cell, evaluator); record.put(columns.get(j), value.trim()); }邏輯說明formatCellValue會尊重單元格的數(shù)字格式日期就是日期字符串百分比就是50%不會出現(xiàn)序列號。傳evaluator是為了讓公式單元格返回計算結(jié)果而不是公式本身。代價是它輸出的是「顯示值」如果 Excel 列寬不夠顯示成####取出來也可能是異常值所以列寬要正常。我一般會把這個方法和手動轉(zhuǎn)換結(jié)合先用DataFormatter取再對日期和長數(shù)字做二次校正。純手動轉(zhuǎn)換適合格式規(guī)整的數(shù)據(jù)DataFormatter適合格式五花八門的業(yè)務(wù)表格。5.2 生成前先校驗表結(jié)構(gòu)與其等 SQL 執(zhí)行報錯不如生成前先連庫查一次表結(jié)構(gòu)把 Excel 表頭和數(shù)據(jù)庫字段做比對// 查詢目標(biāo)表字段和 Excel 表頭比對 public SetString getTableColumns(Connection conn, String tableName) throws SQLException { SetString columns new HashSet(); DatabaseMetaData meta conn.getMetaData(); try (ResultSet rs meta.getColumns(null, null, tableName, null)) { while (rs.next()) { columns.add(rs.getString(COLUMN_NAME).toLowerCase()); } } return columns; }拿到字段集合后遍歷 Excel 表頭凡是數(shù)據(jù)庫里沒有的字段直接報出來讓人去改 Excel而不是生成一堆注定失敗的 SQL。這一步能省掉大量「執(zhí)行到一半報錯、回滾、改、重來」的循環(huán)。5.3 一個我踩過的坑有次給一個配置表導(dǎo)數(shù)據(jù)Excel 里有個字段叫status值是1和0我按字符串處理生成了1和0結(jié)果數(shù)據(jù)庫列是 tinyintMySQL 隱式轉(zhuǎn)換居然沒報錯數(shù)據(jù)也進(jìn)去了。但后來另一個字段amount是 decimalExcel 里填了100.50我按字符串生成100.50插入時精度被截成了100查了半天才發(fā)現(xiàn)是引號導(dǎo)致類型沒對上。從那以后我每次生成腳本前都會強(qiáng)制走一遍「字段類型比對」把數(shù)據(jù)庫列類型和 Excel 值的形態(tài)對一遍數(shù)字列不加引號字符串列加引號日期列用標(biāo)準(zhǔn)格式。這個習(xí)慣幫我省了至少三次線上排查。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取 Excel批量生成SQL腳本工具：POI+JDBC實(shí)現(xiàn)數(shù)據(jù)遷移提效
簡介這是一份面向Java開發(fā)者與數(shù)據(jù)庫運(yùn)維人員的數(shù)據(jù)處理工具源碼包用于將Excel表格數(shù)據(jù)自動解析并生成可執(zhí)行的SQL腳本再通過JDBC連接寫入目標(biāo)數(shù)據(jù)庫適用于數(shù)據(jù)遷移、批量導(dǎo)入與數(shù)據(jù)分析等場景。壓縮包共56個文件約24.48MB包含21個jar依賴、20個class與11個java源碼另有classpath配置、project工程文件、fatjar打包產(chǎn)物及一份xls測試數(shù)據(jù)模板結(jié)構(gòu)完整便于直接運(yùn)行與二次開發(fā)。資源核心圍繞Apache POI解析Excel、構(gòu)造INSERT等SQL語句、配置數(shù)據(jù)庫驅(qū)動并管理連接執(zhí)行源碼可幫助讀者理解從表格字段映射到語句生成、再到JDBC執(zhí)行與異常處理的完整鏈路。目前已有979人學(xué)習(xí)下載適合希望掌握Excel到數(shù)據(jù)庫自動化轉(zhuǎn)換思路的開發(fā)者參考借鑒。1. 從手工拼 SQL 到批量生成一個被低估的提效工具做數(shù)據(jù)遷移或者后臺初始化的時候最煩的不是寫 SQL而是把 Excel 里幾百上千行配置數(shù)據(jù)一行行拼成 INSERT 語句。字段一多引號、逗號、NULL 值、日期格式隨便哪個地方手滑就是一條報錯。更別提產(chǎn)品經(jīng)理隔三差五丟過來一個「補(bǔ)充數(shù)據(jù).xlsx」你還得重新對一遍字段順序。我最近拆的一個小工具就是干這件事的讀取 Excel按模板生成可直接執(zhí)行的 SQL 腳本同時支持通過 JDBC 連接數(shù)據(jù)庫做校驗或直接寫入。它解決的核心問題很具體——把「人肉拼 SQL」變成「配置一次、批量產(chǎn)出」。適合誰做數(shù)據(jù)初始化、配置表導(dǎo)入、測試數(shù)據(jù)構(gòu)造的后端和運(yùn)維同學(xué)尤其是那種表結(jié)構(gòu)固定、數(shù)據(jù)來源是 Excel 的場景。下面按「它怎么工作 → 怎么跑起來 → 坑在哪 → 怎么用得更順」的順序拆開講。2. 解析 Excel 生成 SQL 的底層邏輯POI、JDBC 與模板三件套2.1 為什么是 POI JDBC 這套組合Excel 解析這塊Java 生態(tài)里最穩(wěn)的還是 Apache POI。它同時支持.xlsHSSF和.xlsxXSSF能拿到單元格的類型、格式、公式結(jié)果這對生成 SQL 很關(guān)鍵——因為你要判斷一個格子到底是數(shù)字、字符串還是日期不能一律當(dāng)字符串處理。JDBC 在這里有兩個用途一是生成腳本前先連庫校驗表結(jié)構(gòu)字段名、類型對不對二是小批量數(shù)據(jù)可以直接走PreparedStatement寫入省去生成腳本再手動執(zhí)行的步驟。常見做法是大批量走「生成 .sql 文件 命令行執(zhí)行」小批量或需要即時反饋的走 JDBC 直連。模板引擎的選擇上簡單場景用字符串拼接加占位符就夠了復(fù)雜場景多表、條件字段可以上 FreeMarker 或 Velocity。我一般建議先用最樸素的String.format或StringBuilder因為 SQL 生成的可讀性比「優(yōu)雅」重要——出問題時你得能一眼看出哪條語句拼錯了。2.2 一次完整的生成流程拆解整個流程可以拆成五步每一步都有對應(yīng)的代碼落點(diǎn)讀取 Excel 文件定位 sheet 和表頭行逐行讀取單元格按列映射到字段對每個值做類型轉(zhuǎn)換和轉(zhuǎn)義重點(diǎn)字符串單引號、日期格式、NULL套用 INSERT 模板生成 SQL 語句輸出到文件或通過 JDBC 執(zhí)行先看讀取和映射的核心代碼// 讀取 Excel 并映射為字段-值對 public ListMapString, String readExcel(String filePath, int headerRow) throws IOException { ListMapString, String rows new ArrayList(); try (InputStream is new FileInputStream(filePath); Workbook workbook WorkbookFactory.create(is)) { // 自動識別 xls/xlsx Sheet sheet workbook.getSheetAt(0); Row header sheet.getRow(headerRow); ListString columns new ArrayList(); for (Cell cell : header) { columns.add(cell.getStringCellValue().trim()); // 表頭即字段名 } // 從表頭下一行開始讀數(shù)據(jù) for (int i headerRow 1; i sheet.getLastRowNum(); i) { Row row sheet.getRow(i); if (row null) continue; MapString, String record new LinkedHashMap(); for (int j 0; j columns.size(); j) { Cell cell row.getCell(j); record.put(columns.get(j), getCellValue(cell)); // 統(tǒng)一轉(zhuǎn)字符串 } rows.add(record); } } return rows; }這段代碼的關(guān)鍵點(diǎn)WorkbookFactory.create能自動判斷文件格式省去手動區(qū)分 HSSF 和 XSSF用LinkedHashMap保證字段順序和 Excel 列順序一致生成 SQL 時字段和值的順序不會錯位表頭直接當(dāng)字段名意味著 Excel 第一行必須寫數(shù)據(jù)庫真實(shí)字段名這是約定也是約束。getCellValue是容易翻車的地方單獨(dú)拎出來// 單元格取值處理數(shù)字、日期、布爾、公式等類型 private String getCellValue(Cell cell) { if (cell null) return null; switch (cell.getCellType()) { case STRING: return cell.getStringCellValue().trim(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // 日期統(tǒng)一轉(zhuǎn)成 yyyy-MM-dd HH:mm:ss return new SimpleDateFormat(yyyy-MM-dd HH:mm:ss) .format(cell.getDateCellValue()); } // 避免 1.0 這種科學(xué)計數(shù)法尾巴 double d cell.getNumericCellValue(); if (d Math.floor(d)) return String.valueOf((long) d); return String.valueOf(d); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); // 或按需取計算結(jié)果 default: return null; } }參數(shù)說明DateUtil.isCellDateFormatted判斷是否是日期格式這一步不能省否則日期會變成一串?dāng)?shù)字序列號數(shù)字處理里判斷d Math.floor(d)是為了把100.0還原成100不然生成的 SQL 里 ID 字段會變成浮點(diǎn)數(shù)插入整型列直接報錯。2.3 生成 SQL 時的轉(zhuǎn)義與模板拿到字段-值對之后生成 INSERT 語句// 根據(jù)字段映射生成 INSERT 語句 public String buildInsert(String tableName, MapString, String record) { StringBuilder cols new StringBuilder(); StringBuilder vals new StringBuilder(); for (Map.EntryString, String e : record.entrySet()) { cols.append(e.getKey()).append(,); vals.append(toSqlValue(e.getValue())).append(,); } cols.setLength(cols.length() - 1); // 去掉末尾逗號 vals.setLength(vals.length() - 1); return String.format(INSERT INTO %s (%s) VALUES (%s);, tableName, cols, vals); } // 值轉(zhuǎn) SQL 字面量字符串加引號并轉(zhuǎn)義null 直接寫 NULL private String toSqlValue(String value) { if (value null || value.isEmpty()) return NULL; // 單引號轉(zhuǎn)義防止 SQL 注入和語法錯誤 return value.replace(, ) ; }邏輯說明toSqlValue里把單引號替換成兩個單引號這是 SQL 標(biāo)準(zhǔn)轉(zhuǎn)義方式能避免「OBrien」這類數(shù)據(jù)把語句截斷?？兆址?NULL 處理是個策略選擇——如果你的表不允許 NULL就得改成寫這個要按實(shí)際表結(jié)構(gòu)定。提示如果字段里有純數(shù)字但數(shù)據(jù)庫列是 varchar上面的寫法會帶上引號沒問題但如果列是 int 而 Excel 里混進(jìn)了空值NULL 是合法的空字符串就會報錯。類型邊界一定要提前對齊。3. 把工具跑起來環(huán)境、依賴與 JDBC 直連配置3.1 依賴引入與最小可運(yùn)行工程Maven 項目里引入 POI 和數(shù)據(jù)庫驅(qū)動即可。POI 建議用 5.x 版本對.xlsx支持更完整dependencies !-- Excel 解析 -- dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version5.2.3/version /dependency !-- JDBC 驅(qū)動按實(shí)際數(shù)據(jù)庫替換 -- dependency groupIdcom.mysql/groupId artifactIdmysql-connector-j/artifactId version8.0.33/version /dependency /dependencies參數(shù)說明poi-ooxml已經(jīng)傳遞依賴了poi核心包不用重復(fù)引驅(qū)動版本要和數(shù)據(jù)庫服務(wù)端匹配MySQL 8 用com.mysql.cj.jdbc.Driver5.x 用com.mysql.jdbc.Driver寫錯會報ClassNotFoundException。3.2 JDBC 連接與批量寫入如果選擇直連寫入而不是生成腳本核心是用PreparedStatement加批處理// JDBC 批量寫入避免逐條提交 public void batchInsert(ListMapString, String rows) throws SQLException { String sql INSERT INTO config_table (id, name, create_time) VALUES (?, ?, ?); try (Connection conn DriverManager.getConnection(URL, USER, PWD); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); // 關(guān)閉自動提交 for (MapString, String row : rows) { ps.setLong(1, Long.parseLong(row.get(id))); ps.setString(2, row.get(name)); ps.setTimestamp(3, Timestamp.valueOf(row.get(create_time))); ps.addBatch(); // 加入批次 } ps.executeBatch(); // 一次性提交 conn.commit(); } }邏輯說明setAutoCommit(false)配合executeBatch能把上千條插入合并成一次網(wǎng)絡(luò)往返性能差距在數(shù)據(jù)量大時非常明顯。addBatch只是攢著executeBatch才真正發(fā)送。注意Timestamp.valueOf要求字符串格式嚴(yán)格是yyyy-MM-dd HH:mm:ss和前面 Excel 日期轉(zhuǎn)換的格式必須一致否則拋IllegalArgumentException。3.3 生成腳本 vs JDBC 直連怎么選維度生成 .sql 文件JDBC 直連寫入適用數(shù)據(jù)量大批量、可分批執(zhí)行中小批量、即時反饋可審查性高腳本可人工檢查低直接進(jìn)庫回滾難度低腳本可留存高需事務(wù)控制環(huán)境依賴只需文件需網(wǎng)絡(luò)和驅(qū)動典型場景生產(chǎn)初始化、交付腳本測試數(shù)據(jù)、臨時導(dǎo)入我一般建議生產(chǎn)環(huán)境走生成腳本先人工過一遍再執(zhí)行測試環(huán)境走 JDBC 直連圖快。兩者不是互斥的同一個工具里可以都實(shí)現(xiàn)用參數(shù)切換。4. 避坑與排查那些讓腳本跑不通的細(xì)節(jié)4.1 日期變成 45000 這種數(shù)字現(xiàn)象Excel 里明明是2024-01-01生成的 SQL 里卻是45292。原因POI 把日期存為數(shù)字序列號如果沒調(diào)DateUtil.isCellDateFormatted判斷就會按普通數(shù)字取值。解決在getCellValue里先判斷日期格式用cell.getDateCellValue()取值再格式化。另外注意 Excel 單元格格式本身要是「日期」類型如果只是手動輸入了看起來像日期的文本POI 會當(dāng)字符串處理這種反而不會出問題。4.2 數(shù)字精度丟失或多了小數(shù)點(diǎn)現(xiàn)象ID 是10000000000000001生成后變成1.0E16或10000000000000000。原因Excel 對超過 15 位的數(shù)字本身就會丟精度POI 讀出來是 double再轉(zhuǎn)字符串就變形了。解決長數(shù)字如訂單號、身份證號在 Excel 里必須存成文本格式或者讀取時用DataFormatter按顯示值取。這是 Excel 的鍋不是工具的鍋但排查時容易誤判。4.3 表頭字段名和數(shù)據(jù)庫對不上現(xiàn)象生成的 SQL 執(zhí)行報Unknown column xxx。原因Excel 表頭寫了中文名或別名和數(shù)據(jù)庫真實(shí)字段名不一致。解決約定表頭必須寫真實(shí)字段名或者在工具里加一層字段映射配置Excel 列名 → 數(shù)據(jù)庫字段名。我傾向于加映射表因為業(yè)務(wù)同學(xué)填 Excel 時更愿意寫中文。4.4 空行和合并單元格導(dǎo)致錯位現(xiàn)象生成的 SQL 里某些行字段值整體偏移或者多出空 INSERT。原因Excel 里有空行、合并單元格getLastRowNum會把空行也算進(jìn)去getCell遇到合并單元格只有左上角有值。解決讀取時跳過row null和所有單元格都為空的行合并單元格場景建議在 Excel 側(cè)拆開工具側(cè)處理合并單元格成本高且容易出錯。4.5 批量執(zhí)行時事務(wù)太大撐爆內(nèi)存現(xiàn)象JDBC 批量寫入幾萬條時 OOM 或超時。原因一次性addBatch太多或者沒分批提交。解決每 500 到 1000 條執(zhí)行一次executeBatch并commit然后清空批次。這個閾值按數(shù)據(jù)庫和網(wǎng)絡(luò)情況調(diào)沒有萬能值。5. 進(jìn)階技巧用 DataFormatter 兜底與生成前的結(jié)構(gòu)校驗5.1 用 DataFormatter 拿到「所見即所得」的值前面getCellValue是按類型手動轉(zhuǎn)換遇到復(fù)雜格式百分比、貨幣、自定義格式還是會翻車。更穩(wěn)的做法是用 POI 的DataFormatter它按單元格顯示格式輸出字符串// 用 DataFormatter 按顯示格式取值兜底復(fù)雜格式 DataFormatter formatter new DataFormatter(); FormulaEvaluator evaluator workbook.getCreationHelper().createFormulaEvaluator(); for (int j 0; j columns.size(); j) { Cell cell row.getCell(j); // 第二個參數(shù)傳 evaluator公式單元格也能取到計算結(jié)果 String value formatter.formatCellValue(cell, evaluator); record.put(columns.get(j), value.trim()); }邏輯說明formatCellValue會尊重單元格的數(shù)字格式日期就是日期字符串百分比就是50%不會出現(xiàn)序列號。傳evaluator是為了讓公式單元格返回計算結(jié)果而不是公式本身。代價是它輸出的是「顯示值」如果 Excel 列寬不夠顯示成####取出來也可能是異常值所以列寬要正常。我一般會把這個方法和手動轉(zhuǎn)換結(jié)合先用DataFormatter取再對日期和長數(shù)字做二次校正。純手動轉(zhuǎn)換適合格式規(guī)整的數(shù)據(jù)DataFormatter適合格式五花八門的業(yè)務(wù)表格。5.2 生成前先校驗表結(jié)構(gòu)與其等 SQL 執(zhí)行報錯不如生成前先連庫查一次表結(jié)構(gòu)把 Excel 表頭和數(shù)據(jù)庫字段做比對// 查詢目標(biāo)表字段和 Excel 表頭比對 public SetString getTableColumns(Connection conn, String tableName) throws SQLException { SetString columns new HashSet(); DatabaseMetaData meta conn.getMetaData(); try (ResultSet rs meta.getColumns(null, null, tableName, null)) { while (rs.next()) { columns.add(rs.getString(COLUMN_NAME).toLowerCase()); } } return columns; }拿到字段集合后遍歷 Excel 表頭凡是數(shù)據(jù)庫里沒有的字段直接報出來讓人去改 Excel而不是生成一堆注定失敗的 SQL。這一步能省掉大量「執(zhí)行到一半報錯、回滾、改、重來」的循環(huán)。5.3 一個我踩過的坑有次給一個配置表導(dǎo)數(shù)據(jù)Excel 里有個字段叫status值是1和0我按字符串處理生成了1和0結(jié)果數(shù)據(jù)庫列是 tinyintMySQL 隱式轉(zhuǎn)換居然沒報錯數(shù)據(jù)也進(jìn)去了。但后來另一個字段amount是 decimalExcel 里填了100.50我按字符串生成100.50插入時精度被截成了100查了半天才發(fā)現(xiàn)是引號導(dǎo)致類型沒對上。從那以后我每次生成腳本前都會強(qiáng)制走一遍「字段類型比對」把數(shù)據(jù)庫列類型和 Excel 值的形態(tài)對一遍數(shù)字列不加引號字符串列加引號日期列用標(biāo)準(zhǔn)格式。這個習(xí)慣幫我省了至少三次線上排查。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取