Excel表結(jié)構(gòu)模板生成MySql建表語句

• • Excel表結(jié)構(gòu)模板
  • Java 代碼
  • 生成的MySql建表語句

Excel表結(jié)構(gòu)模板

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 ");}// 默認(rèn)值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!";}

行數(shù)據(jù)對應(yīng)實體類

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;/*** 默認(rèn)值*/@ExcelProperty(index = 6)private String defaultValue;/*** 備注*/@ExcelProperty(index = 7)private String remark; }

生成的MySql建表語句

加上表名，表注釋即可執(zhí)行

DROP TABLE IF EXISTS ``; CREATE TABLE `` ( `fd_id` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AUTO_INCREMENT DEFAULT '' COMMENT '唯一標(biāo)識碼', `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 '創(chuàng)建人', `fd_create_time` DATETIME NOT NULL COMMENT '創(chuàng)建時間', `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 '刪除狀態(tài) 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='';

總結(jié)

以上是生活随笔為你收集整理的Excel表结构模板生成MySql建表语句的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。