医疗行业下发的数据库标准不都是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);
//
//
//}
}