Excel生成建表语句

简介

前言

最近公司开展新项目，由于项目进度紧急，部分项目负责人不在现在，建表相关的使用的是Excel，由于Excel拼接过于麻烦，博猪COPY了一下大神的代码，修改了部分代码，以便适用于我们项目。

参照博客地址：博客地址

准备工作

Excel模板

模板可以根据自己的习惯来创建，需要注意的是建表时我们需要的参数有：数据库名，表名，表注释，列名，列注释，列类型，列长度，列精度，是否主键，是否非空。

image-20210224163802424

Database Name your database name Table Name (physical name) your table name Table Name (logical name) 你的表名
Column Name (physical name) Column Name (logical name) Type Length Decimal PK NOT NULL
column 1 列 1 VARCHAR 255 * *

撸代码

依赖

首先新建maven项目，并导入poi（用于操作excel）、lombok（简化实体类代码）依赖：

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.18.16</version>
	<scope>provided</scope>
</dependency>

因为我们只需要控制台输入，并且输出sql语句，所以只需要这3个依赖即可。

相关实体类

数据类型常量类TableTypeConstant.java

定义MySQL的数据类型

public class TableTypeConstant {
	public static final String INT = "int";
	public static final String VARCHAR ="varchar";
	public static final String DATE = "date";
	public static final String DATE_TIME="datetime";
	public static final String TEXT ="text";
	public static final String LONG_TEXT = "longtext";
	public static final String CHAR = "char";
	public static final String LONG = "long";
	public static final String TIME_STAMP = "timestamp";
	public static final String DECIMAL="decimal";
}

字段信息ColumnEntity.java

定义我们关注的字段的关键信息

@Data
public class ColumnEntity {
	/*物理列名*/
	private String physicalColumnName;
	/*逻辑列名*/
	private String logicalColumnName;
	/*类型*/
	private String type;
	/*长度*/
	private String length;
	/*精度*/
	private String decimal;
	/*是否主键*/
	private boolean isPrimaryKey;
	/*是否非空*/
	private boolean isNotNull;
}

表信息TableEntity.java

定义建表语句需要的信息

@Data
public class TableEntity {
	/*列信息*/
	private List<ColumnEntity> entities;
	/*表物理名*/
	private String physicalTableName;
	/*表逻辑名*/
	private String logicalTableName;
	/*数据库名*/
	private String databaseName;
}

业务类

生成SQL语句Excel.java

包含3个方法

  • TableEntity readExcel(String excelPath) 解析Excel并返回TableEntity
  • void convertSQL(TableEntity tableEntity) 根据TableEntity生成SQL语句并写入txt
  • void writeTXT(String path, String value, String fileName) 将字符串写入txt文件
import com.bossYang.myBatisTest.bean.ColumnEntity;
import com.bossYang.myBatisTest.bean.TableEntity;
import com.bossYang.myBatisTest.bean.TableTypeConstant;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.List;

public class Excel {

