Excel生成建表语句

Excel生成建表语句

简介

前言

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

参照博客地址：博客地址

准备工作

Excel模板

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

image-20210224163802424

Database Nameyour database nameTable Name (physical name)your table nameTable Name (logical name)你的表名
Column Name (physical name)Column Name (logical name)TypeLengthDecimalPKNOT NULL
column 1列 1VARCHAR255**

撸代码

依赖

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<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的数据类型

1
2
3
4
5
6
7
8
9
10
11
12
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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@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

定义建表语句需要的信息

1
2
3
4
5
6
7
8
9
10
11
@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文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
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文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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语句。

目前支持情况：

  • VARCHAR
  • INT
  • LONG
  • DATE
  • DATETIME
  • TIMESTAMP
  • TEXT
  • LONGTEXT
  • CHAR
  • 是否为空
  • 单主键
  • BIGINT
  • FLOAT
  • DOUBLE
  • DECIMAL
  • 联合主键
  • 自定义字符集编码

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

好好码代码吖 > JAVA > POI
#POI
Excel生成建表语句
https://github.com/yangxiangnanwill/yangxiangnanwill.github.io/2024/01/03/好好码代码吖/JAVA/POI/Excel生成建表语句/
作者
will
发布于
2024年1月3日
许可协议