根据excel字段信息动态生成建表语句sql

医疗行业下发的数据库标准不都是sql脚本，有的是excel统计的。所以需要根据excel，数据库类型生成脚本

工程所用的工具：hutool的excel工具类（本来想用easyExcel的，但是本身的项目里有jar冲突不能执行，鬼知道为啥😂）有个坑后面会说

一、Demo

package cn.com.rdhl.project.referdata.mdDataset.service.impl;

import cn.com.rdhl.common.constant.PlatConstant;
import cn.com.rdhl.common.exception.base.BaseException;
import cn.com.rdhl.common.utils.FileUploadUtil;
import cn.com.rdhl.common.utils.security.ShiroUtils;
import cn.com.rdhl.project.referdata.mdDataset.domain.MdDataset;
import cn.com.rdhl.project.referdata.mdDataset.domain.MdDatasetVO;
import cn.com.rdhl.project.referdata.mdDataset.excel.ExcelConstant;
import cn.com.rdhl.project.referdata.mdDataset.excel.ExcelDomain;
import cn.com.rdhl.project.referdata.mdDataset.excel.ExcelMatchEnum;
import cn.com.rdhl.project.referdata.mdDataset.excel.ImportDomain;
import cn.com.rdhl.project.referdata.mdDataset.mapper.MdDatasetMapper;
import cn.com.rdhl.project.referdata.mdDataset.service.IMdDatasetService;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * 数据集管理 服务层实现
 *
 * @author panx
 * @date 2020-06-03 09:57:48
 */
@Service
@Slf4j
public class Demo {



    public String generateTableSql(ImportDomain importDomain) throws IOException {
        final String type = importDomain.getType();
        Assert.notEmpty(type, "数据库类型不能为空！ ");
        // 选择数据类型
        Map<String, String> dataTypeMap = new HashMap<>(16);
        if (type.equalsIgnoreCase(ExcelConstant.IS_ORACLE)) {
            dataTypeMap.put("S", ExcelMatchEnum.OracleEnum.STRING.getValue());
            dataTypeMap.put("D", ExcelMatchEnum.OracleEnum.DATE.getValue());
            dataTypeMap.put("N", ExcelMatchEnum.OracleEnum.NUMBER.getValue());
            dataTypeMap.put("T", ExcelMatchEnum.OracleEnum.TEXT.getValue());
        } else if (type.equalsIgnoreCase(ExcelConstant.IS_MYSQL)) {
            dataTypeMap.put("S", ExcelMatchEnum.MysqlEnum.STRING.getValue());
            dataTypeMap.put("D", ExcelMatchEnum.MysqlEnum.DATE.getValue());
            dataTypeMap.put("N", ExcelMatchEnum.MysqlEnum.NUMBER.getValue());
            dataTypeMap.put("T", ExcelMatchEnum.MysqlEnum.TEXT.getValue());
        }


        //hutool excel工具无读取全部sheet方法，只能折中循环读取
        //遇到无sheet情况，直接捕捉异常退出循环
        List<ExcelReader> readerList = new ArrayList<>();
        for (int i = 0; i < ExcelConstant.MAX_EXCEL_NUM; i++) {
            try {
                InputStream in = importDomain.getExcelFile().getInputStream();
                ExcelReader reader = ExcelUtil.getReader(in, i);
                readerList.add(reader);
            } catch (Exception e) {
                log.error("该sheet{}无值", i + 1);
                break;
            }
        }

        // 组装后的sql集合
        StringBuilder builder = new StringBuilder();
        for (ExcelReader reader : readerList) {
            String sql = buildUpSql(dataTypeMap, reader);
            builder.append(sql).append(System.getProperty("line.separator"));
        }

        final byte[] data = generatorSql(builder.toString());
        // 上传到文件服务器
        ByteArrayInputStream in = new ByteArrayInputStream(data);
        final String uploadUrl = FileUploadUtil.uploadFileToServer(in, StrUtil.format("V{}.zip", ExcelConstant.FILE_NAME), ExcelConstant.FILE_DIRECTORY,
                ShiroUtils.getLoginUser().getUserId());
        return uploadUrl;
    }

