个人随笔
目录
SpringBoot+MyBatis实现可配置报表系统与Excel批量导入更新方案（传统模式）
2026-01-14 11:09:13

一、需求概述

核心需求：

  1. 可配置报表系统：报表查询SQL配置在数据库中，前端传递参数，需利用MyBatis预编译功能防止SQL注入，避免安全问题

  2. Excel批量导入/更新：导入目标表名、参数均为可配置，同时保证安全性和预编译特性

二、核心实现思路

核心原则：分离SQL模板静态部分与动态参数，MyBatis预编译处理参数，表名/字段名等配置项通过安全校验后拼接

  1. SQL模板设计：配置在数据库的SQL使用MyBatis风格参数占位符（如#{name}），保留预编译能力

  2. 动态SQL执行：通过MyBatis的SqlSession手动构建PreparedStatement，利用预编译处理参数

  3. 表名/字段名安全校验：对配置的表名、字段名做白名单校验，防止恶意注入

  4. Excel导入配置化：设计导入配置表，关联Excel列、目标表、字段映射关系，批量操作采用MyBatis批量语法

三、具体实现代码

3.1 数据库表设计（核心配置表）

  2. -- 报表SQL配置表
  3. CREATE TABLE report_config (
  4.     id BIGINT PRIMARY KEY AUTO_INCREMENT,
  5.     report_code VARCHAR(50) NOT NULL COMMENT '报表编码',
  6.     sql_template TEXT NOT NULL COMMENT 'SQL模板（使用#{参数名}占位符）',
  7.     create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  8.     update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  9.     UNIQUE KEY uk_report_code (report_code)
  10. ) COMMENT '报表SQL配置表';
  12. -- Excel导入配置表
  13. CREATE TABLE excel_import_config (
  14.     id BIGINT PRIMARY KEY AUTO_INCREMENT,
  15.     import_code VARCHAR(50) NOT NULL COMMENT '导入编码',
  16.     target_table VARCHAR(50) NOT NULL COMMENT '目标表名',
  17.     column_mapping JSON NOT NULL COMMENT '列映射：{"excel列名":"数据库字段名"}',
  18.     create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  19.     update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  20.     UNIQUE KEY uk_import_code (import_code)
  21. ) COMMENT 'Excel导入配置表';
  23. -- 初始化测试数据
  24. INSERT INTO report_config (report_code, sql_template) VALUES 
  25. ('user_query', 'SELECT * FROM user WHERE name = #{name} AND age = #{age}');
  27. INSERT INTO excel_import_config (import_code, target_table, column_mapping) VALUES 
  28. ('user_import', 'user', '{"姓名":"name","年龄":"age","手机号":"phone"}');

3.2 核心依赖（pom.xml）

  2. <dependencies>
  3.     <!-- SpringBoot核心 -->
  4.     <dependency>
  5.         <groupId>org.springframework.boot</groupId>
  6.         <artifactId>spring-boot-starter-web&lt;/artifactId&gt;
  7.     &lt;/dependency&gt;
  8.     <!-- MyBatis -->
  9.     <dependency>
  10.         <groupId>org.mybatis.spring.boot</groupId>
  11.         <artifactId>mybatis-spring-boot-starter</artifactId>
  12.         <version>3.0.3</version>
  13.     </dependency&gt;
  14.     <!-- MySQL驱动 -->
  15.     <dependency>
  16.         <groupId>com.mysql</groupId>
  17.         <artifactId>mysql-connector-j</artifactId>
  18.         <scope>runtime</scope>
  19.     </dependency>
  20. <!-- EasyExcel（Excel解析） -->
  21.     <dependency>
  22.         <groupId>com.alibaba</groupId>
  23.         <artifactId>easyexcel</artifactId>
  24.         <version>3.3.2</version>
  25.     </dependency&gt;
  26.     <!-- 工具类 -->
  27.     <dependency>
  28.         <groupId>cn.hutool</groupId>
  29.         <artifactId>hutool-all</artifactId>
  30.         <version>5.8.22</version>
  31.     </dependency>
  32. </dependencies>

