杭州微信网站开发,网络营销策划书的范文,泉州网站建设开发,省运会官方网站建设故事背景
出于好奇#xff0c;当下扫描excel读取数据进数据库 or 导出数据库数据组成excel的功能层出不穷#xff0c;代码也是前篇一律#xff0c;poi或者easy excel两种SDK的二次利用带来了各种封装方法。
那么为何不能直接扫描excel后根据列的属性名与行数据的属性建立S…故事背景
出于好奇当下扫描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.javaRestController
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);ListColumnEntity 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)) {ListColumnEntity excelColumnList readExcelService.getExcelColumnList(file);MapInteger, ListValueEntity excelRowDataMap readExcelService.getExcelRowDataMap(file, excelColumnList);ListString 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 列属性实体列表只含有列的属性*/ListColumnEntity getExcelColumnList(MultipartFile file);/*** 读取每一行的行实体列表一个LIST为一行** param file 文件* param columnEntityList 列实体列表* return 全部的值MAP行号 行的属性值LIST*/MapInteger, ListValueEntity getExcelRowDataMap(MultipartFile file, ListColumnEntity 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 ListColumnEntity getExcelColumnList(MultipartFile file) {ListColumnEntity 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 MapInteger, ListValueEntity getExcelRowDataMap(MultipartFile file, ListColumnEntity columnEntityList) {Workbook workbook getWorkbook(file);if (Objects.isNull(workbook)) {return new HashMap();}Sheet sheet workbook.getSheetAt(0);int lastRowNum sheet.getLastRowNum();MapInteger, ListValueEntity map new HashMap();if (lastRowNum LIMIT_SCAN_NUM) {for (int i 1; i lastRowNum; i) {Row row sheet.getRow(i);ListValueEntity 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 ListColumnEntity 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));}ListColumnEntity 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(MapString, 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(ListColumnEntity 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*/ListString spliceInsertValueSql(MapInteger, ListValueEntity map, String tableName);}
SqlSplicer.java
Service
public class SqlSplicer implements SqlSpliceProvider {/*** 拼接建表SQL** param columnEntityList 列信息实体列表* param tableName 表格名称* return SQL*/Overridepublic StringBuffer spliceCreateTableSql(ListColumnEntity 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; );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 ListString spliceInsertValueSql(MapInteger, ListValueEntity map, String tableName) {ListString 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, ListValueEntity 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(ListColumnEntity 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);ListMapString, Object mapList jdbcTemplate.queryForList(sql);if (CollectionUtils.isEmpty(mapList)) {return ;}ListString result new LinkedList();mapList.forEach(map - result.add(JsonUtil.mapToJsonString(map)));return result.toString();}
}
具体的讲解就不说了分享代码案例感兴趣的同学可以gitee上搜gitgitee.com:huanglinchun/resolve_excel_auto.git
