JavaExcel：自动生成数据表并插入数据

故事背景

出于好奇，当下扫描excel读取数据进数据库 or 导出数据库数据组成excel的功能层出不穷，代码也是前篇一律，poi或者easy excel两种SDK的二次利用带来了各种封装方法。

那么为何不能直接扫描excel后根据列的属性名与行数据的属性建立SQL数据表，并将数据插入到数据表中，再通过前端与用户交互进行SQL组装，得到用户想要的数据结果。

模块架构图

采取原始的数据库信息读取，配置编程式事务（代码块维度锁事务），封装原始的JDBC执行模板。

excel读取列属性名，转换成英文作为表的属性名，行解析器解析数据行数据属性作为表属性的数据类型。

代码构成

基础实体：

ColumnEntity.java

@Data
@EqualsAndHashCode
public class ColumnEntity {private String columnName;private String columnSqlInfo;public void clear(){setColumnName(null);setColumnSqlInfo(null);}
}

ValueEntity.java

@Data
public class ValueEntity{private String columnName;/*数据拼接到SQL上也是字符串类型*/private String valueOfString;public void clear(){setColumnName(null);setValueOfString(null);}
}

JsonResponse.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class JsonResponse {private int code = -1;private String message;private String json;public static JsonResponse success(String json){JsonResponse jsonResponse = new JsonResponse();jsonResponse.setCode(0);jsonResponse.setJson(json);jsonResponse.setMessage("访问成功");return jsonResponse;}public static JsonResponse fail(String massage){JsonResponse jsonResponse = new JsonResponse();jsonResponse.setMessage(massage);return jsonResponse;}
}

api对前端提供特性：

WebMvcConfiguration.java

@Configuration
@Slf4j
public class WebMvcConfiguration implements WebMvcConfigurer , HandlerInterceptor {@Overridepublic void addInterceptors(@NonNull InterceptorRegistry registry) {registry.addInterceptor(this);WebMvcConfigurer.super.addInterceptors(registry);}@Overridepublic void addCorsMappings(@NonNull CorsRegistry registry) {WebMvcConfigurer.super.addCorsMappings(registry);}@Overridepublic void postHandle(@NonNull HttpServletRequest request,@NonNull  HttpServletResponse response,@NonNull  Object handler, ModelAndView modelAndView) throws Exception {HandlerInterceptor.super.postHandle(request, response, handler, modelAndView);}@Overridepublic boolean preHandle(HttpServletRequest request,@NonNull HttpServletResponse response,@NonNull Object handler) throws Exception {String sql = request.getParameter("sql");String tableName = request.getParameter("tableName");if (StringUtils.isNotEmpty(sql)){if (!sql.contains("test") || !tableName.contains("test")){log.error("过滤非法请求：{}",sql);return false;}if (sql.contains("DELETE") || sql.contains("delete") || sql.contains("UPDATE") || sql.contains("update")){log.error("过滤非法请求：{}",sql);return false;}}return HandlerInterceptor.super.preHandle(request, response, handler);}@Overridepublic void afterCompletion(@NonNull HttpServletRequest request,@NonNull HttpServletResponse response,@NonNull Object handler, Exception ex) throws Exception {HandlerInterceptor.super.afterCompletion(request, response, handler, ex);}
}

ExcelDataController.java

@RestController
@RequestMapping("/data")
@CrossOrigin
@Slf4j
public class ExcelDataController {@Resourceprivate SqlDataProvider sqlDataProvider;@GetMapping("/handle/sql")@ResponseBodypublic JsonResponse handleData(@RequestParam String sql) {String executed = sqlDataProvider.executeQuerySql(sql);return Objects.equals("", executed)? JsonResponse.fail("sql执行错误"): JsonResponse.success(executed);}
}

ExcelResolveController.java

@RestController
@RequestMapping("/excel")
@CrossOrigin
@Slf4j
public class ExcelResolveController {@Resourceprivate ExcelEntityService excelEntityService;@RequestMapping("/createAndInsert")@ResponseBodypublic void uploadExcel(@RequestParam("fileName") MultipartFile file) {excelEntityService.createTable(file);excelEntityService.insertEntity(file);}@RequestMapping("/insert")@ResponseBodypublic void insertExcel(@RequestParam("fileName") MultipartFile file) {excelEntityService.insertEntity(file);}@RequestMapping("/drop")@ResponseBodypublic void delData(@RequestParam("fileName")String fileName){excelEntityService.dropTable(fileName);}}

ExcelEntityService.java