	/**
	 * 读取excel并转换为表实体
	 * 
	 * @param excelPath
	 * @return
	 */
	public TableEntity readExcel(String excelPath) {
		// 解析模板对象List
		List<ColumnEntity> entities = new ArrayList<ColumnEntity>();
		// 表物理名
		String physicalTableName = null;
		// 表逻辑名
		String logicalTableName = null;
		// 数据库名
		String databaseName = null;
		try {
			// String encoding = "GBK";
			File excel = new File(excelPath);
			if (excel.isFile() && excel.exists()) { // 判断文件是否存在

				String[] split = excel.getName().split("\\."); // .是特殊字符，需要转义！！！！！
				Workbook wb;
				// 根据文件后缀（xls/xlsx）进行判断
				if ("xls".equals(split[1])) {
					FileInputStream fis = new FileInputStream(excel); // 文件流对象
					wb = new HSSFWorkbook(fis);
				} else if ("xlsx".equals(split[1])) {
					wb = new XSSFWorkbook(excel);
				} else {
					System.out.println("文件类型错误!");
					return null;
				}

				// 开始解析
				Sheet sheet = wb.getSheetAt(0); // 读取sheet 0

				int firstRowIndex = sheet.getFirstRowNum() + 2; // 第一、二行是列名，所以不读，从第三行开始读
				int lastRowIndex = sheet.getLastRowNum();
				// System.out.println("firstRowIndex: "+firstRowIndex);
				// System.out.println("lastRowIndex: "+lastRowIndex);

				// 解析模板对象List
				entities = new ArrayList<ColumnEntity>();
				// 数据库名
				databaseName = sheet.getRow(0).getCell(1).getStringCellValue();
				// 表物理名
				physicalTableName = sheet.getRow(0).getCell(3).getStringCellValue();
				// 表逻辑名
				logicalTableName = sheet.getRow(0).getCell(5).getStringCellValue();

				for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { // 遍历行
					// System.out.println("rIndex: " + rIndex);
					ColumnEntity entity = new ColumnEntity();
					Row row = sheet.getRow(rIndex);
					if (row != null) {
						if (row.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue() == null || row
								.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().isEmpty()) {
							continue;
						}
						/*
						 * int firstCellIndex = row.getFirstCellNum(); int lastCellIndex =
						 * row.getLastCellNum(); for (int cIndex = firstCellIndex; cIndex <
						 * lastCellIndex; cIndex++) { //遍历列 Cell cell = row.getCell(cIndex); if (cell !=
						 * null) { System.out.println(cell.toString()); } }
						 */
						
						// 解析对象
						entity.setPhysicalColumnName(
								row.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());// 列物理名
						entity.setLogicalColumnName(
								row.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());// 列逻辑名
						entity.setType(row.getCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());// 类型
						entity.setLength(row.getCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());// 长度
						entity.setDecimal(row.getCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());// 精度
						entity.setPrimaryKey("*"
								.equals(row.getCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue()));// 是否主键
						entity.setNotNull("*"
								.equals(row.getCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue()));// 是否非空
						// 存入list
						entities.add(entity);
					}
				}
			} else {
				System.out.println("找不到指定的文件");
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		TableEntity tableEntity = new TableEntity();
		tableEntity.setEntities(entities);
		tableEntity.setDatabaseName(databaseName);
		tableEntity.setLogicalTableName(logicalTableName);
		tableEntity.setPhysicalTableName(physicalTableName);
		return tableEntity;
	}

	/**
	 * 将表实体转换为sql并输出为txt
	 * 
	 * @param tableEntity
	 */
	public void convertSQL(TableEntity tableEntity) {
		StringBuffer sql = new StringBuffer();
		sql.append("CREATE TABLE `");
		sql.append(tableEntity.getDatabaseName());
		sql.append("`.`");
		sql.append(tableEntity.getPhysicalTableName());
		sql.append("` (");
		// CREATE TABLE `databaseName`.`tablePhysicalName` (
		List<ColumnEntity> cellEnties = tableEntity.getEntities();
		// 主键
		String primaryKey = null;
		// 获取主键
		for (ColumnEntity item : cellEnties) {
			// 将pk为true的设为主键
			if (item.isPrimaryKey()) {
				primaryKey = item.getPhysicalColumnName();
				break;
			}
		}
		// 循环列
		for (ColumnEntity item : cellEnties) {
			sql.append(" `");
			sql.append(item.getPhysicalColumnName().trim());
			sql.append("` ");
			// `physicalColumnName`

			// 根据NOT NULL 来拼接
			if (item.isNotNull()) { // 如果不允许为空，则拼接NOT NULL
				//类型
				String type = item.getType().toLowerCase();
				//类型转换
				if(type.indexOf("varchar")!=-1) {
					type= TableTypeConstant.VARCHAR;
				}else if (type.indexOf("number")!=-1) {
					type=TableTypeConstant.INT;
					//默认长度
					if(item.getLength()==null||item.getLength().isEmpty()) {
						item.setLength("11");
					}
				}else if(type.indexOf("char")!=-1) {
					type=TableTypeConstant.CHAR;
				}

				
				// 根据类型选择是否拼接长度
				if (TableTypeConstant.CHAR.equals(type) || TableTypeConstant.VARCHAR.equals(type)) { // 文本需要拼接长度
					sql.append(type);
					sql.append("(");
					sql.append(item.getLength());
					sql.append(") CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '");
				} else if (TableTypeConstant.TEXT.equals(type) || TableTypeConstant.LONG_TEXT.equals(type)) { // 文本不需要拼接长度
					sql.append(type);
					sql.append(" CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '");
				} else if (TableTypeConstant.DATE.equals(type)) { // 时间不需要拼接长度
					sql.append(type);
					sql.append(" NOT NULL COMMENT '");
				} else if (TableTypeConstant.DATE_TIME.equals(type)||TableTypeConstant.TIME_STAMP.equals(type)) { // 时间需要拼接长度 并且长度为0
					sql.append(type);
					sql.append("(0) NOT NULL COMMENT '");
				} else if (TableTypeConstant.INT.equals(type)) { // 数字需要拼接长度
					sql.append(type);
					sql.append("(");
					sql.append(item.getLength());
					sql.append(")  NOT NULL COMMENT '");
				}
			} else { // 如果允许为空，则拼接 NULL DEFAULT NULL
				
				String type = item.getType().toLowerCase();
				//类型转换
				if(type.indexOf("varchar")!=-1) {
					type=TableTypeConstant.VARCHAR;
				}else if (type.indexOf("number")!=-1) {
					type=TableTypeConstant.INT;
					//默认长度
					if(item.getLength()==null||item.getLength().isEmpty()) {
						item.setLength("11");
					}
				}else if(type.indexOf("char")!=-1) {
					type=TableTypeConstant.CHAR;
				}
				
				// 根据类型选择是否拼接长度
				if (TableTypeConstant.CHAR.equals(type) || TableTypeConstant.VARCHAR.equals(type)) { // 文本需要拼接长度
					sql.append(type);
					sql.append("(");
					sql.append(item.getLength());
					sql.append(") CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '");
				} else if (TableTypeConstant.TEXT.equals(type) || TableTypeConstant.LONG_TEXT.equals(type)) { // 文本不需要拼接长度
					sql.append(type);
					sql.append(" CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '");
				} else if (TableTypeConstant.DATE.equals(type)) { // 时间不需要拼接长度
					sql.append(type);
					sql.append(" NULL DEFAULT NULL COMMENT '");
				} else if (TableTypeConstant.DATE_TIME.equals(type)||TableTypeConstant.TIME_STAMP.equals(type)) { // 时间需要拼接长度 并且长度为0
					sql.append(type);
					sql.append("(0) NULL DEFAULT NULL COMMENT '");
				} else if (TableTypeConstant.INT.equals(type) || TableTypeConstant.BIG_INT.equals(type) || TableTypeConstant.LONG.equals(type)) { // 数字需要拼接长度
					sql.append(type);
					sql.append("(");
					sql.append(item.getLength());
					sql.append(")  NULL DEFAULT NULL COMMENT '");
				}
			}

			// 拼接逻辑列名
			sql.append(item.getLogicalColumnName());
			sql.append("',");
		}

		// 拼接主键
		sql.append(" PRIMARY KEY (`");
		sql.append(primaryKey);
		sql.append("`) USING BTREE ) ");
		// 拼接引擎和逻辑表名
		sql.append("ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '");
		sql.append(tableEntity.getLogicalTableName());
		sql.append("'  ROW_FORMAT = Compact;");
		System.err.println(sql);
		try {
			writeTXT("", sql.toString(), tableEntity.getPhysicalTableName() + tableEntity.getLogicalTableName());
			System.out
					.println("已导出:" + tableEntity.getPhysicalTableName() + tableEntity.getLogicalTableName() + ".txt!");
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("导出文件失败");
		}
	}

	/**
	 * 将字符串写入txt并导出
	 * 
	 * @throws Exception
	 */
	public void writeTXT(String path, String value, String fileName) throws Exception {
		File f = new File(path + fileName + ".txt");
		FileOutputStream fos1 = new FileOutputStream(f);
		OutputStreamWriter dos1 = new OutputStreamWriter(fos1);
		dos1.write(value);
		dos1.close();
	}
}

主入口

通过控制台输入Excel路径，会在jar包同级目录下生成包含建表SQL的TXT文件。

public class MainApplication {
	public static void main(String[] args) {
		while(true) {
			Excel excel = new Excel();
			Scanner scanner = new Scanner(System.in);
			System.err.println("请输入Excel的路径：");
			String excelPath = scanner.nextLine();
			TableEntity tEntity = excel.readExcel(excelPath);
			if(tEntity==null) {
				continue;
			}
			excel.convertSQL(tEntity);
		}
	}
}

总结

基本上就是解析EXCEL，然后根据数据类型拼接SQL语句。

目前支持情况：

较原文没啥改动，只是增加了部分字段，处理了部分默认值问题。

posted @ 2021-11-23 09:50  will-yang  阅读(978)  评论(0)  编辑  收藏  举报