Excel表结构模板生成MySql建表语句

Excel表结构模板生成MySql建表语句

• • Excel表结构模板
  • Java 代码
  • 生成的MySql建表语句

Excel表结构模板

Java 代码

依赖EasyExcel

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.8</version> </dependency> @PostMapping("/importExcel")public String importExcel(@RequestBody MultipartFile file) {FileOutputStream fos = null;OutputStreamWriter osw = null;try {// 读取文件流InputStream inputStream = file.getInputStream();List<ImportExcel> list = EasyExcel.read(inputStream).head(ImportExcel.class).sheet().doReadSync();StringBuilder sb = new StringBuilder("DROP TABLE IF EXISTS ``;\n" +"CREATE TABLE `` (\n");for (int i = 0; i < list.size(); i++) {// 字段名sb.append("`").append(list.get(i).getFiledName()).append("` ");// 字段类型和长度if (list.get(i).getDataType().contains("CHAR")) {sb.append(list.get(i).getDataType()).append("(").append(list.get(i).getLength()).append(") ");sb.append("CHARACTER SET utf8 COLLATE utf8_general_ci ");} else {sb.append(list.get(i).getDataType()).append(" ");}// 非空与否if (list.get(i).getNotNull().equals("Y")) {sb.append("NOT NULL ");}// 是否自增if (StringUtils.equals("fd_id", list.get(i).getFiledName())) {sb.append("AUTO_INCREMENT ");}// 默认值if (!StringUtils.equals(list.get(i).getDefaultValue(), "自增序列")) {if (list.get(i).getDataType().contains("CHAR")) {sb.append("DEFAULT '").append(list.get(i).getDefaultValue() == null ? "" : list.get(i).getDefaultValue()).append("' ");} else if (list.get(i).getDataType().contains("INT") && list.get(i).getDefaultValue() != null) {sb.append("DEFAULT ").append(list.get(i).getDefaultValue()).append(" ");}}// 注释String remark = list.get(i).getRemark();String replace = remark.replace("\n", " ");sb.append("COMMENT '").append(replace).append("'");sb.append(",\n");}// 索引List<ImportExcel> keyFd = list.stream().filter(data -> StringUtils.equals("UNIQUE", data.getKey())).collect(Collectors.toList());StringBuilder keySb = new StringBuilder("PRIMARY KEY (`fd_id`)");if (!CollectionUtils.isEmpty(keyFd)) {keySb.append(",\n");for (int i = 0; i < keyFd.size(); i++) {keySb.append("UNIQUE KEY `").append(keyFd.get(i).getFiledName()).append("` (`").append(keyFd.get(i).getFiledName()).append("`) USING BTREE");if (i != keyFd.size() - 1) {keySb.append(",");}keySb.append("\n");}}sb.append(keySb);sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';");System.out.println(sb);// 写入SQL脚本文件String saveFile = "C:\\Users\\Administrator\\Desktop\\ibuilding.sql";File sqlFile = new File(saveFile);if (!sqlFile.exists()) {fos = new FileOutputStream(sqlFile);} else {fos = new FileOutputStream(sqlFile, true);}osw = new OutputStreamWriter(fos, StandardCharsets.UTF_8);osw.write(sb.toString());osw.write("\r\n\n");} catch (IOException e) {return "文件读取失败";} finally {try {if (osw != null) {osw.close();}} catch (IOException e) {e.printStackTrace();}try {if (fos != null) {fos.close();}} catch (IOException e) {e.printStackTrace();}}return "Bingo!";}

行数据对应实体类

public class ImportExcel implements Serializable {private static final long serialVersionUID = 1L;/*** 字段名*/@ExcelProperty(index = 1)private String filedName;/*** 字段类型*/@ExcelProperty(index = 2)private String dataType;/*** 字段长度*/@ExcelProperty(index = 3)private String length;/*** 是否非空*/@ExcelProperty(index = 4)private String notNull;/*** 键*/@ExcelProperty(index = 5)private String key;/*** 默认值*/@ExcelProperty(index = 6)private String defaultValue;/*** 备注*/@ExcelProperty(index = 7)private String remark; }

生成的MySql建表语句

加上表名，表注释即可执行

DROP TABLE IF EXISTS ``; CREATE TABLE `` ( `fd_id` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AUTO_INCREMENT DEFAULT '' COMMENT '唯一标识码', `fd_mobile` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '手机号', `fd_account` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '账号', `fd_password` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '密码', `fd_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'default' COMMENT '名称', `fd_english_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '英文名', `fd_head_img` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '头像', `fd_gender` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'secrecy' COMMENT '性别 male：男； female：女； secrecy：保密；', `fd_creator` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '创建人', `fd_create_time` DATETIME NOT NULL COMMENT '创建时间', `fd_updater` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '更新人', `fd_update_time` DATETIME COMMENT '更新时间', `fd_delete_status` CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '删除状态 1：已删除； 0：未删除；', `fd_remarks` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '备注', PRIMARY KEY (`fd_id`), UNIQUE KEY `fd_id` (`fd_id`) USING BTREE, UNIQUE KEY `fd_mobile` (`fd_mobile`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';

总结

以上是生活随笔为你收集整理的Excel表结构模板生成MySql建表语句的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。