public interface ExcelEntityService {/*** 创建数据表** @param file 文件*/void createTable(MultipartFile file);/*** 插入数据实体** @param file 文件*/void insertEntity(MultipartFile file);/*** 删除数据表* @param fileName 文件名*/void dropTable(String fileName);
}

ExcelEntityServiceImpl.java

@Service
@Slf4j
@ConditionalOnBean({SqlDataProvider.class,SqlSpliceProvider.class})
public class ExcelEntityServiceImpl implements ExcelEntityService {@Resourceprivate SqlDataProvider sqlDataProvider;@Resourceprivate SqlSpliceProvider spliceProvider;@Resourceprivate ReadExcelService readExcelService;@Overridepublic void createTable(MultipartFile file) {String dropTableSql = spliceProvider.dropTableSql(file.getName());log.info("删表SQL：{}",dropTableSql);sqlDataProvider.executeSql(dropTableSql);List<ColumnEntity> excelColumnList = readExcelService.getExcelColumnList(file);StringBuffer stringBuffer = spliceProvider.spliceCreateTableSql(excelColumnList, file.getName());log.warn("建表SQL：{}", stringBuffer);sqlDataProvider.executeSql(stringBuffer.toString());}@Overridepublic void insertEntity(MultipartFile file) {String existsTableSql = spliceProvider.existsTableSql(file.getName());Object aReturn = sqlDataProvider.executeSqlAndGetReturn(existsTableSql);if (Objects.nonNull(aReturn)) {List<ColumnEntity> excelColumnList = readExcelService.getExcelColumnList(file);Map<Integer, List<ValueEntity>> excelRowDataMap = readExcelService.getExcelRowDataMap(file, excelColumnList);List<String> stringBuffer1 = spliceProvider.spliceInsertValueSql(excelRowDataMap, file.getName());stringBuffer1.forEach(s -> sqlDataProvider.executeSql(s));}else{log.warn("不存在数据表：{}",file.getName());}}@Overridepublic void dropTable(String fileName) {String dropTableSql = spliceProvider.dropTableSql(fileName);log.info("删表SQL：{}",dropTableSql);sqlDataProvider.executeSql(dropTableSql);}
}

excel解析器：

ReadExcelService.java

public interface ReadExcelService {/*** 读取EXCEL的列属性列表** @param file 文件* @return 列属性实体列表（只含有列的属性）*/List<ColumnEntity> getExcelColumnList(MultipartFile file);/*** 读取每一行的行实体列表，一个LIST为一行** @param file             文件* @param columnEntityList 列实体列表* @return 全部的值MAP<行号 ， 行的属性值LIST>*/Map<Integer, List<ValueEntity>> getExcelRowDataMap(MultipartFile file, List<ColumnEntity> columnEntityList);}

ReadExcel.java