3.3 报表查询实现（利用MyBatis预编译）

  2. import org.apache.ibatis.session.SqlSession;
  3. import org.springframework.beans.factory.annotation.Autowired;
  4. import org.springframework.jdbc.core.JdbcTemplate;
  5. import org.springframework.stereotype.Service;
  6. import org.springframework.util.Assert;
  8. import javax.sql.DataSource;
  9. import java.sql.Connection;
  10. import java.sql.PreparedStatement;
  11. import java.sql.ResultSet;
  12. import java.sql.SQLException;
  13. import java.util.ArrayList;
  14. import java.util.HashMap;
  15. import java.util.List;
  16. import java.util.Map;
  17. import java.util.regex.Matcher;
  18. import java.util.regex.Pattern;
  20. @Service
  21. public class ReportService {
  23.     @Autowired
  24.     private JdbcTemplate jdbcTemplate;
  25.     @Autowired
  26.     private SqlSession sqlSession;
  27.     @Autowired
  28.     private DataSource dataSource;
  30.     /**
  31.      * 执行配置化报表查询
  32.      * @param reportCode 报表编码
  33.      * @param params 前端传递的参数
  34.      * @return 查询结果
  35.      */
  36.     public List<Map<String, Object>> executeReport(String reportCode, Map<String, Object> params) {
  37.         // 1. 从数据库获取SQL模板
  38.         String sqlTemplate = jdbcTemplate.queryForObject(
  39.                 "SELECT sql_template FROM report_config WHERE report_code = ?",
  40.                 new Object[]{reportCode},
  41.                 String.class
  42.         );
  43.         Assert.notNull(sqlTemplate, "报表配置不存在：" + reportCode);
  45.         // 2. 利用MyBatis的预编译执行SQL（核心：保留预编译能力）
  46.         try (Connection conn = dataSource.getConnection()) {
  47.             // 构建预编译Statement
  48.             PreparedStatement ps = createPreparedStatement(conn, sqlTemplate, params);
  49.             // 执行查询
  50.             ResultSet rs = ps.executeQuery();
  51.             // 解析结果集为Map列表
  52.             return ResultSetUtil.parseResultSet(rs);
  53.         } catch (SQLException e) {
  54.             throw new RuntimeException("执行报表SQL失败", e);
  55.         }
  56.     }
  58.     /**
  59.      * 创建预编译Statement（核心：参数预编译，防止注入）
  60.      */
  61.     private PreparedStatement createPreparedStatement(Connection conn, String sqlTemplate, Map<String, Object> params) throws SQLException {
  62.         // 步骤1：解析SQL模板中的参数名，替换为?（保持MyBatis风格）
  63.         // 示例：select * from user where name=#{name} → select * from user where name=?
  64.         String sql = parseSqlTemplate(sqlTemplate);
  65.         PreparedStatement ps = conn.prepareStatement(sql);
  67.         // 步骤2：按参数顺序设置值（预编译核心，防止SQL注入）
  68.         List<String> paramNames = SqlParamParser.extractParamNames(sqlTemplate);
  69.         for (int i = 0; i < paramNames.size(); i++) {
  70.             String paramName = paramNames.get(i);
  71.             Object value = params.get(paramName);
  72.             ps.setObject(i + 1, value); // JDBC参数索引从1开始
  73.         }
  74.         return ps;
  75.     }
  77.     /**
  78.      * 解析SQL模板：将#{param}替换为?
  79.      */
  80.     private String parseSqlTemplate(String sqlTemplate) {
  81.         return sqlTemplate.replaceAll("#\\{[^}]+\\}", "?");
  82.     }
  83. }
  85. // 辅助类：提取SQL模板中的参数名
  86. class SqlParamParser {
  87.     /**
  88.      * 提取#{param}中的param名称，返回有序列表
  89.      */
  90.     public static List<String> extractParamNames(String sqlTemplate) {
  91.         List<String> paramNames = new ArrayList<>();
  92.         Pattern pattern = Pattern.compile("#\\{([^}]+)\\}");
  93.         Matcher matcher = pattern.matcher(sqlTemplate);
  94.         while (matcher.find()) {
  95.             paramNames.add(matcher.group(1).trim());
  96.         }
  97.         return paramNames;
  98.     }
  99. }
  101. // 辅助类：解析ResultSet为Map列表
  102. class ResultSetUtil {
  103.     public static List<Map<String, Object>> parseResultSet(ResultSet rs) throws SQLException {
  104.         List<Map<String, Object>> result = new ArrayList<>();
  105.         ResultSetMetaData metaData = rs.getMetaData();
  106.         int columnCount = metaData.getColumnCount();
  108.         while (rs.next()) {
  109.             Map<String, Object> row = new HashMap<>();
  110.             for (int i = 1; i <= columnCount; i++) {
  111.                 String columnName = metaData.getColumnName(i);
  112.                 Object value = rs.getObject(i);
  113.                 row.put(columnName, value);
  114.             }
  115.             result.add(row);
  116.         }
  117.         return result;
  118.     }
  119. }

