读取Excel生成Sql脚本：POI与EasyExcel解析及JDBC批量插入实战

简介这是一份面向Java开发者与数据库运维人员的数据处理工具源码包用于将Excel表格数据自动解析并生成可执行的SQL语句再通过JDBC连接写入目标数据库适用于数据迁移、批量导入与数据分析等场景。压缩包共56个文件约24.48MB以21个jar依赖、20个class编译文件、11个java源码为主另含classpath配置、xls测试模板与fatjar打包文件结构完整便于直接运行或二次开发。资源核心围绕Apache POI读取Excel、构造INSERT/UPDATE等语句、配置数据库驱动并执行连接展开源码可帮助读者理解从工作簿解析、字段映射到SQL执行与连接关闭的完整链路demo与测试模板则方便快速验证效果。目前已有979人学习下载适合具备一定Java与数据库基础、希望提升数据操作效率的开发者参考借鉴。1. 读取Excel生成Sql脚本从手工拼SQL到一键导出你有没有遇到过这种场景业务方甩过来一个 Excel里面几十列、上千行数据要求你“尽快入库”。你打开一看字段名是中文的日期格式五花八门还有合并单元格和隐藏 Sheet。手工拼 INSERT 语句拼到第十行就开始怀疑人生。这份“读取Excel生成Sql脚本”的资源解决的就是这个痛点——把 Excel 解析、字段映射、类型推断、SQL 生成串成一条可复用的流水线。它适合经常做数据迁移、批量导入、测试数据构造的后端和数据分析从业者。核心思路不复杂用 POI 或 EasyExcel 读单元格按列名映射到目标表字段再根据 JDBC 元数据推断类型最后输出可执行的 INSERT 或批量脚本。下面把我拆过的实现细节和踩过的坑一次讲清。2. 解析Excel生成脚本POI与EasyExcel的选型与读取实现2.1 为什么选型要先看内存模型解析 Excel 生成 SQL 脚本第一步不是写 SQL而是选对读取库。常见做法是 Apache POI 和阿里 EasyExcel 二选一。POI 的XSSFWorkbook会把整个文件加载进内存一个 10MB 的 xlsx 可能膨胀到 100MB 以上行数过万就容易 OOM。EasyExcel 基于 SAX 事件模型逐行读取内存占用稳定在几十 MB适合大文件。但 EasyExcel 对公式单元格、合并单元格的读取支持不如 POI 直接需要额外处理。我一般这样决策文件小于 5000 行且格式复杂多级表头、公式用 POI文件大或格式规整用 EasyExcel。下面给出两种读取方式的核心代码。// 方式一POI 读取适合小文件、复杂格式 import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.util.*; public class PoiExcelReader { public static ListMapString, String read(String filePath) throws Exception { ListMapString, String rows new ArrayList(); try (FileInputStream fis new FileInputStream(filePath); Workbook workbook new XSSFWorkbook(fis)) { Sheet sheet workbook.getSheetAt(0); // 默认读第一个 Sheet Row headerRow sheet.getRow(0); ListString headers new ArrayList(); for (Cell cell : headerRow) { headers.add(cell.getStringCellValue().trim()); } // 从第二行开始读数据 for (int i 1; i sheet.getLastRowNum(); i) { Row row sheet.getRow(i); if (row null) continue; MapString, String rowMap new LinkedHashMap(); for (int j 0; j headers.size(); j) { Cell cell row.getCell(j); rowMap.put(headers.get(j), getCellValue(cell)); } rows.add(rowMap); } } return rows; } // 统一单元格取值避免类型转换异常 private static String getCellValue(Cell cell) { if (cell null) return ; switch (cell.getCellType()) { case STRING: return cell.getStringCellValue().trim(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getLocalDateTimeCellValue().toString(); } // 避免科学计数法 return new java.math.BigDecimal(cell.getNumericCellValue()).toPlainString(); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); default: return ; } } }这段代码的关键点有三个。第一getCellValue里对 NUMERIC 类型做了日期判断和 BigDecimal 转换否则手机号、身份证号会变成1.2345678901E10这种科学计数法入库后全是脏数据。第二用LinkedHashMap保持列顺序方便后续按位置映射。第三sheet.getLastRowNum()返回的是最后一行索引空行要跳过。参数方面filePath建议用绝对路径避免相对路径在不同运行目录下找不到文件。// 方式二EasyExcel 读取适合大文件、流式处理 import com.alibaba.excel.EasyExcel; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.read.listener.ReadListener; import java.util.*; public class EasyExcelReader { public static ListMapInteger, String read(String filePath) { ListMapInteger, String rows new ArrayList(); EasyExcel.read(filePath, new ReadListenerMapInteger, String() { Override public void invoke(MapInteger, String data, AnalysisContext context) { rows.add(data); // 逐行回调内存可控 } Override public void doAfterAllAnalysed(AnalysisContext context) { // 读取完成后的收尾逻辑 } }).sheet(0).doRead(); // 指定 Sheet 索引 return rows; } }EasyExcel 的invoke是逐行回调data的 key 是列索引value 是单元格字符串。注意它默认不读表头如果需要表头信息用headRowNumber(1)指定。实际项目中我通常把两种方式封装成统一接口根据文件大小自动切换。这里有个容易翻车的点EasyExcel 读到的数字默认也是字符串但日期格式可能变成2024-01-01 00:00:00这种带时分秒的需要在生成 SQL 时按目标列类型截断。2.2 字段映射与类型推断的落地做法读出来只是原料生成 SQL 还需要把 Excel 列名映射到数据库表字段。常见做法是维护一张映射配置表比如 Excel 列“用户姓名”对应user_name“注册时间”对应register_time。我一般用 YAML 或 JSON 存映射关系代码里加载成 Map。类型推断则依赖 JDBC 的ResultSetMetaData或DatabaseMetaData拿到目标表的列类型后把 Excel 字符串转成对应 Java 类型再拼 SQL。下面是一个映射配置和类型转换的示例。// 字段映射配置示例YAML 加载后转为 Map // excelColumn - dbColumn MapString, String columnMapping new LinkedHashMap(); columnMapping.put(用户姓名, user_name); columnMapping.put(手机号, phone); columnMapping.put(注册时间, register_time); columnMapping.put(余额, balance); // 根据数据库列类型转换值 public static String convertValue(String raw, String dbType) { if (raw null || raw.isEmpty()) return NULL; switch (dbType.toUpperCase()) { case VARCHAR: case TEXT: return raw.replace(, ) ; // 转义单引号 case INT: case BIGINT: return raw.replaceAll([^0-9-], ); // 去除非数字字符 case DECIMAL: return new java.math.BigDecimal(raw).toPlainString(); case DATETIME: case TIMESTAMP: return raw.replace(T, ) ; default: return raw ; } }这里有两个血泪经验。第一字符串拼接必须转义单引号否则遇到“OBrien”这种名字直接 SQL 语法错误。第二数值类型不要直接信任 Excel 里的值可能混入空格或单位符号用正则清洗一遍。类型推断的边界在于如果目标表还没建就需要根据 Excel 数据采样推断类型比如整列都是数字就推 INT 或 DECIMAL有日期格式就推 DATETIME其余推 VARCHAR。我一般会生成一份建表语句草稿让开发确认后再执行。3. JDBC连接数据库批量插入与事务控制的参数调优3.1 生成INSERT还是走JDBC批量执行“读取Excel生成Sql脚本”有两种落地形态一种是生成.sql文件交给 DBA 执行另一种是程序内通过 JDBC 直接批量插入。前者适合数据量不大、需要人工审核的场景后者适合自动化流水线。如果走 JDBC核心是PreparedStatement.addBatch()和executeBatch()配合事务控制。下面是一个批量插入的模板。import java.sql.*; public class JdbcBatchInserter { public static void batchInsert(ListMapString, String rows, MapString, String mapping, String tableName) throws SQLException { String url jdbc:mysql://localhost:3306/test_db?rewriteBatchedStatementstrue; String user root; String password your_password; // 构建 INSERT 语句 StringBuilder sql new StringBuilder(INSERT INTO tableName (); StringBuilder placeholders new StringBuilder(); for (String dbCol : mapping.values()) { sql.append(dbCol).append(,); placeholders.append(?,); } sql.setLength(sql.length() - 1); placeholders.setLength(placeholders.length() - 1); sql.append() VALUES ().append(placeholders).append()); try (Connection conn DriverManager.getConnection(url, user, password)) { conn.setAutoCommit(false); // 关闭自动提交 try (PreparedStatement ps conn.prepareStatement(sql.toString())) { int batchSize 500; // 每 500 条提交一次 int count 0; for (MapString, String row : rows) { int idx 1; for (String excelCol : mapping.keySet()) { ps.setString(idx, row.get(excelCol)); } ps.addBatch(); if (count % batchSize 0) { ps.executeBatch(); conn.commit(); ps.clearBatch(); } } ps.executeBatch(); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } } } }参数说明rewriteBatchedStatementstrue是 MySQL 驱动的重要参数开启后会把多条 INSERT 合并成一条网络包性能提升明显。batchSize设 500 到 1000 比较稳太大容易触发max_allowed_packet限制。事务方面每批提交一次避免长事务锁表。如果目标库是 Oracle批量参数和提交策略需要调整addBatch的行为也有差异。注意ps.setString对所有列都按字符串设置依赖数据库隐式转换严格模式下可能报错建议按类型用setInt、setBigDecimal等方法。3.2 连接池与超时参数怎么设直接DriverManager.getConnection只适合测试生产环境要用连接池。HikariCP 是常见选择关键参数有maximumPoolSize、connectionTimeout、idleTimeout。批量导入场景下连接数不用太多4 到 8 个足够因为瓶颈在磁盘 IO 和网络。connectionTimeout设 30 秒避免拿不到连接时无限等待。另外MySQL 的wait_timeout默认 8 小时长时间批量任务要注意连接被服务端断开可以开启autoReconnect或定期心跳。我一般会在批量任务前后各打一条日志记录开始时间、结束时间、成功条数和失败条数方便排查。# HikariCP 配置示例 spring: datasource: hikari: maximum-pool-size: 8 minimum-idle: 2 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000 connection-test-query: SELECT 1max-lifetime要小于数据库的wait_timeout否则连接被服务端关闭后池里还是死连接。connection-test-query在 MySQL 中可以用SELECT 1但 JDBC4 驱动支持isValid后可以不配。这些参数看着琐碎但批量任务跑一半断连往往就是这里没设对。4. 避坑与排查Excel解析和SQL生成中的五个常见问题4.1 科学计数法把手机号变成 1.23E10现象Excel 里手机号、身份证号读出来变成1.2345678901E10入库后长度不对。原因POI 把数字单元格按 double 读取超过 11 位自动转科学计数法。解决在getCellValue里用BigDecimal(cell.getNumericCellValue()).toPlainString()或者把 Excel 列预先设成文本格式。更稳妥的做法是读之前用DataFormatter它会按单元格显示格式返回值。4.2 日期格式千奇百怪导致入库失败现象2024/1/1、2024-01-01、20240101混在一列拼出的 SQL 执行报错。原因Excel 日期单元格底层是数字显示格式不同直接toString结果不可控。解决统一用DateUtil.isCellDateFormatted判断是日期就转成yyyy-MM-dd HH:mm:ss标准格式不是日期就按字符串处理。如果目标列是 DATETIME还要处理空值和0000-00-00这种非法日期。4.3 批量插入时内存溢出现象程序跑着跑着 OOM日志显示堆内存耗尽。原因一次性把所有行读进List或者addBatch后没clearBatch。解决用 EasyExcel 流式读取边读边写JDBC 批量每 500 条executeBatch后clearBatch。如果必须全量读给 JVM 加-Xmx参数但治标不治本。4.4 字段映射错位导致数据串列现象用户名跑到手机号字段余额跑到备注字段。原因Excel 列顺序和映射配置的keySet顺序不一致或者中间有空列。解决不要依赖列顺序用列名做 key 映射。如果 Excel 表头有重复列名先做去重或加后缀。读取时打印第一行数据的映射结果人工核对一遍再批量执行。4.5 事务回滚不彻底留下脏数据现象批量插入报错后部分数据已经入库部分没有。原因autoCommit没关或者executeBatch部分成功后异常没有整体回滚。解决conn.setAutoCommit(false)每批提交前记录批次号异常时rollback。如果用的是分布式事务或分库分表要考虑补偿机制。我一般会在目标表加一个batch_id字段失败后按批次删除重跑。5. 进阶技巧用JDBC元数据自动生成建表语句和校验脚本前面讲的都是“已知目标表结构”的情况。实际项目中经常遇到 Excel 先到、表还没建。这时候可以用 JDBC 的DatabaseMetaData反向生成建表语句或者根据 Excel 数据采样推断类型。下面这段代码演示如何读取目标库现有表的列信息用于校验 Excel 列是否匹配。import java.sql.*; public class MetaDataChecker { public static void printTableColumns(String url, String user, String password, String tableName) throws SQLException { try (Connection conn DriverManager.getConnection(url, user, password)) { DatabaseMetaData meta conn.getMetaData(); // 获取指定表的列信息 try (ResultSet rs meta.getColumns(null, null, tableName, null)) { System.out.println(列名\t类型\t长度\t是否可空); while (rs.next()) { String colName rs.getString(COLUMN_NAME); String typeName rs.getString(TYPE_NAME); int size rs.getInt(COLUMN_SIZE); String nullable rs.getString(IS_NULLABLE); System.out.printf(%s\t%s\t%d\t%s%n, colName, typeName, size, nullable); } } } } }拿到列信息后和 Excel 表头做交集比对缺失的列给默认值或报错。如果表不存在可以根据 Excel 前 100 行采样整列能转成数字且无小数推BIGINT有小数推DECIMAL(18,4)能解析成日期推DATETIME其余推VARCHAR(255)。生成的建表语句先输出到控制台人工确认后再执行。这个习惯帮我省过很多次后悔药——有次直接把“备注”列推成VARCHAR(255)结果实际数据有 800 多字插入时被截断后来改成TEXT才解决。从那以后我每次生成建表语句都强制走一遍采样校验把最大长度、空值比例、唯一性都打出来看一眼。希望帮到你。本文还有配套的精品资源点击获取