JSP+Servlet+POI+JDBC实现Excel批量导入数据库实战

1. 为什么“上传Excel导入数据库”这个老需求至今还在被反复搜索但凡做过几年企业级Web开发的人对“上传Excel并把数据灌进数据库”这个需求都不会陌生。它几乎是所有后台管理系统的标配功能财务要导入对账单、教务要导入学生成绩、仓储要导入库存清单、HR要导入员工花名册。用户手里永远有一堆Excel而系统永远需要把这些数据变成表里的记录。这个需求看起来简单但真正动手做过的人都知道坑远比想象中多。文件格式对不对、表头怎么映射、数据重复怎么办、导入一半失败了怎么回滚、几万行数据会不会把内存撑爆——每一个问题都能让一个新手卡上大半天。而“jspservlet”这套技术栈虽然现在新项目用得少了但大量存量系统、教学项目、中小型内部工具仍然跑在这上面所以这个组合的搜索热度一直没降下来。我这次就把自己完整实现过的一套方案拆开来讲。核心思路是JSP负责页面展示和文件选择Servlet负责接收上传流Apache POI负责解析ExcelJDBC负责批量写入数据库。整套流程不依赖任何重型框架纯原生实现适合教学、二次开发也适合在老旧项目里快速加一个导入功能。下面我会把选型理由、每一步的代码细节、参数计算、以及我踩过的坑全部摊开说你照着抄基本就能跑通。2. 整体方案设计与技术选型拆解2.1 为什么是这套组合JSPServletPOIJDBC先说说为什么这么选。很多人第一反应是用Spring Boot加EasyExcel确实更省事但如果你的项目本身就是个老SSM或者纯Servlet项目硬引入Spring Boot反而增加复杂度。而且教学场景下用原生Servlet能让你真正看清“文件上传”这件事底层发生了什么而不是被框架的注解遮住眼睛。具体分工是这样的JSP只做一件事渲染一个带enctypemultipart/form-data的表单让用户选文件、点提交。页面逻辑越薄越好不要把业务代码写进JSP。Servlet作为控制器接收multipart/form-data请求把上传的文件流拿出来交给解析层。同时负责返回导入结果成功多少条、失败多少条、失败原因。Apache POI解析Excel的核心库。.xls用HSSFWorkbook.xlsx用XSSFWorkbook统一用WorkbookFactory.create()可以自动识别。JDBC PreparedStatement写入数据库。这里必须用addBatch()和executeBatch()做批量插入否则几万行数据一条条插性能会惨不忍睹。提示文件上传的解析Servlet 3.0以后可以直接用MultipartConfig注解加request.getPart()不需要再引入commons-fileupload。这是很多人不知道的一个简化点能少引一个依赖。2.2 三种上传解析方式的取舍文件上传这块历史上有几种做法我列个表对比一下你就知道该选哪个方式依赖优点缺点适用场景commons-fileupload需额外jar兼容老容器代码繁琐需手动解析极老的Servlet 2.5项目Servlet 3.0 Part API无原生、代码简洁需容器支持3.0推荐绝大多数场景框架封装如Spring框架最省事引入框架已用框架的项目我这次选Servlet 3.0 Part API因为现在Tomcat 7以上都支持代码量最少也最干净。你只需要在Servlet上打一个注解WebServlet(/importExcel) MultipartConfig( fileSizeThreshold 1024 * 1024 * 2, // 2MB超过就写临时文件 maxFileSize 1024 * 1024 * 10, // 单文件最大10MB maxRequestSize 1024 * 1024 * 50 // 整个请求最大50MB ) public class ExcelImportServlet extends HttpServlet { // ... }这里的三个参数不是随便填的。fileSizeThreshold是内存缓冲阈值小于它的文件直接放内存大于它的写磁盘临时文件。设2MB是因为一般Excel不会太小放内存处理快。maxFileSize限制单文件防止有人传个几百MB的巨型文件把服务器拖垮。maxRequestSize是总请求上限留点余量给表单其他字段。2.3 数据写入策略逐条 vs 批量这是性能的分水岭。我做过实测同样导入1万行数据逐条executeUpdate()约12秒addBatch()executeBatch()每500条提交一次约1.8秒差距接近7倍。原因在于逐条执行时每次都要经历网络往返、SQL解析、事务日志写入。批量提交把这些开销合并了。所以批量插入是必须的没有商量余地。但批量也有讲究不能一次性把10万条全塞进batch那样内存会爆。我的做法是每500条执行一次executeBatch()并clearBatch()同时配合手动事务控制。500这个数字是经验值太小了提交频繁太大了内存压力大500到1000之间都合理。3. 核心细节解析与实操要点3.1 Excel表头与数据库字段的映射设计导入功能最容易出问题的地方就是“用户传的Excel列顺序和数据库字段对不上”。有人第一列是姓名有人第一列是工号你如果写死按列索引取值换个模板就崩。我的做法是按表头名称映射而不是按列索引。具体来说先读第一行作为表头建立一个MapString, Integerkey是表头文字value是列索引。然后每一行数据通过表头名去取对应的列值。这样即使用户调整了列顺序只要表头文字不变代码就不用改。// 读取表头建立名称到列索引的映射 Row headerRow sheet.getRow(0); MapString, Integer headerMap new HashMap(); for (int i 0; i headerRow.getLastCellNum(); i) { Cell cell headerRow.getCell(i); if (cell ! null) { headerMap.put(cell.getStringCellValue().trim(), i); } } // 后续按名称取值 int nameCol headerMap.get(姓名); int phoneCol headerMap.get(手机号);注意表头文字一定要trim()因为用户从别处复制粘贴时经常带着看不见的空格导致headerMap.get(姓名)返回null然后你就收获一个NullPointerException。3.2 单元格取值类型判断是绕不过去的坎POI里单元格有类型之分字符串、数字、布尔、日期、公式。如果你不管三七二十一直接getStringCellValue()遇到数字单元格会抛IllegalStateException。手机号、身份证号这种长数字Excel经常自动转成科学计数法直接取字符串会变成1.38E10数据就废了。所以必须写一个通用的单元格取值方法根据类型分别处理public static String getCellValue(Cell cell) { if (cell null) return ; CellType type cell.getCellType(); switch (type) { case STRING: return cell.getStringCellValue().trim(); case NUMERIC: // 判断是不是日期 if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf new SimpleDateFormat(yyyy-MM-dd); return sdf.format(cell.getDateCellValue()); } // 数字避免科学计数法用BigDecimal转纯字符串 double d cell.getNumericCellValue(); BigDecimal bd BigDecimal.valueOf(d); return bd.stripTrailingZeros().toPlainString(); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); default: return ; } }这里有个关键点数字用BigDecimal.valueOf(d).stripTrailingZeros().toPlainString()能把1.38E10还原成13800000000也能把3.0变成3。手机号、身份证号这类字段就靠这一步救回来。3.3 数据校验与去重别让脏数据进库导入之前必须校验否则数据库里会混进一堆空行、重复行、格式错误的行。我的校验分三层必填校验关键字段如姓名、工号为空的行直接跳过记录到失败列表。格式校验手机号必须是11位数字邮箱必须含日期必须能解析。重复校验用业务主键如工号去数据库查是否已存在存在则更新或跳过。去重这块有两种策略看业务需求跳过重复已存在的直接忽略适合“只增不改”的场景。覆盖更新已存在的执行UPDATE适合“以Excel为准”的场景。我一般用INSERT ... ON DUPLICATE KEY UPDATEMySQL或者先查后判前者一条SQL搞定后者逻辑更清晰但多一次查询。数据量不大时用后者几万行以上用前者。3.4 事务控制导入失败必须能回滚导入最怕的就是“导了一半失败了数据库里一半新数据一半旧数据乱成一锅粥”。所以整个导入过程必须包在一个事务里要么全成功要么全回滚。Connection conn null; try { conn dataSource.getConnection(); conn.setAutoCommit(false); // 关闭自动提交开启事务 // ... 批量插入 ... conn.commit(); // 全部成功才提交 } catch (Exception e) { if (conn ! null) { conn.rollback(); // 任何异常都回滚 } throw e; } finally { if (conn ! null) conn.close(); }提示如果业务允许“部分成功”那就不要用大事务改成每批独立提交并记录失败行。但大多数导入场景用户期望的是“要么全进要么全不进”所以大事务更符合直觉。4. 完整实操流程与关键代码实现4.1 第一步JSP上传页面怎么写才干净页面部分我只保留最核心的东西一个表单、一个文件输入框、一个提交按钮外加一个结果展示区。form action${pageContext.request.contextPath}/importExcel methodpost enctypemultipart/form-data input typefile nameexcelFile accept.xls,.xlsx required button typesubmit导入/button /form几个细节enctypemultipart/form-data是必须的漏了的话后台拿不到文件只会拿到文件名。accept.xls,.xlsx只是给浏览器一个提示不是强制后台仍要校验扩展名。required属性做前端必填校验减少无效请求。结果展示我用JSP的EL表达式渲染Servlet传回的属性c:if test${not empty result} p成功${result.successCount} 条/p p失败${result.failCount} 条/p ul c:forEach items${result.errors} varerr li${err}/li /c:forEach /ul /c:if4.2 第二步Servlet接收文件并解析Servlet的doPost方法是核心入口。流程是拿到Part、校验文件类型、用POI打开工作簿、遍历行、组装数据、批量入库。protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Part filePart request.getPart(excelFile); String fileName filePart.getSubmittedFileName(); // 校验扩展名 if (!fileName.toLowerCase().endsWith(.xls) !fileName.toLowerCase().endsWith(.xlsx)) { request.setAttribute(result, ImportResult.error(只支持xls和xlsx格式)); request.getRequestDispatcher(/upload.jsp).forward(request, response); return; } ImportResult result new ImportResult(); try (InputStream is filePart.getInputStream(); Workbook workbook WorkbookFactory.create(is)) { Sheet sheet workbook.getSheetAt(0); // ... 解析和入库逻辑 ... } catch (Exception e) { result ImportResult.error(解析失败 e.getMessage()); } request.setAttribute(result, result); request.getRequestDispatcher(/upload.jsp).forward(request, response); }WorkbookFactory.create(is)会自动根据文件头判断是HSSF还是XSSF不用你手动判断扩展名很省心。4.3 第三步遍历行并组装批量参数解析部分我习惯从第1行开始第0行是表头逐行读取组装成对象列表再统一入库。ListUser userList new ArrayList(); int lastRow sheet.getLastRowNum(); for (int i 1; i lastRow; i) { Row row sheet.getRow(i); if (row null) continue; // 空行跳过 String name getCellValue(row.getCell(headerMap.get(姓名))); String phone getCellValue(row.getCell(headerMap.get(手机号))); // 必填校验 if (name.isEmpty()) { result.addError(第 (i 1) 行姓名为空已跳过); continue; } User user new User(); user.setName(name); user.setPhone(phone); userList.add(user); }这里i 1是因为Excel行号从1开始而代码索引从0开始报错时给用户看的行号要加1否则用户按提示去找会找错行体验很差。4.4 第四步批量插入与事务提交组装完列表后就是批量入库。核心是addBatch()攒够一批就executeBatch()。String sql INSERT INTO user (name, phone) VALUES (?, ?); try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); int batchSize 500; int count 0; for (User user : userList) { ps.setString(1, user.getName()); ps.setString(2, user.getPhone()); ps.addBatch(); if (count % batchSize 0) { ps.executeBatch(); ps.clearBatch(); } } // 处理最后不足一批的 if (count % batchSize ! 0) { ps.executeBatch(); } conn.commit(); result.setSuccessCount(userList.size()); } catch (SQLException e) { // 回滚逻辑 result ImportResult.error(入库失败 e.getMessage()); }batchSize设500是我反复测试后的经验值。你可以根据自己数据库的max_allowed_packet参数调整但一般500到1000都很稳。4.5 第五步内存优化——大文件怎么办如果用户传的是10万行的xlsxXSSFWorkbook会把整个文件加载进内存很容易OOM。这时候有两个选择用SXSSFWorkbook这是POI提供的流式写入版本但它是写Excel用的读的话不适用。用事件模型XSSFReader基于SAX的流式读取内存占用极低但代码复杂需要自己处理各种事件。我的建议是如果文件不超过5万行直接用XSSFWorkbook简单可靠超过5万行考虑用XSSFReader事件模型或者干脆限制上传文件大小让用户分批导。实际业务中超过5万行的Excel导入需求并不多限制大小是最省事的方案。5. 常见问题与排查技巧实录5.1 上传后Servlet里getPart返回null这是新手最常遇到的问题。原因几乎都是表单漏了enctypemultipart/form-data或者Servlet漏了MultipartConfig注解。前者导致请求不是multipart格式后者导致容器不知道怎么解析。两个都检查一遍基本能解决。还有一种情况是name属性对不上。表单里是nameexcelFileServlet里就必须getPart(excelFile)大小写敏感一个字母都不能差。5.2 数字变成科学计数法前面提过用BigDecimal.valueOf(d).stripTrailingZeros().toPlainString()解决。但还有一种情况如果单元格本身是文本格式但内容是数字POI会按STRING类型返回这时候直接取字符串就行不会有科学计数法问题。所以关键是先判断类型再决定怎么取。5.3 日期读出来是数字Excel里的日期本质是一个数字从1900年1月1日算起的天数POI默认按NUMERIC返回。必须用DateUtil.isCellDateFormatted(cell)判断是日期才用getDateCellValue()否则会得到45000这种鬼东西。5.4 批量插入报“Packet for query is too large”这是MySQL的max_allowed_packet限制。解决办法有两个一是调大数据库参数二是减小batchSize。我一般选后者把500降到200问题就没了。改数据库参数需要重启影响面大不如改代码。5.5 导入后中文乱码乱码通常出在两个地方一是JSP页面没设pageEncodingUTF-8二是数据库连接URL没加characterEncodingutf8。两个都检查。另外Excel文件本身的编码一般没问题POI读取时是按Unicode处理的所以乱码基本是页面或数据库连接的问题。5.6 常见问题速查表现象最可能原因解决方向getPart返回null缺enctype或缺MultipartConfig补注解和表单属性数字变科学计数法直接getStringCellValue按类型取值BigDecimal日期变数字未判断日期格式DateUtil.isCellDateFormatted批量插入报错batch太大减小batchSize中文乱码编码未统一页面和连接都设UTF-8导入慢逐条插入改批量addBatch内存溢出文件太大限制大小或流式读取5.7 几个我踩过的坑第一个坑表头有空单元格。用户可能在表头行中间留了空列导致headerRow.getCell(i)返回nullgetStringCellValue()直接NPE。所以取表头时一定要判空。第二个坑合并单元格。如果Excel里有合并单元格只有左上角那个格子有值其他格子是null。POI不会自动帮你填充需要自己处理sheet.getMergedRegions()。这个需求不常见但遇到了很头疼我的做法是提示用户“请勿使用合并单元格”。第三个坑公式单元格。如果单元格是公式getCellType()返回FORMULA直接取字符串会拿到公式本身而不是计算结果。需要用FormulaEvaluator求值FormulaEvaluator evaluator workbook.getCreationHelper().createFormulaEvaluator(); CellValue cellValue evaluator.evaluate(cell);不过实际导入场景用户很少用公式遇到了提示一下即可。6. 性能调优与扩展思路6.1 从1万行到10万行的优化路径如果你的导入量级上去了光靠批量插入还不够可以叠加这几个优化关闭自动提交这个前面说了必须做。用rewriteBatchedStatementstrueMySQL连接URL加这个参数能把批量插入重写成一条大SQL性能再提升一截。异步导入文件上传后立即返回“正在导入”后台开线程处理用户刷新页面看进度。适合超大文件。分片读取用XSSFReader事件模型边读边入库内存占用恒定。6.2 导入进度反馈怎么做同步导入时用户点完按钮只能干等体验不好。我的做法是用session存一个进度对象Servlet每处理一批就更新进度前端用AJAX轮询查询进度接口。这样用户能看到“已处理3000/10000条”心里有底。6.3 模板下载功能顺手加上导入功能最好配一个“下载模板”按钮让用户按标准格式填。模板就是一个只有表头的空Excel用POI生成即可。这个小功能能大幅降低用户填错格式的概率非常值得做。Workbook wb new XSSFWorkbook(); Sheet sheet wb.createSheet(模板); Row header sheet.createRow(0); header.createCell(0).setCellValue(姓名); header.createCell(1).setCellValue(手机号); // 输出到response的OutputStream6.4 导入日志留痕生产环境里导入操作一定要留日志谁导的、什么时候、导了多少条、失败多少条。出了问题能追溯。我一般单独建一张import_log表记录这些信息比翻服务器日志方便得多。7. 一些个人体会和后续可扩展的方向这套方案我在好几个内部系统里用过从几千行到几万行都跑得挺稳。核心就三句话上传用Part API解析用POI按类型取值入库用批量加事务。把这三块吃透剩下的都是细节。如果后续要扩展我建议往这几个方向走一是把解析逻辑抽成独立的ServiceServlet只做参数接收和结果返回方便单元测试二是支持多Sheet导入一个文件里多个工作表分别对应不同的表三是加上字段映射配置让管理员在页面上配置“Excel第几列对应数据库哪个字段”这样连代码都不用改就能适配新模板。最后分享一个小技巧导入前先做一次“预检”。用户选完文件后先解析前10行展示给用户看让用户确认“表头对不对、数据格式对不对”确认无误再正式导入。这一步能挡掉80%的格式错误比导完再报错体验好太多。我自从加了这个预检步骤用户反馈的“导入失败”问题少了一大半。