3.4 Excel批量导入/更新实现（配置化+预编译）

  2. import com.alibaba.excel.EasyExcel;
  3. import com.alibaba.excel.read.listener.PageReadListener;
  4. import com.alibaba.fastjson2.JSON;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.jdbc.core.JdbcTemplate;
  7. import org.springframework.stereotype.Service;
  8. import org.springframework.transaction.annotation.Transactional;
  9. import org.springframework.util.Assert;
  11. import javax.sql.DataSource;
  12. import java.sql.Connection;
  13. import java.sql.PreparedStatement;
  14. import java.sql.SQLException;
  15. import java.util.*;
  17. @Service
  18. public class ExcelImportService {
  20.     @Autowired
  21.     private JdbcTemplate jdbcTemplate;
  22.     @Autowired
  23.     private DataSource dataSource;
  25.     // 表名白名单（核心：防止表名注入，可从配置文件/数据库加载）
  26.     private static final Set<String> TABLE_WHITELIST = new HashSet<>(Arrays.asList("user", "order", "product"));
  28.     /**
  29.      * 批量导入Excel数据（配置化表名+字段）
  30.      * @param importCode 导入编码
  31.      * @param excelFilePath Excel文件路径
  32.      */
  33.     @Transactional(rollbackFor = Exception.class)
  34.     public void batchImport(String importCode, String excelFilePath) {
  35.         // 1. 获取导入配置
  36.         Map<String, Object> config = jdbcTemplate.queryForMap(
  37.                 "SELECT target_table, column_mapping FROM excel_import_config WHERE import_code = ?",
  38.                 importCode
  39.         );
  40.         String targetTable = config.get("target_table").toString();
  41.         String columnMappingStr = config.get("column_mapping").toString();
  42.         Map<String, String> columnMapping = JSON.parseObject(columnMappingStr, Map.class);
  44.         // 2. 表名安全校验（核心：防止表名注入）
  45.         Assert.isTrue(TABLE_WHITELIST.contains(targetTable), "不允许导入的表名：" + targetTable);
  47.         // 3. 解析Excel并批量插入
  48.         List<Map<String, Object>> dataList = new ArrayList<>();
  49.         EasyExcel.read(excelFilePath, new PageReadListener<Map<Integer, String>>((data, context) -> {
  50.             // 解析单行Excel数据（key为列索引，value为单元格值）
  51.             Map<String, Object> rowData = parseExcelRow(data, columnMapping);
  52.             dataList.add(rowData);
  54.             // 批量提交（每1000条）
  55.             if (dataList.size() >= 1000) {
  56.                 batchInsert(targetTable, columnMapping, dataList);
  57.                 dataList.clear();
  58.             }
  59.         })).sheet().doRead();
  61.         // 处理剩余数据
  62.         if (!dataList.isEmpty()) {
  63.             batchInsert(targetTable, columnMapping, dataList);
  64.         }
  65.     }
  67.     /**
  68.      * 批量插入（使用预编译，防止参数注入）
  69.      */
  70.     private void batchInsert(String targetTable, Map<String, String> columnMapping, List<Map<String, Object>> dataList) {
  71.         // 构建插入SQL：INSERT INTO user (name, age, phone) VALUES (?, ?, ?)
  72.         List<String> dbColumns = new ArrayList<>(columnMapping.values());
  73.         String columns = String.join(",", dbColumns);
  74.         String placeholders = String.join(",", Collections.nCopies(dbColumns.size(), "?"));
  75.         String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", targetTable, columns, placeholders);
  77.         try (Connection conn = dataSource.getConnection()) {
  78.             conn.setAutoCommit(false); // 关闭自动提交
  79.             PreparedStatement ps = conn.prepareStatement(sql);
  81.             // 批量设置参数（预编译核心）
  82.             for (Map<String, Object> row : dataList) {
  83.                 for (int i = 0; i < dbColumns.size(); i++) {
  84.                     String column = dbColumns.get(i);
  85.                     Object value = row.get(column);
  86.                     ps.setObject(i + 1, value);
  87.                 }
  88.                 ps.addBatch(); // 添加到批处理
  89.             }
  91.             // 执行批处理
  92.             ps.executeBatch();
  93.             conn.commit(); // 提交事务
  94.         } catch (SQLException e) {
  95.             throw new RuntimeException("批量插入失败", e);
  96.         }
  97.     }
  99.     /**
  100.      * 解析Excel行数据，映射为数据库字段值
  101.      */
  102.     private Map<String, Object> parseExcelRow(Map<Integer, String> excelRow, Map<String, String> columnMapping) {
  103.         Map<String, Object> rowData = new HashMap<>();
  104.         // 假设Excel表头行是第一行，这里简化处理（实际需先读取表头）
  105.         excelRow.forEach((index, value) -> {
  106.             // 根据列索引获取Excel列名（实际需先解析表头映射）
  107.             String excelColumnName = getExcelColumnNameByIndex(index);
  108.             String dbColumn = columnMapping.get(excelColumnName);
  109.             if (dbColumn != null) {
  110.                 rowData.put(dbColumn, value);
  111.             }
  112.         });
  113.         return rowData;
  114.     }
  116.     /**
  117.      * 模拟：根据列索引获取Excel列名（实际需从Excel表头解析）
  118.      */
  119.     private String getExcelColumnNameByIndex(Integer index) {
  120.         Map<Integer, String> indexToName = new HashMap<>();
  121.         indexToName.put(0, "姓名");
  122.         indexToName.put(1, "年龄");
  123.         indexToName.put(2, "手机号");
  124.         return indexToName.get(index);
  125.     }
  127.     /**
  128.      * 批量更新扩展（核心思路同批量插入）
  129.      */
  130.     private void batchUpdate(String targetTable, Map<String, String> columnMapping, List<Map<String, Object>> dataList) {
  131.         // 假设主键为id，构建更新SQL：UPDATE user SET name=?, age=? WHERE id=?
  132.         List<String> dbColumns = new ArrayList<>(columnMapping.values());
  133.         dbColumns.remove("id"); // 移除主键列
  134.         String updateSet = dbColumns.stream().map(col -> col + "=?").collect(Collectors.joining(","));
  135.         String sql = String.format("UPDATE %s SET %s WHERE id=?", targetTable, updateSet);
  137.         // 后续逻辑同批量插入，仅参数设置顺序不同（先设置更新列，再设置主键）
  138.         try (Connection conn = dataSource.getConnection()) {
  139.             conn.setAutoCommit(false);
  140.             PreparedStatement ps = conn.prepareStatement(sql);
  142.             for (Map<String, Object> row : dataList) {
  143.                 int paramIndex = 1;
  144.                 // 设置更新列参数
  145.                 for (String column : dbColumns) {
  146.                     ps.setObject(paramIndex++, row.get(column));
  147.                 }
  148.                 // 设置主键参数（WHERE条件）
  149.                 ps.setObject(paramIndex, row.get("id"));
  150.                 ps.addBatch();
  151.             }
  153.             ps.executeBatch();
  154.             conn.commit();
  155.         } catch (SQLException e) {
  156.             throw new RuntimeException("批量更新失败", e);
  157.         }
  158.     }
  159. }