@Slf4j
@Service
public class ReadExcel implements ReadExcelService {private static final Integer LIMIT_SCAN_NUM = 500 * 1000;/*** 根据文件名读取Excel文件获取列信息列表** @param file 文件* @return List<列实体>*/@Overridepublic List<ColumnEntity> getExcelColumnList(MultipartFile file) {List<ColumnEntity> list;Workbook workbook = getWorkbook(file);if (Objects.isNull(workbook)) {return new LinkedList<>();}list = getExcelColumnList(workbook);return list;}/*** 获取行数与对应行得值SQL实体列表** @param file             工作薄文件* @param columnEntityList 列信息对象* @return map<行号 ， 行内每一个单元格得值SQL实体列表>*/@Overridepublic Map<Integer, List<ValueEntity>> getExcelRowDataMap(MultipartFile file, List<ColumnEntity> columnEntityList) {Workbook workbook = getWorkbook(file);if (Objects.isNull(workbook)) {return new HashMap<>();}Sheet sheet = workbook.getSheetAt(0);int lastRowNum = sheet.getLastRowNum();Map<Integer, List<ValueEntity>> map = new HashMap<>();if (lastRowNum <= LIMIT_SCAN_NUM) {for (int i = 1; i <= lastRowNum; i++) {Row row = sheet.getRow(i);List<ValueEntity> list = new LinkedList<>();for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {ValueEntity valueEntity = new ValueEntity();valueEntity.setColumnName(columnEntityList.get(j).getColumnName());valueEntity.setValueOfString(ExcelReadStringUtil.getValueSqlString(row.getCell(j)));list.add(valueEntity);}map.put(i, list);}}else{throw new RuntimeException("扫描的Excel文件数据量超过限定值，请检查核定容量");}return map;}/*** 解析返回excel数据表第一行属性信息列表数据** @param workbook 数据工作薄* @return List<列实体>*/private static List<ColumnEntity> getExcelColumnList(Workbook workbook) {/*默认取第一个工作表的第一行数据与第二行数据（第一行用来感知属性名称、第二行用来感知属性类型）*/Sheet dataSheet = workbook.getSheetAt(0);Row topRow = dataSheet.getRow(0);Row typeRow = dataSheet.getRow(1);if (topRow.getPhysicalNumberOfCells() != typeRow.getPhysicalNumberOfCells()) {log.error("数据表列行与数据行列数不一致！退出解析，请整理数据表格式！");throw new RuntimeException("DataSheet is error: com.runjing.resolve_excel_auto.excel.ReadExcel.getExcelColumnList(org.apache.poi.ss.usermodel.Workbook)");}List<ColumnEntity> columnEntityList = new LinkedList<>();for (int i = 0; i < topRow.getPhysicalNumberOfCells(); i++) {ColumnEntity columnEntity = new ColumnEntity();Cell nameCell = topRow.getCell(i);Cell typeRowCell = typeRow.getCell(i);columnEntity.setColumnName(ExcelReadStringUtil.transferPinYin(nameCell.getStringCellValue()));columnEntity.setColumnSqlInfo(ExcelReadStringUtil.switchCellDataSqlInfo(typeRowCell));columnEntityList.add(columnEntity);}return columnEntityList;}/*** 文件转换工作簿对象** @param file excel文件* @return 工作簿对象*/private static Workbook getWorkbook(MultipartFile file) {InputStream is = null;try {is = file.getInputStream();Workbook workbook;workbook = WorkbookFactory.create(is);return workbook;} catch (Exception ex) {ex.printStackTrace();return null;} finally {if (is != null) {try {is.close();} catch (IOException e) {e.printStackTrace();}}}}
}

SQL拼接器：

ExcelReadStringUtil.java

public class ExcelReadStringUtil {/*获取构建表格数据属性SQL*/public static String switchCellDataSqlInfo(Cell dataCell) {return switch (dataCell.getCellType()) {case NUMERIC -> " double default 0.00 ";case STRING -> " varchar(100) default null";case FORMULA -> " varchar default null";case BOOLEAN -> " tinyint(1) default 0";default -> " varchar(64) default null";} + ",";}/*获取单元格值SQL*/public static String getValueSqlString(Cell dataCell) {return switch (dataCell.getCellType()) {case NUMERIC -> String.valueOf(dataCell.getNumericCellValue());case STRING -> quotesHandle(dataCell.getStringCellValue());case FORMULA -> quotesHandle(dataCell.getCellFormula());case BOOLEAN -> transferBool(dataCell.getBooleanCellValue());default -> "null";};}/*** 单引号包裹字段** @param fieldValue 字段值* @return 包裹后字段串*/public static String quotesHandle(String fieldValue) {return "'" + fieldValue + "'";}/*** 布尔类型转换** @param arg1 入参* @return 转换值*/public static String transferBool(Boolean arg1) {return arg1 ? "0" : "1";}/*** 将汉字串转成拼音串** @param columnChineseName 汉字字段名* @return 字段拼音*/public static String transferPinYin(String columnChineseName) {/*转换中文为简体拼音*/return LanguageUtil.convertChineseLan2PinYinAbbreviation(columnChineseName, LanguageUtil.CHINESE_CHAR_REG_SIMPLIFIED);}
}

SqlSpliceStringUtil.java

public class SqlSpliceStringUtil {/*** 反引号处理** @param fieldName 字段名称* @return 被反引号包裹得字段名称*/public static String quotesHandle(String fieldName) {return "`" + fieldName + "`";}/*** 将汉字串转成拼音串** @param columnChineseName 汉字字段名* @return 字段拼音*/public static String transferPinYin(String columnChineseName) {/*转换中文为简体拼音*/return LanguageUtil.convertChineseLan2PinYinAbbreviation(columnChineseName, LanguageUtil.CHINESE_CHAR_REG_SIMPLIFIED);}
}

LanguageUtil.java

