根据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);
    //
    //
    //}


}

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
### 回答1: 好的,首先你需要提供这些字段信息,例如字段名称、数据类型、是否非空等。 假设你有以下字段信息: - id:数字类型,主键,自增长 - name:字符串类型,非空 - age:数字类型,非空 - gender:字符串类型,可以为空 - address:字符串类型,可以为空 则可以使用以下 SQL 语句来建名为 `person` 的表: ``` CREATE TABLE person ( id serial PRIMARY KEY, name VARCHAR NOT NULL, age INTEGER NOT NULL, gender VARCHAR, address VARCHAR ); ``` 这条语句会建一个名为 `person` 的表,其有五个字段,分别是 `id`、`name`、`age`、`gender` 和 `address`。`id` 字段是数字类型,并且是主键,设置为自增长。`name` 和 `age` 字段是字符串类型,并且设置为非空。`gender` 和 `address` 字段是字符串类型,并且可以为空。 请注意,这只是一个示例,实际上可能会有更多的字段和更复杂的表结构。 希望这对你有帮助! ### 回答2: 生成 PostgreSQL建表语句需要根据给定的字段进行处理。以下是一个示例的程序脚本,用于根据给定的字段生成 PostgreSQL建表语句: ```python def generate_create_table_statement(fields): statement = "CREATE TABLE table_name(\n" for field in fields: statement += f"{field['name']} {field['type']}" if field['length']: statement += f"({field['length']})" if field['primary_key']: statement += " PRIMARY KEY" if not field['nullable']: statement += " NOT NULL" statement += ",\n" statement = statement.rstrip(",\n") # 移除最后一个逗号和换行符 statement += ");" return statement # 示例字段 fields = [ {"name": "id", "type": "INT", "length": None, "primary_key": True, "nullable": False}, {"name": "name", "type": "VARCHAR", "length": 50, "primary_key": False, "nullable": True}, {"name": "age", "type": "INT", "length": None, "primary_key": False, "nullable": True} ] create_table_statement = generate_create_table_statement(fields) print(create_table_statement) ``` 以上代码,我们定义了一个 `generate_create_table_statement()` 函数,该函数接受字段列表作为参数,将这些字段逐个处理拼接到 `CREATE TABLE` 语句字段的属性包括字段名 `name`、字段类型 `type`、字段长度 `length`、是否为主键 `primary_key`,以及是否可为空 `nullable`。 使用示例字段生成的 PostgreSQL 建表语句如下所示: ``` CREATE TABLE table_name( id INT PRIMARY KEY NOT NULL, name VARCHAR(50), age INT ); ``` 你可以将字段列表替换为自己的数据,并调用函数 `generate_create_table_statement()` 来生成自定义的 PostgreSQL 建表语句。 ### 回答3: 在生成postgresql建表语句之前,需要了解这些字段的具体信息,包括字段名称、数据类型、长度、约束条件等。以下是一个例子,假设我们有以下字段信息字段1:用户ID(user_id),数据类型为整型(int),长度为10,主键(PRIMARY KEY); 字段2:用户名(username),数据类型为字符串型(varchar),长度为50,唯一性约束(UNIQUE); 字段3:年龄(age),数据类型为整型(int),长度为2,非空约束(NOT NULL); 字段4:性别(gender),数据类型为字符串型(varchar),长度为10; 字段5:注册日期(register_date),数据类型为日期型(date)。 根据以上字段生成postgresql建表语句如下: CREATE TABLE 表名 ( user_id INT(10) PRIMARY KEY, username VARCHAR(50) UNIQUE, age INT(2) NOT NULL, gender VARCHAR(10), register_date DATE ); 以上语句,CREATE TABLE表示建一张表,表名可以根据实际情况进行命名。括号内每一行表示一个字段,包括字段名称和字段的数据类型及约束条件。其,INT表示整型,VARCHAR表示字符串型,PRIMARY KEY表示主键,UNIQUE表示唯一性约束,NOT NULL表示非空约束,DATE表示日期型。 根据具体的字段信息,可以根据上述格式进行调整和修改,以生成适合的postgresql建表语句
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

十方天士

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值