3.5 控制器层（测试接口）

  2. import org.springframework.beans.factory.annotation.Autowired;
  3. import org.springframework.web.bind.annotation.*;
  4. import org.springframework.web.multipart.MultipartFile;
  6. import java.io.File;
  7. import java.util.List;
  8. import java.util.Map;
  10. @RestController
  11. @RequestMapping("/config")
  12. public class ConfigController {
  14.     @Autowired
  15.     private ReportService reportService;
  16.     @Autowired
  17.     private ExcelImportService excelImportService;
  19.     /**
  20.      * 报表查询接口
  21.      */
  22.     @PostMapping("/report/{reportCode}")
  23.     public List<Map<String, Object>> queryReport(
  24.             @PathVariable String reportCode,
  25.             @RequestBody Map<String, Object> params) {
  26.         return reportService.executeReport(reportCode, params);
  27.     }
  29.     /**
  30.      * Excel导入接口
  31.      */
  32.     @PostMapping("/import/{importCode}")
  33.     public String importExcel(
  34.             @PathVariable String importCode,
  35.             @RequestParam("file") MultipartFile file) {
  36.         // 保存文件到临时目录（实际项目建议使用流式处理，避免落地）
  37.         File tempFile = new File(System.getProperty("java.io.tmpdir") + "/" + file.getOriginalFilename());
  38.         try {
  39.             file.transferTo(tempFile);
  40.             excelImportService.batchImport(importCode, tempFile.getAbsolutePath());
  41.             return "导入成功";
  42.         } catch (Exception e) {
  43.             return "导入失败：" + e.getMessage();
  44.         }
  45.     }
  46. }