@Slf4j
public class LanguageUtil {/*** 定义输出格式*/public static HanyuPinyinOutputFormat hpFormat = new HanyuPinyinOutputFormat();/*** 匹配所有东亚区的语言*/public static String CHINESE_CHAR_REG_SOUTHEAST_ASIA ="^[\u2E80-\u9FFF]+$";/*** 匹配简体和繁体*/public static String CHINESE_CHAR_REG_SIMPLIFIED_OR_TRADITIONAL ="^[\u4E00-\u9FFF]+$";/*** 匹配简体*/public static String CHINESE_CHAR_REG_SIMPLIFIED ="[\u4E00-\u9FA5]+$";static{// 大写格式输出hpFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);// 不需要语调输出hpFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);}/**** 将汉字转成拼音(取首字母或全拼)* @param singleChar 中文字符* @param full 是否全拼* @return 转换后拼音*/public static String convertChineseChar2Pinyin(String singleChar, boolean full,String regExp ) {StringBuffer sb = new StringBuffer();if (singleChar == null || "".equals(singleChar.trim())) {return "";}String pinyin = "";for (int i = 0; i < singleChar.length(); i++) {char unit = singleChar.charAt(i);//是汉字，则转拼音if (match(String.valueOf(unit), regExp)){pinyin = convertSingleChineseChar2Pinyin(unit);if (full) {sb.append(pinyin);} else {sb.append(pinyin.charAt(0));}} else {sb.append(unit);}}return sb.toString();}/**** 将单个汉字转成拼音* @param singleChar 中文汉字* @return 拼音*/private static String convertSingleChineseChar2Pinyin(char singleChar) {String[] res;StringBuffer sb = new StringBuffer();try {res = PinyinHelper.toHanyuPinyinStringArray(singleChar, hpFormat);//对于多音字，只用第一个拼音sb.append(res[0]);} catch (Exception e) {log.error("单个汉字转换成字符失败：{}",e.getMessage());return "";}return sb.toString();}/**** @param str 源字符串* @param regex 正则表达式* @return 是否匹配*/public static boolean match(String str, String regex) {Pattern pattern = Pattern.compile(regex);Matcher matcher = pattern.matcher(str);return matcher.find();}/*** 汉字字符串的的首拼拼成字符串* @param chineseLan 中文字符串* @return 拼音字符串*/public static String convertChineseLan2PinYinAbbreviation(String chineseLan,String regExp) {String ret = "";// 将汉字转换为字符数组char[] charChineseLan = chineseLan.toCharArray();try {for (int i = 0; i < charChineseLan.length; i++) {if(String.valueOf(charChineseLan[i]).matches(regExp)) {// 如果字符是中文,则将中文转为汉语拼音（获取全拼则去掉红色的代码即可）ret += PinyinHelper.toHanyuPinyinStringArray(charChineseLan[i], hpFormat)[0].substring(0, 1);} else {// 如果字符不是中文,则不转换ret += charChineseLan[i];}}} catch (BadHanyuPinyinOutputFormatCombination e) {log.error("获取汉字的的首拼失败：{}",e.getMessage());}return ret;}/*** 判断字符串中是否包含中文汉字** @param content 字符串内容* @return true至少包含1个*/public static boolean hasChinese(CharSequence content) {if (null == content) {return false;}String regex = "[\u2E80-\u2EFF\u2F00-\u2FDF\u31C0-\u31EF\u3400-\u4DBF\u4E00-\u9FFF\uF900-\uFAFF\uD840\uDC00-\uD869\uDEDF\uD869\uDF00-\uD86D\uDF3F\uD86D\uDF40-\uD86E\uDC1F\uD86E\uDC20-\uD873\uDEAF\uD87E\uDC00-\uD87E\uDE1F]+";Pattern pattern = Pattern.compile(regex);return pattern.matcher(content).find();}/*** 判断字符串是否为中文汉字** @param content 字符串内容* @return true都是汉字*/public static boolean isChinese(CharSequence content) {if (null == content) {return false;}String regex = "[\u2E80-\u2EFF\u2F00-\u2FDF\u31C0-\u31EF\u3400-\u4DBF\u4E00-\u9FFF\uF900-\uFAFF\uD840\uDC00-\uD869\uDEDF\uD869\uDF00-\uD86D\uDF3F\uD86D\uDF40-\uD86E\uDC1F\uD86E\uDC20-\uD873\uDEAF\uD87E\uDC00-\uD87E\uDE1F]+";Pattern pattern = Pattern.compile(regex);return pattern.matcher(content).matches();}
}

JsonUtil.java

public class JsonUtil {private final static String arg = "\"";/*** Map转成JSON字符串** @param map* @return JSON*/public static String mapToJsonString(Map<String, Object> map) {return CollectionUtils.isEmpty(map) ? "" : JSONObject.toJSONString(map).replace(arg,"'");}}

SQL配置与执行：

SqlConfiguration.java

@Component("SqlConfiguration")
@ConfigurationProperties(prefix = "jdbc-config")
@Data
public class SqlConfiguration {private String driver;private String url;private String userName;private String password;
}

SqlDataSourceConfiguration.java

@Configuration
@ConditionalOnBean(SqlConfiguration.class)
@Slf4j
public class SqlDataSourceConfiguration {@Resourceprivate SqlConfiguration sqlConfiguration;@Bean("DriverManagerDataSource")@Scope(value = "singleton")public DriverManagerDataSource getDataSource(){DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();driverManagerDataSource.setDriverClassName(sqlConfiguration.getDriver());driverManagerDataSource.setUrl(sqlConfiguration.getUrl());driverManagerDataSource.setUsername(sqlConfiguration.getUserName());driverManagerDataSource.setPassword(sqlConfiguration.getPassword());log.info("扫描生成自定义配置JDBC数据源：{}",sqlConfiguration.getUrl());return driverManagerDataSource;}
}

JdbcTransactionManagerConfiguration.java

@Configuration
@ConditionalOnBean({SqlDataSourceConfiguration.class})
@Slf4j
public class JdbcTransactionManagerConfiguration {@Resourceprivate DriverManagerDataSource driverManagerDataSource;@Bean("JdbcTransactionManager")@Scope("singleton")public JdbcTransactionManager getJdbcTransactionManager(){JdbcTransactionManager jdbcTransactionManager = new JdbcTransactionManager();log.info("开始配置JDBC事务管理者");jdbcTransactionManager.setDataSource(driverManagerDataSource);jdbcTransactionManager.setRollbackOnCommitFailure(true);jdbcTransactionManager.setFailEarlyOnGlobalRollbackOnly(true);jdbcTransactionManager.setGlobalRollbackOnParticipationFailure(true);return jdbcTransactionManager;}}

TransactionTemplateConfiguration.java

@Configuration
@Slf4j
public class TransactionTemplateConfiguration {@Resourceprivate JdbcTransactionManager jdbcTransactionManager;@Bean("ReadCommittedTransactionTemplate")@Scope("singleton")@ConditionalOnBean({JdbcTransactionManager.class})@Lazypublic TransactionTemplate getReadCommittedTransactionTemplate(){TransactionTemplate transactionTemplate = new TransactionTemplate();log.info("生成事务模板，注入事务管理器，设置事务隔离级别为读已提交");transactionTemplate.setTransactionManager(jdbcTransactionManager);transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);return transactionTemplate;}@Bean("ReadUnCommittedTransactionTemplate")@Scope("singleton")@ConditionalOnBean({JdbcTransactionManager.class})@Lazypublic TransactionTemplate getReadUnCommittedTransactionTemplate(){TransactionTemplate transactionTemplate = new TransactionTemplate();log.info("生成事务模板，注入事务管理器，设置事务隔离级别为读未提交");transactionTemplate.setTransactionManager(jdbcTransactionManager);transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);return transactionTemplate;}@Bean("RepeatableReadTransactionTemplate")@Scope("singleton")@ConditionalOnBean({JdbcTransactionManager.class})@Lazypublic TransactionTemplate getRepeatableReadTransactionTemplate(){TransactionTemplate transactionTemplate = new TransactionTemplate();log.info("生成事务模板，注入事务管理器，设置事务隔离级别为可重复读");transactionTemplate.setTransactionManager(jdbcTransactionManager);transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);return transactionTemplate;}@Bean("SerializableTransactionTemplate")@Scope("singleton")@ConditionalOnBean({JdbcTransactionManager.class})@Lazypublic TransactionTemplate getSerializableTransactionTemplate(){TransactionTemplate transactionTemplate = new TransactionTemplate();log.info("生成事务模板，注入事务管理器，设置事务隔离级别为可串行化");transactionTemplate.setTransactionManager(jdbcTransactionManager);transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE);return transactionTemplate;}}