    /**
     * 组装sql
     *
     * @param dataTypeMap
     * @param reader
     */
    private String buildUpSql(Map<String, String> dataTypeMap, ExcelReader reader) {
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_1.getValue(), ExcelMatchEnum.MATCH_1.getKey());
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_2.getValue(), ExcelMatchEnum.MATCH_2.getKey());
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_3.getValue(), ExcelMatchEnum.MATCH_3.getKey());
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_4.getValue(), ExcelMatchEnum.MATCH_4.getKey());
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_5.getValue(), ExcelMatchEnum.MATCH_5.getKey());
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_7.getValue(), ExcelMatchEnum.MATCH_7.getKey());
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_8.getValue(), ExcelMatchEnum.MATCH_8.getKey());
        reader.addHeaderAlias(ExcelMatchEnum.MATCH_9.getValue(), ExcelMatchEnum.MATCH_9.getKey());
        // 读取表名信息
        final List<List<Object>> read = reader.read(1, 2);
        Assert.notEmpty(read, "表名信息不能为空");
        // 表名
        String tableName = (String) read.get(0).get(1);
        if (StringUtils.isBlank(tableName)) {
            throw new BaseException("表名不能为空");
        }
        //表中文名
        String tableChineseName = (String) read.get(0).get(0);

        // 读取标题栏后的数据
        final List<ExcelDomain> excelDomains = reader.read(3, 4, ExcelDomain.class);
        // 建表语句
        String sql;
        // 字段->中文注释 后续用于注释的生成
        Map<String, String> map = new HashMap<>(16);
        // 需要定义为主键的集合
        List<String> keyList = new ArrayList<>(16);
        // 拼接字符串
        StringBuilder sb = new StringBuilder();
        sb.append("-- ").append(tableChineseName).append(System.getProperty("line.separator")).append("CREATE TABLE ").append(tableName).append(" (").append(System.getProperty("line.separator"));
        for (ExcelDomain d : excelDomains) {
            sb.append(d.getField()).append("  ");
            if (d.getFieldType().contains("S")) {
                final Matcher matcher = ExcelConstant.PATTERN.matcher(d.getFieldFormat());
                final String trim = matcher.replaceAll("").trim();
                // 与阀值比较大小，如果大于阀值将将数据类型转为文本类型
                BigDecimal bigDecimal = new BigDecimal(trim);
                if (bigDecimal.compareTo(new BigDecimal(ExcelConstant.MAX_NUM)) > 0) {
                    sb.append(dataTypeMap.get("T")).append(" ");
                } else {
                    sb.append(dataTypeMap.get("S")).append("(").append(trim).append(")").append(" ");
                }
            } else if (d.getFieldType().contains("D")) {
                sb.append(dataTypeMap.get("D")).append(" ");
            } else if ("N".equals(d.getFieldType())) {
                String[] array = d.getFieldFormat().substring(1).split(",");
                // 整体部分表达式
                String prefix = array[0];
                final String prefixMaxValue = countMaxValue(prefix, ExcelConstant.PATTERN.matcher(prefix));
                // 小数部分表达式
                String suffix = "0";
                if (array.length > 1) {
                    suffix = array[1];
                }

                final String suffixMaxValue = countMaxValue(suffix, ExcelConstant.PATTERN.matcher(suffix));
                sb.append(dataTypeMap.get("N")).append("(").append(prefixMaxValue).append(",").append(suffixMaxValue).append(")").append(" ");
            } else {
                sb.append(dataTypeMap.get("S")).append("(10)").append(" ");
            }

            //是否主键
            if (ExcelConstant.IS_PRIMARY_KEY.equals(d.getPrimaryFlag())) {
                keyList.add(d.getField());
                //如果是主键则为必填项
                d.setRequiredFlag(ExcelConstant.IS_REQUIRED);
            }
            //是否必填
            if (ExcelConstant.IS_REQUIRED.equals(d.getRequiredFlag())) {
                sb.append(" NOT NULL ");
            }
            sb.append(",").append(System.getProperty("line.separator"));
            // 如果有值域内容则添加于中文符号【】中
            String range = CharSequenceUtil.isNotBlank(d.getRange()) ? CharSequenceUtil.format("【{}】", d.getRange()) : "";
            map.put(d.getField(), d.getFieldName() + range);
        }
        if (CollUtil.isNotEmpty(keyList)) {
            sb.append("CONSTRAINT ").append("PK_").append(tableName).append("  PRIMARY KEY ")
                    .append("(").append(StringUtils.join(keyList.toArray(), ",")).append("));").append(System.getProperty("line.separator"));
        }
        sql = sb.toString();
        // 组装中文注释
        StringBuilder builder = new StringBuilder(sql);
        for (Map.Entry<String, String> entry : map.entrySet()) {
            builder.append(System.getProperty("line.separator")).append("COMMENT ON COLUMN  ").append(tableName).append(".").append(entry.getKey()).append(" IS '").append(entry.getValue()).append("' ;");
        }
        builder.append(System.getProperty("line.separator")).append("COMMENT ON TABLE  ").append(tableName).append(" IS '").append(tableChineseName).append("' ;").append(System.getProperty("line.separator"));
        return builder.toString();
    }

    /**
     * 截取最大值
     *
     * @param prefix
     * @param matcher
     * @return
     */
    private static String countMaxValue(String prefix, Matcher matcher) {
        if (prefix.contains(StrUtil.DOUBLE_DOT)) {
            String[] preArr = prefix.split("[.][.]");
            return preArr[1];
        } else {
            return matcher.replaceAll("").trim();
        }
    }

    public byte[] generatorSql(String sqlList) {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        ZipOutputStream zip = new ZipOutputStream(outputStream);
        // 生成脚本
        try {
            // 添加到zip
            zip.putNextEntry(new ZipEntry(ExcelConstant.FILE_NAME + ".sql"));
            IOUtils.write(sqlList, zip, "UTF-8");
            zip.closeEntry();
        } catch (IOException e) {
            e.printStackTrace();
        }
        IOUtils.closeQuietly(zip);
        return outputStream.toByteArray();
    }

    //public static void main(String[] args) throws FileNotFoundException {
    //    List<ExcelReader> readerList = new ArrayList<>();
    //    final File file = FileUtil.file("C:\\Users\\Administrator\\Desktop\\datasetReverseGeneration.xlsx");
    //
    //    ExcelReader reader;
    //
    //    for (int i = 0; i < 5; i++) {
    //        try {
    //            FileInputStream input = new FileInputStream(file);
    //            reader = ExcelUtil.getReader(input, i);
    //            readerList.add(reader);
    //        } catch (Exception e) {
    //            log.error("该sheet{}无值", i);
    //            break;
    //        }
    //    }
    //
    //    System.out.println(readerList);
    //
    //
    //}


}