四、关键安全保障说明

  1. 参数预编译：所有前端传递的参数均通过PreparedStatement.setObject()设置，MyBatis/JDBC自动做参数转义，彻底防止SQL注入

  2. 表名/字段名白名单：对配置的表名做白名单校验，仅允许导入到指定表，避免恶意配置user; drop table xxx等危险表名

  3. SQL模板校验：可扩展添加SQL模板语法校验，禁止配置包含;、DROP、ALTER等危险关键字的SQL

  4. 权限控制：建议为报表配置/Excel导入配置添加权限校验，仅允许管理员配置SQL和导入规则

五、总结

  1. 预编译核心：将配置的SQL模板中的#{参数}替换为?，通过PreparedStatement设置参数，保留JDBC预编译能力，杜绝参数注入

  2. 配置化安全：表名/字段名通过白名单校验，仅允许合法配置，防止表名注入

  3. 批量操作优化：使用JDBC批处理（addBatch()+executeBatch()）提升Excel导入/更新效率，结合事务保证数据一致性

该方案完全利用MyBatis/JDBC的预编译特性，同时满足报表SQL和Excel导入的全配置化需求，兼顾安全性和灵活性，可直接集成到SpringBoot项目中使用。

（注：文档部分内容可能由 AI 生成）

 9

啊！这个可能是世界上最丑的留言输入框功能~


当然，也是最丑的留言列表

有疑问发邮件到 : suibibk@qq.com 侵权立删
Copyright : 个人随笔   备案号 : 粤ICP备18099399号-2