SqlSpliceProvider.java

public interface SqlSpliceProvider {/*** 拼接建表SQL** @param columnEntityList excel的列属性列表* @param tableName        表名* @return SQL*/StringBuffer spliceCreateTableSql(List<ColumnEntity> columnEntityList, String tableName);/*** 拼接删表SQL** @param tableName 表名* @return SQL*/String dropTableSql(String tableName);/*** 拼接判断表存在SQL** @param tableName 表名* @return SQL*/String existsTableSql(String tableName);/*** 拼接插值SQL列表循环执行即可** @param map       值map<行号，对应行的值LIST>* @param tableName 表名* @return SQL*/List<String> spliceInsertValueSql(Map<Integer, List<ValueEntity>> map, String tableName);}

SqlSplicer.java

@Service
public class SqlSplicer implements SqlSpliceProvider {/*** 拼接建表SQL** @param columnEntityList 列信息实体列表* @param tableName        表格名称* @return SQL*/@Overridepublic StringBuffer spliceCreateTableSql(List<ColumnEntity> columnEntityList, String tableName) {StringBuffer stringBuffer = new StringBuffer();stringBuffer.append("CREATE TABLE ").append(SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName))).append(" ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自动主键',");/*扫描属性列表，填充建表SQL*/stringBuffer.append(scanColumnListToSql(columnEntityList));stringBuffer.append(" PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ");return stringBuffer;}/*** 删除数据表，防止表重复** @param tableName 表名称* @return SQL*/@Overridepublic  String dropTableSql(String tableName) {return "DROP TABLE IF EXISTS " + SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName));}/*** 查询数据表是否存在* @param tableName 表名称* @return SQL*/@Overridepublic  String existsTableSql(String tableName) {return "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = " + SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName));}/*** 拼接插值SQL(单插入SQL集合)** @param map       值实体列表Map* @param tableName 表名* @return 插值SQL*/@Overridepublic  List<String> spliceInsertValueSql(Map<Integer, List<ValueEntity>> map, String tableName) {List<String> sqlList = new LinkedList<>();map.values().forEach(valueEntityList -> sqlList.add(scanValueListToSql(tableName, valueEntityList)));return sqlList;}/*** 拼接单一数据行值SQL** @param tableName       表名* @param valueEntityList 一行数据值列表* @return SQL*/private static String scanValueListToSql(String tableName, List<ValueEntity> valueEntityList) {StringBuilder stringBuffer = new StringBuilder();stringBuffer.append("INSERT INTO ").append(SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName)));stringBuffer.append(" VALUES( null,");for (ValueEntity element : valueEntityList) {if ((valueEntityList.indexOf(element) + 1) != valueEntityList.toArray().length) {stringBuffer.append(element.getValueOfString()).append(",");} else {stringBuffer.append(element.getValueOfString());}}stringBuffer.append(");");return stringBuffer.toString();}/*** 将列信息实体列表转SQL** @param columnEntityList 列信息实体列表* @return 处理完成得列属性SQL串*/private static String scanColumnListToSql(List<ColumnEntity> columnEntityList) {StringBuilder fieldSql = new StringBuilder();for (ColumnEntity element : columnEntityList) {fieldSql.append(SqlSpliceStringUtil.quotesHandle(element.getColumnName())).append(element.getColumnSqlInfo());}return fieldSql.toString();}
}

