- 根据Java对象生成建表语句，支持Oracle，MySQL
- 基础功能
- 扩充功能
- 实现代码
- 1、两个关键的枚举类
- 2、可以扩展的注解类
- 3、重要的逻辑生成类
- 4、处理默认值的类
- 5、测试
- 6、结果
-
根据全类名、类路径、创建建表语句
功能实现
- 适配Model 字段 转换为 数据库中的字段类型
- 构建create table 语句
- 可自定义表名
- 可通过自定义在数据库中的字段长度
- 可通过某种方式定义主键 或 notnull 或默认什么。
实现代码 1、两个关键的枚举类之后希望可以反向生成model对象(还未完成)
package com.model;
import lombok.Getter;
@Getter
public enum DbType {
MySQL,Oracle;
}
-----------------------
package com.db;
import cn.hutool.core.util.StrUtil;
import com.model.DbType;
import lombok.Getter;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.stream.Collectors;
@Getter
public enum FieldToColumnType {
// MySQL类型
MYSQL_BYTE_COLUMN_TYPE("byte", "big", "1", DbType.MySQL),
MYSQL_INT_COLUMN_TYPE("int", "int", "11", DbType.MySQL),
MYSQL_SHORT_COLUMN_TYPE("short", "int", "6", DbType.MySQL),
MYSQL_LONG_COLUMN_TYPE("long", "bigint", "20", DbType.MySQL),
MYSQL_BOOLEAN_COLUMN_TYPE("boolean", "tinyint", "1", DbType.MySQL),
MYSQL_FLOAT_COLUMN_TYPE("float", "float", "", DbType.MySQL),
MYSQL_DOUBLE_COLUMN_TYPE("double", "double", "", DbType.MySQL),
MYSQL_CHAR_COLUMN_TYPE("char", "VARCHAR", "50", DbType.MySQL),
MYSQL_BYTE_PACKING_COLUMN_TYPE("com.lang.Byte", "big", "1", DbType.MySQL),
MYSQL_INT_PACKING_COLUMN_TYPE("com.lang.Integer", "int", "11", DbType.MySQL),
MYSQL_SHORT_PACKING_COLUMN_TYPE("com.lang.Short", "int", "6", DbType.MySQL),
MYSQL_LONG_PACKING_COLUMN_TYPE("com.lang.Long", "bigint", "20", DbType.MySQL),
MYSQL_BOOLEAN_PACKING_COLUMN_TYPE("com.lang.Boolean", "tinyint", "1", DbType.MySQL),
MYSQL_FLOAT_PACKING_COLUMN_TYPE("com.lang.Float", "float", "", DbType.MySQL),
MYSQL_DOUBLE_PACKING_COLUMN_TYPE("com.lang.Double", "double", "", DbType.MySQL),
MYSQL_CHAR_PACKING_COLUMN_TYPE("com.lang.Character", "VARCHAR", "255", DbType.MySQL),
MYSQL_BYTEARRAY_COLUMN_TYPE("byte[]", "blob", "", DbType.MySQL),
MYSQL_DATE_COLUMN_TYPE("java.util.Date", "datetime", "", DbType.MySQL),
MYSQL_SQL_DATE_COLUMN_TYPE("java.sql.Date", "data", "", DbType.MySQL),
MYSQL_TIMESTAMP_COLUMN_TYPE("java.sql.TIMESTAMP", "timestamp", "", DbType.MySQL),
DEFAULT_MYSQL_UNKNOWN_TYPE("String", "VARCHAR", "255", DbType.MySQL),
// Oracle类型
// Number类型是oralce的数值类型，存储的数值的精度可以达到38位。
// Number是一种变长类型，长度为0-22字节。取值范围为:10^(-130) —— 10^126(不包括)。以十进制格式进行存储的，
// 它便于存储，但是在计算上，系统会自动的将它转换成为二进制进行运算的。
// Number(p,s)：
// p和s都是可选的。
// p指精度(precision)，即总位数。默认情况下精度为38。精度的取值范围为1~38。
// s指小数位(scale)，小数点右边的位数。小数点位数的合法值为-84~127。小数位的默认值由精度来决定。如果没有指定精度，小数位默认为最大的取值区间。
// 如果指定了精度，没有指定小数位。小数位默认为0(即没有小数位)。
// 精度和小数位不会影响数据如何存储，只会影响允许哪些数值及数值如何舍入。
ORACLE_BYTE_COLUMN_TYPE("byte", "CHAR", "1", DbType.Oracle),
ORACLE_INT_COLUMN_TYPE("int", "NUMBER", "11", DbType.Oracle),
ORACLE_SHORT_COLUMN_TYPE("short", "NUMBER", "6", DbType.Oracle),
ORACLE_LONG_COLUMN_TYPE("long", "NUMBER", "20", DbType.Oracle),
ORACLE_BOOLEAN_COLUMN_TYPE("boolean", "CHAR", "1", DbType.Oracle),
ORACLE_FLOAT_COLUMN_TYPE("float", "NUMBER", "13,2", DbType.Oracle),
ORACLE_DOUBLE_COLUMN_TYPE("double", "NUMBER", "23,2", DbType.Oracle),
ORACLE_CHAR_COLUMN_TYPE("char", "VARCHAR2", "255", DbType.Oracle),
ORACLE_BYTE_PACKING_COLUMN_TYPE("com.lang.Byte", "CHAR", "1", DbType.Oracle),
ORACLE_INT_PACKING_COLUMN_TYPE("com.lang.Integer", "NUMBER", "11", DbType.Oracle),
ORACLE_SHORT_PACKING_COLUMN_TYPE("com.lang.Short", "NUMBER", "6", DbType.Oracle),
ORACLE_LONG_PACKING_COLUMN_TYPE("com.lang.Long", "NUMBER", "20", DbType.Oracle),
ORACLE_BOOLEAN_PACKING_COLUMN_TYPE("com.lang.Boolean", "CHAR", "1", DbType.Oracle),
ORACLE_FLOAT_PACKING_COLUMN_TYPE("com.lang.Float", "NUMBER", "13.2", DbType.Oracle),
ORACLE_DOUBLE_PACKING_COLUMN_TYPE("com.lang.Double", "NUMBER", "23.2", DbType.Oracle),
ORACLE_CHAR_PACKING_COLUMN_TYPE("com.lang.Character", "VARCHAR2", "255", DbType.Oracle),
ORACLE_BYTEARRAY_COLUMN_TYPE("byte[]", "BLOB", "", DbType.Oracle),
ORACLE_DATE_COLUMN_TYPE("java.util.Date", "DATE", "", DbType.Oracle),
ORACLE_SQL_DATE_COLUMN_TYPE("java.sql.Date", "DATE", "", DbType.Oracle),
ORACLE_TIMESTAMP_COLUMN_TYPE("java.sql.TIMESTAMP", "TIMESTAMP", "", DbType.MySQL),
DEFAULT_ORACLE_UNKNOWN_TYPE("String", "VARCHAR2", "255", DbType.Oracle);
// Model对象属性的类型
private final String fieldType;
// 数据库中列的类型
private final String columnType;
// 数据库中列的长 或 精度
private final String columnLength;
// 数据库类型
private final DbType dbType;
FieldToColumnType(String fieldType, String columnType, String columnLength, DbType dbType) {
this.fieldType = fieldType;
this.columnType = columnType;
this.columnLength = columnLength;
this.dbType = dbType;
}
public static FieldToColumnType getColumnTypeByField(String fieldType, DbType dbType) {
// fieldType 不能为空
if (StrUtil.isBlank(fieldType)) throw new RuntimeException("fieldType is null");
// dbType 不能为空
Objects.requireNonNull(dbType, "DbType NOT NULL");
// 寻找匹配的字段类型
Optional first = getFieldToColumnTypesByDbType(dbType).stream()
.filter((map) -> fieldType.equals(map.getFieldType())).findFirst();
// 如果没有找到合适的类型直接返回该数据库类型中默认的类型
if (first.isEmpty()) {
return defaultTypeByDbType(dbType);
}
return first.get();
}
private static FieldToColumnType defaultTypeByDbType(DbType dbType) {
switch (dbType) {
case MySQL:
return FieldToColumnType.DEFAULT_MYSQL_UNKNOWN_TYPE;
case Oracle:
return FieldToColumnType.DEFAULT_ORACLE_UNKNOWN_TYPE;
default:
return null;
}
}
public static List getFieldToColumnTypesByDbType(DbType dbType) {
return Arrays.stream(FieldToColumnType.values()).filter((map) -> dbType == map.getDbType()).collect(Collectors.toList());
}
}
2、可以扩展的注解类
package com.db;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.TYPE})
public @interface TableExtension {
// 是否为主键
boolean isId() default false;
// 自定义的列名
String columnName() default "";
// 字段的长度
int length() default Integer.MIN_VALUE;
// 字段是否不为空
boolean isNotNull() default false;
// 字段的默认值
String defaultValue() default DefaultValues.NONE;
// 字段的注释
String remark() default "";
}
3、重要的逻辑生成类
package com.db;
import cn.hutool.core.util.StrUtil;
import com.model.DbType;
import javax.persistence.Table;
import java.lang.reflect.Field;
import java.util.*;
public class TableBuilder {
public String createTableSql(String className, DbType dbType) throws ClassNotFoundException {
if(StrUtil.isNotBlank(className)) throw new RuntimeException("className cannot null");
return createTableSql(Class.forName(className),dbType);
}
public String createTableSql(Class model, DbType dbType) {
String result = "";
switch (dbType) {
case MySQL:
result = createMySQLTableSQL(model);
break;
case Oracle:
result = createOracleTableSQL(model);
break;
default:
break;
}
return result;
}
private String createMySQLTableSQL(Class model) {
// model不能为空
Objects.requireNonNull(model, "MODEL MUST NOT NULL");
StringBuffer sql = new StringBuffer();
TableExtension extension = model.getAnnotation(TableExtension.class);
// 表注释
String columnRemark = extension == null ? "" : extension.remark();
// 表名
String tableName = getTableName(model);
// 主体内容
String context = getMySQLModelContext(model);
// 前置处理表存在的导致表创建不了的Sql
sql.append("DROp TABLE IF EXISTS `").append(tableName).append("`;n");
// 构建CREATE TABLE 语句
sql.append("CREATE TABLE ").append("`").append(tableName).append("`").append("(n")
.append(context)
.append(") ").append(" COMMENT '").append(columnRemark).append("';");
return sql.toString();
}
private String getMySQLModelContext(Class model) {
Field[] declaredFields = model.getDeclaredFields();
StringBuffer context = new StringBuffer();
Arrays.stream(declaredFields).forEach(field -> {
// 根据对应的字段类型与数据库类型获取匹配的FieldToColumnType
FieldToColumnType columnTypeByField = FieldToColumnType.getColumnTypeByField(field.getType().getName(), DbType.MySQL);
// 字段名
String fieldName = field.getName();
// 数据库列类型
String columnType = columnTypeByField.getColumnType();
// 字段类型长度
String columnLength = StrUtil.isBlank(columnTypeByField.getColumnLength()) ?
"" : String.format("(%s)", columnTypeByField.getColumnLength());
// 字段是否为主键
boolean idPresent = false;
// 字段是否不能为NULL
boolean isNotNull = false;
// 字段默认值
String defaultValue = "";
// 数据库字段注释
String columnRemark = "";
// 判断该字段是否有扩展属性的这个注解 如果有个话 针对每个字段进行特殊处理
TableExtension extension = field.getAnnotation(TableExtension.class);
if (extension != null) {
// 扩展属性的 isNotNull 为true 时defaultValue不能为 DefaultValues.NULL
if (extension.isNotNull() && extension.defaultValue().equals(DefaultValues.NULL))
throw new RuntimeException("Table " + model.getSimpleName() + " Field " + fieldName +
"! When Extension IsNotNull are true, DefaultValue cannot be DefaultValues.NULL.");
fieldName = StrUtil.isNotBlank(extension.columnName()) ? extension.columnName() : fieldName;
columnLength = extension.length() == Integer.MIN_VALUE ?
columnLength : String.format("(%s)", extension.length());
idPresent = extension.isId();
isNotNull = extension.isNotNull();
// 可自定义默认值
defaultValue = getDefaultValue(extension.defaultValue());
columnRemark = extension.remark();
}
context.append("t")
.append(" `").append(fieldName).append("` ")
.append(columnType)
.append(columnLength)
// 该字段是主键的话添加 PRIMARY KEY这个关键字
.append(idPresent ? " PRIMARY KEY" : "")
// 该字段如果不为NULL添加 NOT NULL这个关键字
.append(isNotNull ? " NOT NULL" : "")
.append(StrUtil.isBlank(defaultValue) ? "" : (" DEFAULT " + defaultValue))
.append(" COMMENT '").append(columnRemark).append("'")
// 判断是否为最后一个字段 做最后一个逗号的处理
.append(field == declaredFields[declaredFields.length - 1] ? "" : ",")
.append("n");
});
return context.toString();
}
private String createOracleTableSQL(Class model) {
Objects.requireNonNull(model, "MODEL MUST NOT NULL");
StringBuffer sql = new StringBuffer();
// 表名
String tableName = getTableName(model).toUpperCase();
// 主体内容
String context = getOracleModelContext(model);
// 主体内容
String fieldNodes = getOracleFieldNotes(model, tableName);
// 表存在则删除表的的前置语句
sql.append(String.format("DECLARE n" +
" NUM NUMBER; n" +
"BEGIN n" +
" SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERe TABLE_NAME = '%1$s'; n" +
" IF NUM = 1 THEN n" +
" EXECUTE IMMEDIATE 'DROp TABLE %1$s'; n" +
" END IF; n" +
"END;n", tableName));
// 处理begin 后不能执行其他语句的问题
sql.append("/n");
sql.append("CREATE TABLE ").append(tableName).append("(n")
.append(context)
.append(") ;");
sql.append("n").append(fieldNodes);
return sql.toString();
}
private String getOracleModelContext(Class model) {
String tableName = model.getSimpleName().toUpperCase();
Field[] declaredFields = model.getDeclaredFields();
StringBuffer context = new StringBuffer();
Arrays.stream(declaredFields).forEach(field -> {
FieldToColumnType columnTypeByField = FieldToColumnType.getColumnTypeByField(field.getType().getName(), DbType.Oracle);
// 字段名
String fieldName = field.getName();
// 数据库列类型
String columnType = columnTypeByField.getColumnType();
// 字段类型长度
String columnLength = StrUtil.isBlank(columnTypeByField.getColumnLength()) ?
"" : String.format("(%s)", columnTypeByField.getColumnLength());
// 字段是否为主键
boolean idPresent = false;
// 字段是否不能为NULL
boolean isNotNull = false;
// 字段默认值
String defaultValue = "";
TableExtension extension = field.getAnnotation(TableExtension.class);
if (extension != null) {
if (extension.isNotNull() && extension.defaultValue().equals(DefaultValues.NULL))
throw new RuntimeException("Table " + tableName + " Field " + fieldName +
"! When Extension IsNotNull are true, DefaultValue cannot be DefaultValues.NULL.");
fieldName = (StrUtil.isNotBlank(extension.columnName()) ? extension.columnName() : fieldName).toUpperCase();
columnLength = extension.length() == Integer.MIN_VALUE ?
columnLength : String.format("(%s)", extension.length());
idPresent = extension.isId();
isNotNull = extension.isNotNull();
defaultValue = getDefaultValue(extension.defaultValue());
}
context.append("t")
.append(" ").append(fieldName).append(" ")
.append(columnType)
.append(columnLength)
.append(idPresent ? " PRIMARY KEY" : "")
.append(StrUtil.isBlank(defaultValue) ? "" : (" DEFAULT " + defaultValue))
.append(isNotNull ? " NOT NULL" : "")
// 判断是否为最后一个字段 做最后一个逗号的处理
.append(field == declaredFields[declaredFields.length - 1] ? "" : ",")
.append("n");
});
return context.toString();
}
private String getOracleFieldNotes(Class model, String tableName) {
StringBuffer fieldNotes = new StringBuffer();
TableExtension extension = model.getAnnotation(TableExtension.class);
// 生成表注释
if (extension != null && StrUtil.isNotBlank(extension.remark())) {
fieldNotes.append(String.format("comment on table %s is '%s' ;n", tableName, extension.remark()));
}
// 只为有remark值的字段添加注释
Arrays.stream(model.getDeclaredFields()).filter(field -> {
TableExtension fieldAnnotation = field.getAnnotation(TableExtension.class);
if (fieldAnnotation != null && StrUtil.isNotBlank(fieldAnnotation.remark())) return true;
else return false;
}).forEach(field -> {
TableExtension fieldAnnotation = field.getAnnotation(TableExtension.class);
String fieldName = (StrUtil.isNotBlank(fieldAnnotation.columnName()) ? fieldAnnotation.columnName() : field.getName()).toUpperCase();
fieldNotes.append(String.format("comment on column %s.%s is '%s';n"
, tableName, fieldName, fieldAnnotation.remark()));
});
return fieldNotes.toString();
}
private String getDefaultValue(String defaultValue) {
if (StrUtil.isBlank(defaultValue)) return "";
switch (defaultValue) {
case DefaultValues.NULL:
case DefaultValues.EMPTY_STRING:
case DefaultValues.NULL_STRING:
case DefaultValues.TRUE:
case DefaultValues.FALSE:
return defaultValue;
default:
return "'" + defaultValue + "'";
}
}
private String getTableName(Class model) {
String tableName = "";
// 当model 有 Table 这个注解时 且 Table注解的name值不是空时 直接取tableName为 生成表名
// 否则 类名 当表名
if (model.isAnnotationPresent(Table.class) && StrUtil.isNotBlank(model.getAnnotation(Table.class).name())) {
tableName = model.getAnnotation(Table.class).name();
} else {
tableName = model.getSimpleName();
}
return tableName;
}
}
4、处理默认值的类
package com.db;
public class DefaultValues {
public static final String NULL = "NULL";
public static final String NULL_STRING = "'NULL'";
public static final String EMPTY_STRING = "''";
public static final String FALSE = "0";
public static final String TRUE = "1";
public static final String NONE = "";
}
5、测试
public static void main(String[] args) {
System.out.println(new TableBuilder().createTableSql(TestModel.class, DbType.Oracle));
}
package com.db;
import lombok.Getter;
import lombok.Setter;
import org.springframework.lang.NonNull;
import javax.persistence.Table;
import java.util.Date;
@Getter
@Setter
@Table(name = "t_testModel")
@TableExtension(remark = "测试表")
public class TestModel {
@TableExtension(isId = true)
private String id;
@TableExtension(columnName = "userName",defaultValue = "name1", remark = "姓名")
private String name;
@TableExtension(remark = "年龄", length = 10)
private int age;
@TableExtension(defaultValue = DefaultValues.NULL, remark = "工资")
private Double salary;
@TableExtension(remark = "生日")
private Date birth;
@TableExtension(defaultValue=DefaultValues.TRUE,remark = "是否禁用")
private boolean disabled;
}
6、结果
// 生成结果
------------- Oralce
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERe TABLE_NAME = 'T_TESTMODEL';
IF NUM = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE T_TESTMODEL';
END IF;
END;
/
CREATE TABLE T_TESTMODEL(
ID VARCHAr2(255) PRIMARY KEY,
USERNAME VARCHAr2(255) DEFAULT 'name1',
AGE NUMBER(10),
SALARY VARCHAr2(255) DEFAULT NULL,
BIRTH DATE,
DISABLED CHAr(1) DEFAULT 1
) ;
comment on table T_TESTMODEL is '测试表' ;
comment on column T_TESTMODEL.USERNAME is '姓名';
comment on column T_TESTMODEL.AGE is '年龄';
comment on column T_TESTMODEL.SALARY is '工资';
comment on column T_TESTMODEL.BIRTH is '生日';
comment on column T_TESTMODEL.DISABLED is '是否禁用';
------------- MySQL
DROP TABLE IF EXISTS `t_testModel`;
CREATE TABLE `t_testModel`(
`id` VARCHAr(255) PRIMARY KEY COMMENT '',
`userName` VARCHAr(255) DEFAULT 'name1' COMMENT '姓名',
`age` int(10) COMMENT '年龄',
`salary` VARCHAr(255) DEFAULT NULL COMMENT '工资',
`birth` datetime COMMENT '生日',
`disabled` tinyint(1) DEFAULT 1 COMMENT '是否禁用'
) COMMENT '测试表';
