背景
有时候，我们需要根据数据库表信息和字段信息来生成 DDL语句，如何实现这个功能？
定义基础的数据结构
字段定义
每个字段的定义表，这些信息都可以通过select * from columns where table_name='pg_dynamic_form'; 来进行获取 （在information_schema表里）。
package com.zhonghuitech.generator.config;
import lombok.Data;
import java.io.Serializable;
/**
* @author aborn
* @date 2024/09/10 19:00
*/
@Data
public class TableField implements Serializable {
private static final long serialVersionUID = -2854828353278355999L;
/**
* 字段名
*/
private String fieldName;
/**
* 字段类型
*/
private String fieldType;
/**
* 字段说明
*/
private String fieldComment;
/**
* 主键 0：否 1：是
*/
private boolean primaryPk;
/**
* 索引标识
*/
private String key;
private int keyIdx;
/**
* 扩展信息
*/
private String extra;
/**
* 是否可为 null
*/
private boolean nullable;
/**
* 默认值
*/
private String defVal;
/**
* 最大长度，当为 varchar时有用
*/
private String maxLen;
/**
* 字符集
* utf8mb4_0900_ai_ci
*/
private String collationName;
/**
* 字符编码
* utf8mb4
*/
private String charSetName;
public String getFieldType() {
return "varchar".equals(this.fieldType) ? this.fieldType + "(" + this.maxLen + ")" : this.fieldType;
}
}
表信息定义
接着定义表信息，可以通过select * from tables where table_name='pg_dynamic_form';获取
package com.zhonghuitech.generator.config;
import lombok.Builder;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
/**
* @author aborn
* @date 2024/09/10 20:46
*/
@Data
@Builder
public class TableInfo implements Serializable {
private static final long serialVersionUID = -6369044254445870858L;
/**
* 表名称
*/
private String tableName;
/**
* 表描述
*/
private String tableComment;
/**
* InnoDB
*/
private String engine;
/**
* Dynamic
*/
private String rowFormat;
/**
* utf8mb4_0900_ai_ci
*/
private String tableCollation;
List<TableField> fieldList;
}
生成DDL
public static String generateDDL(TableInfo tableInfo) {
List<TableField> fieldList = tableInfo.getFieldList();
String tableName = tableInfo.getTableName();
DdlBuilder builder = new DdlBuilder().create()
.tableName(tableName)
.leftParenthesis()
.wrap();
int fieldNameLen = 0;
int fieldTypeLen = 0;
for (TableField field : fieldList) {
fieldNameLen = Math.max(fieldNameLen, field.getFieldName().length());
fieldTypeLen = Math.max(fieldTypeLen, field.getFieldType().length());
}
fieldNameLen = fieldNameLen + 3;
fieldTypeLen = fieldTypeLen + 3;
for (TableField field : fieldList) {
builder.addTableField(field, fieldNameLen, fieldTypeLen);
}
Collections.sort(fieldList, (o1, o2) -> o1.getKeyIdx() - o2.getKeyIdx());
for (TableField field : fieldList) {
if (org.apache.commons.lang3.StringUtils.isNotBlank(field.getKey())) {
builder.addKeyInfo(field);
}
}
builder = builder.remove(2)
.wrap()
.rightParenthesis().space().addTableComment(tableInfo);
return builder.end();
}
这里的DdlBuilder定义如下
package com.zhonghuitech.generator.util;
import com.zhonghuitech.generator.config.TableField;
import com.zhonghuitech.generator.config.TableInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
/**
* @author aborn
* @date 2024/09/10 19:03
*/
@Slf4j
public class DdlBuilder {
private StringBuilder ddl = new StringBuilder();
public DdlBuilder create() {
ddl.append("CREATE");
return this.space();
}
public DdlBuilder tableName(String tableName) {
ddl.append("TABLE").append(" `").append(tableName).append("`");
return this.wrap();
}
public DdlBuilder leftParenthesis() {
ddl.append("(");
return this.space();
}
public DdlBuilder rightParenthesis() {
ddl.append(")");
return this;
}
public DdlBuilder addField(String field, String type) {
return addField(field, type, false);
}
public DdlBuilder addField(String field, String type, boolean isPrimaryKey) {
ddl.append(field).append(type);
if (isPrimaryKey) {
ddl.append(" AUTO_INCREMENT PRIMARY KEY");
} else {
ddl.append(" NULL");
}
ddl.append(",");
return this;
}
public DdlBuilder addColumn(String field) {
ddl.append(field);
return this;
}
public DdlBuilder addType(String type) {
ddl.append(type);
return this;
}
public DdlBuilder append(String content) {
ddl.append(content);
return this;
}
public DdlBuilder addKeyInfo(TableField field) {
if (field.isPrimaryPk()) {
this.space(4).append("PRIMARY KEY (`").append(field.getFieldName()).append("`) USING BTREE,");
} else if ("UNI".equals(field.getKey())) {
this.space(4).append("UNIQUE KEY `uniq_").append(field.getFieldName()).append("` (`").append(field.getFieldName()).append("`),");
} else if ("MUL".equals(field.getKey())) {
this.space(4).append("KEY `idx_").append(field.getFieldName()).append("` (`").append(field.getFieldName()).append("`),");
} else {
log.error("Unknown error, key:" + field.getKey());
}
this.wrap();
return this;
}
public DdlBuilder addTableField(TableField field, int fieldNameLen, int fieldTypeLen) {
String tableColumn = field.getFieldName();
this.space(4)
.addColumn(String.format("%-" + fieldNameLen + "s", "`" + tableColumn + "`"))
.addType(String.format("%-" + fieldTypeLen + "s", field.getFieldType()));
if (field.isPrimaryPk()) {
this.space().append("NOT NULL AUTO_INCREMENT");
} else {
// 是否为NULL
if (!field.isNullable()) {
this.space().append("NOT NULL");
}
// 默认值
if (StringUtils.isNotBlank(field.getDefVal())) {
if ("CURRENT_TIMESTAMP".equals(field.getDefVal())) {
this.space().append("DEFAULT ").append(field.getDefVal()).append("");
} else {
this.space().append("DEFAULT '").append(field.getDefVal()).append("'");
}
}
// 额外信息
if (StringUtils.isNotBlank(field.getExtra())) {
if (field.getExtra().startsWith("DEFAULT_GENERATED")) {
this.space().append(field.getExtra().substring("DEFAULT_GENERATED".length()));
} else {
this.space().append(field.getExtra());
}
}
}
// comment描述信息
if (StringUtils.isNotBlank(field.getFieldComment())) {
this.space().addComment(field.getFieldComment());
}
this.addComma().wrap();
return this;
}
public DdlBuilder addComma() {
ddl.append(",");
return this;
}
public DdlBuilder space() {
ddl.append(" ");
return this;
}
public DdlBuilder space(int size) {
if (size <= 0) {
size = 1;
}
for (int i = 0; i < size; i++) {
space();
}
return this;
}
public DdlBuilder addComment(String comment) {
ddl.append("COMMENT ").append("'").append(comment).append("'");
return this;
}
public DdlBuilder wrap() {
ddl.append("\n");
return this;
}
public DdlBuilder remove() {
ddl.deleteCharAt(ddl.length() - 1);
return this;
}
public DdlBuilder remove(int size) {
ddl.delete(ddl.length() - size, ddl.length());
return this;
}
public DdlBuilder addTableComment(TableInfo tableInfo) {
String comment = tableInfo.getTableComment();
ddl.append("ENGINE=").append(tableInfo.getEngine()).append(" DEFAULT CHARSET=utf8mb4 COLLATE=")
.append(tableInfo.getTableCollation())
.append(" ROW_FORMAT=")
.append(tableInfo.getRowFormat()).append(" COMMENT='").append(comment).append("'");
return this;
}
public String end() {
return ddl.append(";").toString();
}
}
最后生成效果如下：
image.png
网友评论