SqlDataProvider.java

public interface SqlDataProvider {/*** 执行非查询SQL** @param sql*/void executeSql(String sql);/*** 执行查询判断某些存在SQL** @param sql* @return 是否存在的对象 为空则不存在*/Object executeSqlAndGetReturn(String sql);/*** 执行查询SQL** @param sql* @return 结果的Json字符串*/String executeQuerySql(String sql);
}

SqlDataService.java

@Service
@Slf4j
public class SqlDataService implements SqlDataProvider {@Resourceprivate JdbcTemplate jdbcTemplate;@Qualifier("ReadCommittedTransactionTemplate")@Resourceprivate TransactionTemplate transactionTemplate;@Overridepublic void executeSql(String sql) {log.info("执行非查询操作SQL,开启事务执行：{}", sql);transactionTemplate.executeWithoutResult(status -> {try {jdbcTemplate.execute(sql);} catch (Exception e) {log.error("事务异常，开启回滚：{}", e.getMessage());status.setRollbackOnly();}});}@Overridepublic Object executeSqlAndGetReturn(String sql) {log.info("执行查询SQL：{}", sql);return jdbcTemplate.queryForObject(sql, Object.class);}@Overridepublic String executeQuerySql(String sql) {log.info("执行查询SQL：{}", sql);List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);if (CollectionUtils.isEmpty(mapList)) {return "";}List<String> result = new LinkedList<>();mapList.forEach(map -> result.add(JsonUtil.mapToJsonString(map)));return result.toString();}
}

具体的讲解就不说了，分享代码案例，感兴趣的同学可以gitee上搜git@gitee.com:huanglinchun/resolve_excel_auto.git