• Java&Excel：自动生成数据表并插入数据

    故事背景

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

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

    模块架构图

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

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

    代码构成

    基础实体：

    ColumnEntity.java
    1. @Data
    2. @EqualsAndHashCode
    3. public class ColumnEntity {
    4.     private String columnName;
    5.     private String columnSqlInfo;
    6.     public void clear(){
    7.         setColumnName(null);
    8.         setColumnSqlInfo(null);
    9.     }
    10. }
    ValueEntity.java
    1. @Data
    2. public class ValueEntity{
    3.     private String columnName;
    4.     /*数据拼接到SQL上也是字符串类型*/
    5.     private String valueOfString;
    6.     public void clear(){
    7.         setColumnName(null);
    8.         setValueOfString(null);
    9.     }
    10. }
    JsonResponse.java
    1. @Data
    2. @AllArgsConstructor
    3. @NoArgsConstructor
    4. public class JsonResponse {
    5.     private int code = -1;
    6.     private String message;
    7.     private String json;
    8.  
    9.     public static JsonResponse success(String json){
    10.         JsonResponse jsonResponse = new JsonResponse();
    11.         jsonResponse.setCode(0);
    12.         jsonResponse.setJson(json);
    13.         jsonResponse.setMessage("访问成功");
    14.         return jsonResponse;
    15.     }
    16.  
    17.     public static JsonResponse fail(String massage){
    18.         JsonResponse jsonResponse = new JsonResponse();
    19.         jsonResponse.setMessage(massage);
    20.         return jsonResponse;
    21.     }
    22. }

    api对前端提供特性：

    WebMvcConfiguration.java
    1. @Configuration
    2. @Slf4j
    3. public class WebMvcConfiguration implements WebMvcConfigurer , HandlerInterceptor {
    4.     @Override
    5.     public void addInterceptors(@NonNull InterceptorRegistry registry) {
    6.         registry.addInterceptor(this);
    7.         WebMvcConfigurer.super.addInterceptors(registry);
    8.     }
    9.  
    10.     @Override
    11.     public void addCorsMappings(@NonNull CorsRegistry registry) {
    12.         WebMvcConfigurer.super.addCorsMappings(registry);
    13.     }
    14.  
    15.     @Override
    16.     public void postHandle(@NonNull HttpServletRequest request,@NonNull  HttpServletResponse response,@NonNull  Object handler, ModelAndView modelAndView) throws Exception {
    17.         HandlerInterceptor.super.postHandle(request, response, handler, modelAndView);
    18.     }
    19.  
    20.     @Override
    21.     public boolean preHandle(HttpServletRequest request,@NonNull HttpServletResponse response,@NonNull Object handler) throws Exception {
    22.         String sql = request.getParameter("sql");
    23.         String tableName = request.getParameter("tableName");
    24.         if (StringUtils.isNotEmpty(sql)){
    25.             if (!sql.contains("test") || !tableName.contains("test")){
    26.                 log.error("过滤非法请求：{}",sql);
    27.                 return false;
    28.             }
    29.             if (sql.contains("DELETE") || sql.contains("delete") || sql.contains("UPDATE") || sql.contains("update")){
    30.                 log.error("过滤非法请求：{}",sql);
    31.                 return false;
    32.             }
    33.         }
    34.         return HandlerInterceptor.super.preHandle(request, response, handler);
    35.     }
    36.  
    37.     @Override
    38.     public void afterCompletion(@NonNull HttpServletRequest request,@NonNull HttpServletResponse response,@NonNull Object handler, Exception ex) throws Exception {
    39.         HandlerInterceptor.super.afterCompletion(request, response, handler, ex);
    40.     }
    41. }
    ExcelDataController.java
    1. @RestController
    2. @RequestMapping("/data")
    3. @CrossOrigin
    4. @Slf4j
    5. public class ExcelDataController {
    6.  
    7.     @Resource
    8.     private SqlDataProvider sqlDataProvider;
    9.  
    10.     @GetMapping("/handle/sql")
    11.     @ResponseBody
    12.     public JsonResponse handleData(@RequestParam String sql) {
    13.         String executed = sqlDataProvider.executeQuerySql(sql);
    14.         return Objects.equals("", executed)
    15.                 ? JsonResponse.fail("sql执行错误")
    16.                 : JsonResponse.success(executed);
    17.     }
    18. }
    ExcelResolveController.java

    1. @RestController
    2. @RequestMapping("/excel")
    3. @CrossOrigin
    4. @Slf4j
    5. public class ExcelResolveController {
    6.  
    7.     @Resource
    8.     private ExcelEntityService excelEntityService;
    9.  
    10.     @RequestMapping("/createAndInsert")
    11.     @ResponseBody
    12.     public void uploadExcel(@RequestParam("fileName") MultipartFile file) {
    13.         excelEntityService.createTable(file);
    14.         excelEntityService.insertEntity(file);
    15.     }
    16.  
    17.     @RequestMapping("/insert")
    18.     @ResponseBody
    19.     public void insertExcel(@RequestParam("fileName") MultipartFile file) {
    20.         excelEntityService.insertEntity(file);
    21.     }
    22.  
    23.     @RequestMapping("/drop")
    24.     @ResponseBody
    25.     public void delData(@RequestParam("fileName")String fileName){
    26.         excelEntityService.dropTable(fileName);
    27.     }
    28.  
    29. }
    ExcelEntityService.java
    1. public interface ExcelEntityService {
    2.     /**
    3.      * 创建数据表
    4.      *
    5.      * @param file 文件
    6.      */
    7.     void createTable(MultipartFile file);
    8.  
    9.     /**
    10.      * 插入数据实体
    11.      *
    12.      * @param file 文件
    13.      */
    14.     void insertEntity(MultipartFile file);
    15.  
    16.     /**
    17.      * 删除数据表
    18.      * @param fileName 文件名
    19.      */
    20.     void dropTable(String fileName);
    21. }
    ExcelEntityServiceImpl.java
    1. @Service
    2. @Slf4j
    3. @ConditionalOnBean({SqlDataProvider.class,SqlSpliceProvider.class})
    4. public class ExcelEntityServiceImpl implements ExcelEntityService {
    5.  
    6.     @Resource
    7.     private SqlDataProvider sqlDataProvider;
    8.  
    9.     @Resource
    10.     private SqlSpliceProvider spliceProvider;
    11.  
    12.     @Resource
    13.     private ReadExcelService readExcelService;
    14.  
    15.     @Override
    16.     public void createTable(MultipartFile file) {
    17.         String dropTableSql = spliceProvider.dropTableSql(file.getName());
    18.         log.info("删表SQL：{}",dropTableSql);
    19.         sqlDataProvider.executeSql(dropTableSql);
    20.         List excelColumnList = readExcelService.getExcelColumnList(file);
    21.         StringBuffer stringBuffer = spliceProvider.spliceCreateTableSql(excelColumnList, file.getName());
    22.         log.warn("建表SQL：{}", stringBuffer);
    23.         sqlDataProvider.executeSql(stringBuffer.toString());
    24.     }
    25.  
    26.     @Override
    27.     public void insertEntity(MultipartFile file) {
    28.         String existsTableSql = spliceProvider.existsTableSql(file.getName());
    29.         Object aReturn = sqlDataProvider.executeSqlAndGetReturn(existsTableSql);
    30.         if (Objects.nonNull(aReturn)) {
    31.             List excelColumnList = readExcelService.getExcelColumnList(file);
    32.             Map> excelRowDataMap = readExcelService.getExcelRowDataMap(file, excelColumnList);
    33.             List stringBuffer1 = spliceProvider.spliceInsertValueSql(excelRowDataMap, file.getName());
    34.             stringBuffer1.forEach(s -> sqlDataProvider.executeSql(s));
    35.         }else{
    36.             log.warn("不存在数据表：{}",file.getName());
    37.         }
    38.     }
    39.  
    40.     @Override
    41.     public void dropTable(String fileName) {
    42.         String dropTableSql = spliceProvider.dropTableSql(fileName);
    43.         log.info("删表SQL：{}",dropTableSql);
    44.         sqlDataProvider.executeSql(dropTableSql);
    45.     }
    46. }

     excel解析器：

    ReadExcelService.java
    1. public interface ReadExcelService {
    2.     /**
    3.      * 读取EXCEL的列属性列表
    4.      *
    5.      * @param file 文件
    6.      * @return 列属性实体列表（只含有列的属性）
    7.      */
    8.     List getExcelColumnList(MultipartFile file);
    9.  
    10.     /**
    11.      * 读取每一行的行实体列表，一个LIST为一行
    12.      *
    13.      * @param file             文件
    14.      * @param columnEntityList 列实体列表
    15.      * @return 全部的值MAP<行号 ， 行的属性值LIST>
    16.      */
    17.     Map> getExcelRowDataMap(MultipartFile file, List columnEntityList);
    18.  
    19. }
    ReadExcel.java
    1. @Slf4j
    2. @Service
    3. public class ReadExcel implements ReadExcelService {
    4.     private static final Integer LIMIT_SCAN_NUM = 500 * 1000;
    5.  
    6.     /**
    7.      * 根据文件名读取Excel文件获取列信息列表
    8.      *
    9.      * @param file 文件
    10.      * @return List<列实体>
    11.      */
    12.     @Override
    13.     public List getExcelColumnList(MultipartFile file) {
    14.         List list;
    15.         Workbook workbook = getWorkbook(file);
    16.         if (Objects.isNull(workbook)) {
    17.             return new LinkedList<>();
    18.         }
    19.         list = getExcelColumnList(workbook);
    20.         return list;
    21.     }
    22.  
    23.     /**
    24.      * 获取行数与对应行得值SQL实体列表
    25.      *
    26.      * @param file             工作薄文件
    27.      * @param columnEntityList 列信息对象
    28.      * @return map<行号 ， 行内每一个单元格得值SQL实体列表>
    29.      */
    30.     @Override
    31.     public Map> getExcelRowDataMap(MultipartFile file, List columnEntityList) {
    32.         Workbook workbook = getWorkbook(file);
    33.         if (Objects.isNull(workbook)) {
    34.             return new HashMap<>();
    35.         }
    36.         Sheet sheet = workbook.getSheetAt(0);
    37.         int lastRowNum = sheet.getLastRowNum();
    38.         Map> map = new HashMap<>();
    39.         if (lastRowNum <= LIMIT_SCAN_NUM) {
    40.             for (int i = 1; i <= lastRowNum; i++) {
    41.                 Row row = sheet.getRow(i);
    42.                 List list = new LinkedList<>();
    43.                 for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
    44.                     ValueEntity valueEntity = new ValueEntity();
    45.                     valueEntity.setColumnName(columnEntityList.get(j).getColumnName());
    46.                     valueEntity.setValueOfString(ExcelReadStringUtil.getValueSqlString(row.getCell(j)));
    47.                     list.add(valueEntity);
    48.                 }
    49.                 map.put(i, list);
    50.             }
    51.         }else{
    52.             throw new RuntimeException("扫描的Excel文件数据量超过限定值，请检查核定容量");
    53.         }
    54.         return map;
    55.     }
    56.  
    57.     /**
    58.      * 解析返回excel数据表第一行属性信息列表数据
    59.      *
    60.      * @param workbook 数据工作薄
    61.      * @return List<列实体>
    62.      */
    63.     private static List getExcelColumnList(Workbook workbook) {
    64.         /*默认取第一个工作表的第一行数据与第二行数据（第一行用来感知属性名称、第二行用来感知属性类型）*/
    65.         Sheet dataSheet = workbook.getSheetAt(0);
    66.         Row topRow = dataSheet.getRow(0);
    67.         Row typeRow = dataSheet.getRow(1);
    68.         if (topRow.getPhysicalNumberOfCells() != typeRow.getPhysicalNumberOfCells()) {
    69.             log.error("数据表列行与数据行列数不一致！退出解析，请整理数据表格式！");
    70.             throw new RuntimeException("DataSheet is error: com.runjing.resolve_excel_auto.excel.ReadExcel.getExcelColumnList(org.apache.poi.ss.usermodel.Workbook)");
    71.         }
    72.         List columnEntityList = new LinkedList<>();
    73.         for (int i = 0; i < topRow.getPhysicalNumberOfCells(); i++) {
    74.             ColumnEntity columnEntity = new ColumnEntity();
    75.             Cell nameCell = topRow.getCell(i);
    76.             Cell typeRowCell = typeRow.getCell(i);
    77.             columnEntity.setColumnName(ExcelReadStringUtil.transferPinYin(nameCell.getStringCellValue()));
    78.             columnEntity.setColumnSqlInfo(ExcelReadStringUtil.switchCellDataSqlInfo(typeRowCell));
    79.             columnEntityList.add(columnEntity);
    80.         }
    81.         return columnEntityList;
    82.     }
    83.  
    84.     /**
    85.      * 文件转换工作簿对象
    86.      *
    87.      * @param file excel文件
    88.      * @return 工作簿对象
    89.      */
    90.     private static Workbook getWorkbook(MultipartFile file) {
    91.         InputStream is = null;
    92.         try {
    93.             is = file.getInputStream();
    94.             Workbook workbook;
    95.             workbook = WorkbookFactory.create(is);
    96.             return workbook;
    97.         } catch (Exception ex) {
    98.             ex.printStackTrace();
    99.             return null;
    100.         } finally {
    101.             if (is != null) {
    102.                 try {
    103.                     is.close();
    104.                 } catch (IOException e) {
    105.                     e.printStackTrace();
    106.                 }
    107.             }
    108.         }
    109.     }
    110. }

    SQL拼接器：

    ExcelReadStringUtil.java
    1. public class ExcelReadStringUtil {
    2.  
    3.     /*获取构建表格数据属性SQL*/
    4.     public static String switchCellDataSqlInfo(Cell dataCell) {
    5.         return switch (dataCell.getCellType()) {
    6.             case NUMERIC -> " double default 0.00 ";
    7.             case STRING -> " varchar(100) default null";
    8.             case FORMULA -> " varchar default null";
    9.             case BOOLEAN -> " tinyint(1) default 0";
    10.             default -> " varchar(64) default null";
    11.         } + ",";
    12.     }
    13.  
    14.     /*获取单元格值SQL*/
    15.     public static String getValueSqlString(Cell dataCell) {
    16.         return switch (dataCell.getCellType()) {
    17.             case NUMERIC -> String.valueOf(dataCell.getNumericCellValue());
    18.             case STRING -> quotesHandle(dataCell.getStringCellValue());
    19.             case FORMULA -> quotesHandle(dataCell.getCellFormula());
    20.             case BOOLEAN -> transferBool(dataCell.getBooleanCellValue());
    21.             default -> "null";
    22.         };
    23.     }
    24.  
    25.     /**
    26.      * 单引号包裹字段
    27.      *
    28.      * @param fieldValue 字段值
    29.      * @return 包裹后字段串
    30.      */
    31.     public static String quotesHandle(String fieldValue) {
    32.         return "'" + fieldValue + "'";
    33.     }
    34.  
    35.     /**
    36.      * 布尔类型转换
    37.      *
    38.      * @param arg1 入参
    39.      * @return 转换值
    40.      */
    41.     public static String transferBool(Boolean arg1) {
    42.         return arg1 ? "0" : "1";
    43.     }
    44.     /**
    45.      * 将汉字串转成拼音串
    46.      *
    47.      * @param columnChineseName 汉字字段名
    48.      * @return 字段拼音
    49.      */
    50.     public static String transferPinYin(String columnChineseName) {
    51.         /*转换中文为简体拼音*/
    52.         return LanguageUtil.convertChineseLan2PinYinAbbreviation(columnChineseName, LanguageUtil.CHINESE_CHAR_REG_SIMPLIFIED);
    53.     }
    54. }
    SqlSpliceStringUtil.java
    1. public class SqlSpliceStringUtil {
    2.  
    3.     /**
    4.      * 反引号处理
    5.      *
    6.      * @param fieldName 字段名称
    7.      * @return 被反引号包裹得字段名称
    8.      */
    9.     public static String quotesHandle(String fieldName) {
    10.         return "`" + fieldName + "`";
    11.     }
    12.  
    13.     /**
    14.      * 将汉字串转成拼音串
    15.      *
    16.      * @param columnChineseName 汉字字段名
    17.      * @return 字段拼音
    18.      */
    19.     public static String transferPinYin(String columnChineseName) {
    20.         /*转换中文为简体拼音*/
    21.         return LanguageUtil.convertChineseLan2PinYinAbbreviation(columnChineseName, LanguageUtil.CHINESE_CHAR_REG_SIMPLIFIED);
    22.     }
    23. }
    LanguageUtil.java
    1. @Slf4j
    2. public class LanguageUtil {
    3.  
    4.     /**
    5.      * 定义输出格式
    6.      */
    7.     public static HanyuPinyinOutputFormat hpFormat = new HanyuPinyinOutputFormat();
    8.     /**
    9.      * 匹配所有东亚区的语言
    10.      */
    11.     public static String CHINESE_CHAR_REG_SOUTHEAST_ASIA ="^[\u2E80-\u9FFF]+$";
    12.     /**
    13.      * 匹配简体和繁体
    14.      */
    15.     public static String CHINESE_CHAR_REG_SIMPLIFIED_OR_TRADITIONAL ="^[\u4E00-\u9FFF]+$";
    16.     /**
    17.      * 匹配简体
    18.      */
    19.     public static String CHINESE_CHAR_REG_SIMPLIFIED ="[\u4E00-\u9FA5]+$";
    20.  
    21.     static{
    22.         // 大写格式输出
    23.         hpFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);
    24.         // 不需要语调输出
    25.         hpFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
    26.     }
    27.  
    28.     /***
    29.      * 将汉字转成拼音(取首字母或全拼)
    30.      * @param singleChar 中文字符
    31.      * @param full 是否全拼
    32.      * @return 转换后拼音
    33.      */
    34.     public static String convertChineseChar2Pinyin(String singleChar, boolean full,String regExp ) {
    35.  
    36.         StringBuffer sb = new StringBuffer();
    37.         if (singleChar == null || "".equals(singleChar.trim())) {
    38.             return "";
    39.         }
    40.         String pinyin = "";
    41.         for (int i = 0; i < singleChar.length(); i++) {
    42.             char unit = singleChar.charAt(i);
    43.             //是汉字，则转拼音
    44.             if (match(String.valueOf(unit), regExp))
    45.             {
    46.                 pinyin = convertSingleChineseChar2Pinyin(unit);
    47.                 if (full) {
    48.                     sb.append(pinyin);
    49.                 } else {
    50.                     sb.append(pinyin.charAt(0));
    51.                 }
    52.             } else {
    53.                 sb.append(unit);
    54.             }
    55.         }
    56.         return sb.toString();
    57.     }
    58.  
    59.     /***
    60.      * 将单个汉字转成拼音
    61.      * @param singleChar 中文汉字
    62.      * @return 拼音
    63.      */
    64.     private static String convertSingleChineseChar2Pinyin(char singleChar) {
    65.         String[] res;
    66.         StringBuffer sb = new StringBuffer();
    67.         try {
    68.             res = PinyinHelper.toHanyuPinyinStringArray(singleChar, hpFormat);
    69.             //对于多音字，只用第一个拼音
    70.             sb.append(res[0]);
    71.         } catch (Exception e) {
    72.             log.error("单个汉字转换成字符失败：{}",e.getMessage());
    73.             return "";
    74.         }
    75.         return sb.toString();
    76.     }
    77.  
    78.     /***
    79.      * @param str 源字符串
    80.      * @param regex 正则表达式
    81.      * @return 是否匹配
    82.      */
    83.     public static boolean match(String str, String regex) {
    84.         Pattern pattern = Pattern.compile(regex);
    85.         Matcher matcher = pattern.matcher(str);
    86.         return matcher.find();
    87.     }
    88.  
    89.     /**
    90.      * 汉字字符串的的首拼拼成字符串
    91.      * @param chineseLan 中文字符串
    92.      * @return 拼音字符串
    93.      */
    94.     public static String convertChineseLan2PinYinAbbreviation(String chineseLan,String regExp) {
    95.         String ret = "";
    96.         // 将汉字转换为字符数组
    97.         char[] charChineseLan = chineseLan.toCharArray();
    98.         try {
    99.             for (int i = 0; i < charChineseLan.length; i++) {
    100.                 if(String.valueOf(charChineseLan[i]).matches(regExp)) {
    101.                     // 如果字符是中文,则将中文转为汉语拼音（获取全拼则去掉红色的代码即可）
    102.                     ret += PinyinHelper.toHanyuPinyinStringArray(charChineseLan[i], hpFormat)[0].substring(0, 1);
    103.                 } else {
    104.                     // 如果字符不是中文,则不转换
    105.                     ret += charChineseLan[i];
    106.                 }
    107.             }
    108.         } catch (BadHanyuPinyinOutputFormatCombination e) {
    109.             log.error("获取汉字的的首拼失败：{}",e.getMessage());
    110.         }
    111.         return ret;
    112.     }
    113.  
    114.     /**
    115.      * 判断字符串中是否包含中文汉字
    116.      *
    117.      * @param content 字符串内容
    118.      * @return true至少包含1个
    119.      */
    120.     public static boolean hasChinese(CharSequence content) {
    121.         if (null == content) {
    122.             return false;
    123.         }
    124.         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]+";
    125.         Pattern pattern = Pattern.compile(regex);
    126.         return pattern.matcher(content).find();
    127.     }
    128.  
    129.     /**
    130.      * 判断字符串是否为中文汉字
    131.      *
    132.      * @param content 字符串内容
    133.      * @return true都是汉字
    134.      */
    135.     public static boolean isChinese(CharSequence content) {
    136.         if (null == content) {
    137.             return false;
    138.         }
    139.         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]+";
    140.         Pattern pattern = Pattern.compile(regex);
    141.         return pattern.matcher(content).matches();
    142.     }
    143. }
    JsonUtil.java
    1. public class JsonUtil {
    2.  
    3.     private final static String arg = "\"";
    4.  
    5.     /**
    6.      * Map转成JSON字符串
    7.      *
    8.      * @param map
    9.      * @return JSON
    10.      */
    11.     public static String mapToJsonString(Map map) {
    12.         return CollectionUtils.isEmpty(map) ? "" : JSONObject.toJSONString(map).replace(arg,"'");
    13.     }
    14.  
    15. }

    SQL配置与执行：

    SqlConfiguration.java
    1. @Component("SqlConfiguration")
    2. @ConfigurationProperties(prefix = "jdbc-config")
    3. @Data
    4. public class SqlConfiguration {
    5.     private String driver;
    6.     private String url;
    7.     private String userName;
    8.     private String password;
    9. }
    SqlDataSourceConfiguration.java
    1. @Configuration
    2. @ConditionalOnBean(SqlConfiguration.class)
    3. @Slf4j
    4. public class SqlDataSourceConfiguration {
    5.     @Resource
    6.     private SqlConfiguration sqlConfiguration;
    7.  
    8.     @Bean("DriverManagerDataSource")
    9.     @Scope(value = "singleton")
    10.     public DriverManagerDataSource getDataSource(){
    11.         DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
    12.         driverManagerDataSource.setDriverClassName(sqlConfiguration.getDriver());
    13.         driverManagerDataSource.setUrl(sqlConfiguration.getUrl());
    14.         driverManagerDataSource.setUsername(sqlConfiguration.getUserName());
    15.         driverManagerDataSource.setPassword(sqlConfiguration.getPassword());
    16.         log.info("扫描生成自定义配置JDBC数据源：{}",sqlConfiguration.getUrl());
    17.         return driverManagerDataSource;
    18.     }
    19. }
    JdbcTransactionManagerConfiguration.java
    1. @Configuration
    2. @ConditionalOnBean({SqlDataSourceConfiguration.class})
    3. @Slf4j
    4. public class JdbcTransactionManagerConfiguration {
    5.  
    6.     @Resource
    7.     private DriverManagerDataSource driverManagerDataSource;
    8.     @Bean("JdbcTransactionManager")
    9.     @Scope("singleton")
    10.     public JdbcTransactionManager getJdbcTransactionManager(){
    11.         JdbcTransactionManager jdbcTransactionManager = new JdbcTransactionManager();
    12.         log.info("开始配置JDBC事务管理者");
    13.         jdbcTransactionManager.setDataSource(driverManagerDataSource);
    14.         jdbcTransactionManager.setRollbackOnCommitFailure(true);
    15.         jdbcTransactionManager.setFailEarlyOnGlobalRollbackOnly(true);
    16.         jdbcTransactionManager.setGlobalRollbackOnParticipationFailure(true);
    17.         return jdbcTransactionManager;
    18.     }
    19.  
    20. }
    TransactionTemplateConfiguration.java
    1. @Configuration
    2. @Slf4j
    3. public class TransactionTemplateConfiguration {
    4.     @Resource
    5.     private JdbcTransactionManager jdbcTransactionManager;
    6.  
    7.     @Bean("ReadCommittedTransactionTemplate")
    8.     @Scope("singleton")
    9.     @ConditionalOnBean({JdbcTransactionManager.class})
    10.     @Lazy
    11.     public TransactionTemplate getReadCommittedTransactionTemplate(){
    12.         TransactionTemplate transactionTemplate = new TransactionTemplate();
    13.         log.info("生成事务模板，注入事务管理器，设置事务隔离级别为读已提交");
    14.         transactionTemplate.setTransactionManager(jdbcTransactionManager);
    15.         transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
    16.         return transactionTemplate;
    17.     }
    18.  
    19.     @Bean("ReadUnCommittedTransactionTemplate")
    20.     @Scope("singleton")
    21.     @ConditionalOnBean({JdbcTransactionManager.class})
    22.     @Lazy
    23.     public TransactionTemplate getReadUnCommittedTransactionTemplate(){
    24.         TransactionTemplate transactionTemplate = new TransactionTemplate();
    25.         log.info("生成事务模板，注入事务管理器，设置事务隔离级别为读未提交");
    26.         transactionTemplate.setTransactionManager(jdbcTransactionManager);
    27.         transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
    28.         return transactionTemplate;
    29.     }
    30.  
    31.     @Bean("RepeatableReadTransactionTemplate")
    32.     @Scope("singleton")
    33.     @ConditionalOnBean({JdbcTransactionManager.class})
    34.     @Lazy
    35.     public TransactionTemplate getRepeatableReadTransactionTemplate(){
    36.         TransactionTemplate transactionTemplate = new TransactionTemplate();
    37.         log.info("生成事务模板，注入事务管理器，设置事务隔离级别为可重复读");
    38.         transactionTemplate.setTransactionManager(jdbcTransactionManager);
    39.         transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);
    40.         return transactionTemplate;
    41.     }
    42.  
    43.     @Bean("SerializableTransactionTemplate")
    44.     @Scope("singleton")
    45.     @ConditionalOnBean({JdbcTransactionManager.class})
    46.     @Lazy
    47.     public TransactionTemplate getSerializableTransactionTemplate(){
    48.         TransactionTemplate transactionTemplate = new TransactionTemplate();
    49.         log.info("生成事务模板，注入事务管理器，设置事务隔离级别为可串行化");
    50.         transactionTemplate.setTransactionManager(jdbcTransactionManager);
    51.         transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE);
    52.         return transactionTemplate;
    53.     }
    54.  
    55. }
    SqlSpliceProvider.java
    1. public interface SqlSpliceProvider {
    2.     /**
    3.      * 拼接建表SQL
    4.      *
    5.      * @param columnEntityList excel的列属性列表
    6.      * @param tableName        表名
    7.      * @return SQL
    8.      */
    9.     StringBuffer spliceCreateTableSql(List columnEntityList, String tableName);
    10.  
    11.     /**
    12.      * 拼接删表SQL
    13.      *
    14.      * @param tableName 表名
    15.      * @return SQL
    16.      */
    17.     String dropTableSql(String tableName);
    18.  
    19.     /**
    20.      * 拼接判断表存在SQL
    21.      *
    22.      * @param tableName 表名
    23.      * @return SQL
    24.      */
    25.     String existsTableSql(String tableName);
    26.  
    27.     /**
    28.      * 拼接插值SQL列表循环执行即可
    29.      *
    30.      * @param map       值map<行号，对应行的值LIST>
    31.      * @param tableName 表名
    32.      * @return SQL
    33.      */
    34.     List spliceInsertValueSql(Map> map, String tableName);
    35.  
    36. }
    SqlSplicer.java
    1. @Service
    2. public class SqlSplicer implements SqlSpliceProvider {
    3.  
    4.     /**
    5.      * 拼接建表SQL
    6.      *
    7.      * @param columnEntityList 列信息实体列表
    8.      * @param tableName        表格名称
    9.      * @return SQL
    10.      */
    11.     @Override
    12.     public StringBuffer spliceCreateTableSql(List columnEntityList, String tableName) {
    13.         StringBuffer stringBuffer = new StringBuffer();
    14.         stringBuffer.append("CREATE TABLE ").append(SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName))).append(" ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自动主键',");
    15.         /*扫描属性列表，填充建表SQL*/
    16.         stringBuffer.append(scanColumnListToSql(columnEntityList));
    17.         stringBuffer.append(" PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ");
    18.         return stringBuffer;
    19.     }
    20.  
    21.     /**
    22.      * 删除数据表，防止表重复
    23.      *
    24.      * @param tableName 表名称
    25.      * @return SQL
    26.      */
    27.     @Override
    28.     public  String dropTableSql(String tableName) {
    29.         return "DROP TABLE IF EXISTS " + SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName));
    30.     }
    31.  
    32.     /**
    33.      * 查询数据表是否存在
    34.      * @param tableName 表名称
    35.      * @return SQL
    36.      */
    37.     @Override
    38.     public  String existsTableSql(String tableName) {
    39.         return "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = " + SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName));
    40.     }
    41.  
    42.     /**
    43.      * 拼接插值SQL(单插入SQL集合)
    44.      *
    45.      * @param map       值实体列表Map
    46.      * @param tableName 表名
    47.      * @return 插值SQL
    48.      */
    49.     @Override
    50.     public  List spliceInsertValueSql(Map> map, String tableName) {
    51.         List sqlList = new LinkedList<>();
    52.         map.values().forEach(valueEntityList -> sqlList.add(scanValueListToSql(tableName, valueEntityList)));
    53.         return sqlList;
    54.     }
    55.  
    56.  
    57.     /**
    58.      * 拼接单一数据行值SQL
    59.      *
    60.      * @param tableName       表名
    61.      * @param valueEntityList 一行数据值列表
    62.      * @return SQL
    63.      */
    64.     private static String scanValueListToSql(String tableName, List valueEntityList) {
    65.         StringBuilder stringBuffer = new StringBuilder();
    66.         stringBuffer.append("INSERT INTO ").append(SqlSpliceStringUtil.quotesHandle(SqlSpliceStringUtil.transferPinYin(tableName)));
    67.         stringBuffer.append(" VALUES( null,");
    68.         for (ValueEntity element : valueEntityList) {
    69.             if ((valueEntityList.indexOf(element) + 1) != valueEntityList.toArray().length) {
    70.                 stringBuffer.append(element.getValueOfString()).append(",");
    71.             } else {
    72.                 stringBuffer.append(element.getValueOfString());
    73.             }
    74.         }
    75.         stringBuffer.append(");");
    76.         return stringBuffer.toString();
    77.     }
    78.  
    79.     /**
    80.      * 将列信息实体列表转SQL
    81.      *
    82.      * @param columnEntityList 列信息实体列表
    83.      * @return 处理完成得列属性SQL串
    84.      */
    85.     private static String scanColumnListToSql(List columnEntityList) {
    86.         StringBuilder fieldSql = new StringBuilder();
    87.         for (ColumnEntity element : columnEntityList) {
    88.             fieldSql.append(SqlSpliceStringUtil.quotesHandle(element.getColumnName())).append(element.getColumnSqlInfo());
    89.         }
    90.         return fieldSql.toString();
    91.     }
    92. }
    SqlDataProvider.java
    1. public interface SqlDataProvider {
    2.     /**
    3.      * 执行非查询SQL
    4.      *
    5.      * @param sql
    6.      */
    7.     void executeSql(String sql);
    8.  
    9.     /**
    10.      * 执行查询判断某些存在SQL
    11.      *
    12.      * @param sql
    13.      * @return 是否存在的对象 为空则不存在
    14.      */
    15.     Object executeSqlAndGetReturn(String sql);
    16.  
    17.     /**
    18.      * 执行查询SQL
    19.      *
    20.      * @param sql
    21.      * @return 结果的Json字符串
    22.      */
    23.     String executeQuerySql(String sql);
    24. }
    SqlDataService.java
    1. @Service
    2. @Slf4j
    3. public class SqlDataService implements SqlDataProvider {
    4.     @Resource
    5.     private JdbcTemplate jdbcTemplate;
    6.  
    7.     @Qualifier("ReadCommittedTransactionTemplate")
    8.     @Resource
    9.     private TransactionTemplate transactionTemplate;
    10.  
    11.  
    12.     @Override
    13.     public void executeSql(String sql) {
    14.         log.info("执行非查询操作SQL,开启事务执行：{}", sql);
    15.         transactionTemplate.executeWithoutResult(status -> {
    16.             try {
    17.                 jdbcTemplate.execute(sql);
    18.             } catch (Exception e) {
    19.                 log.error("事务异常，开启回滚：{}", e.getMessage());
    20.                 status.setRollbackOnly();
    21.             }
    22.         });
    23.     }
    24.  
    25.  
    26.     @Override
    27.     public Object executeSqlAndGetReturn(String sql) {
    28.         log.info("执行查询SQL：{}", sql);
    29.         return jdbcTemplate.queryForObject(sql, Object.class);
    30.     }
    31.  
    32.  
    33.     @Override
    34.     public String executeQuerySql(String sql) {
    35.         log.info("执行查询SQL：{}", sql);
    36.         List> mapList = jdbcTemplate.queryForList(sql);
    37.         if (CollectionUtils.isEmpty(mapList)) {
    38.             return "";
    39.         }
    40.         List result = new LinkedList<>();
    41.         mapList.forEach(map -> result.add(JsonUtil.mapToJsonString(map)));
    42.         return result.toString();
    43.     }
    44. }

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

  • 相关阅读:
    ENSP常用指令
    使用 SAP UI5 ABAP Repository 部署本地 SAP UI5 应用到 ABAP 服务器的单步调试
    数据压缩STC稀疏三元压缩算法复现
    【动手学深度学习】--文本预处理
    Excel 导出打不开
    文件夹复制功能的实现
    【Unity】万人同屏, 从入门到放弃之——多线程RVO避障
    【C++】类和对象 从入门到超神 (上)
    初识进程~
    vue 请求代理 proxy
  • 原文地址：https://blog.csdn.net/m0_59588838/article/details/132612093