根据javabean 生成数据库表
项目组长让我根据 接口文档搭建一个mock工程支持增删改查,一个接口字段值有七八十个字段,一个个粘贴到表中太慢,就搞了这么一个工具。
效果展示 目前就基本能用
其他类型在这里扩展
1 目录结构
2 代码
@Inherited
@Documented
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface DBTable {
String name() default "";
}
@Inherited
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SQLColumn {
//列名
String name() default "";
//描述
String desc() default "";
int size() default 64;
// 约束注解
Constraints constraints() default @Constraints;
}
@Inherited
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Constraints {
// 默认允许为空
boolean allowNull() default true;
// 默认允许重复
boolean unique() default false;
//是否递增
boolean autoIncrement() default false;
// 主键,默认为空
boolean primaryKey() default false;
}
public class TableCreator {
private static String getConstraints(Constraints constraints) { // 获取字段约束属性
String cons = "";
if (!constraints.allowNull()) {
cons += " NOT NULL";
}
if (constraints.primaryKey()) {
cons += " PRIMARY KEY";
}
if (constraints.unique()) {
cons += " UNIQUE";
}
if (constraints.autoIncrement()) {
cons += " AUTO_INCREMENT";
}
return cons;
}
public static String getMySQLSql(Class<?> cl) {
// 通过注解得到表名
DBTable dbtable = cl.getAnnotation(DBTable.class);
String tableName = dbtable.name().length() > 1
? StringCaseUtils.camelCase2LowerSnackCase(dbtable.name())
: StringCaseUtils.camelCase2LowerSnackCase(cl.getName().substring(cl.getName().lastIndexOf(".") + 1));
List<String> columns = new ArrayList<>();
// 得到该类下所有属性
for (Field field : cl.getDeclaredFields()) {
SQLColumn column = field.getAnnotation(SQLColumn.class);
Optional.ofNullable(column).ifPresent(c -> {
String columnName = column.name().length() < 1 ? field.getName() : column.name();
String sqlType = getMySQLType(field.getType(), column);
String name = StringCaseUtils.camelCase2LowerSnackCase(columnName);
columns.add(name + " " + sqlType + getConstraints(column.constraints()) + getComment(column));
});
}
return buildSQL(tableName, columns);
}
public static String getOracleSql(Class<?> cl) {
// 通过注解得到表名
DBTable dbtable = cl.getAnnotation(DBTable.class);
String tableName = dbtable.name().length() > 1
? StringCaseUtils.camelCase2UpperSnackCase(dbtable.name())
: StringCaseUtils.camelCase2UpperSnackCase(cl.getName().substring(cl.getName().lastIndexOf(".") + 1));
List<String> columns = new ArrayList<>();
List<String> comments = new ArrayList<>();
// 得到该类下所有属性
for (Field field : cl.getDeclaredFields()) {
SQLColumn column = field.getAnnotation(SQLColumn.class);
Optional.ofNullable(column).ifPresent(c -> {
String columnName = column.name().length() < 1 ? field.getName() : column.name();
String sqlType = getOracleType(field.getType(), column);
String name = StringCaseUtils.camelCase2UpperSnackCase(columnName);
columns.add(name + " " + sqlType + getConstraints(column.constraints()));
// 字段注释
if (column.desc().length() > 0) {
String comment = "COMMENT ON COLUMN " + tableName + "." + name + " IS '" + column.desc() + "';";
comments.add(comment);
}
});
}
StringBuilder sb = new StringBuilder(buildSQL(tableName, columns)).append("\n");
comments.forEach(comment -> sb.append("\n ").append(comment));
return sb.toString();
}
private static String buildSQL(String tableName, List<String> columns) {
String sql;
StringBuilder sb = new StringBuilder("CREATE TABLE " + tableName + "(");
columns.forEach(column -> {
// 拼接各个字段的定义语句
sb.append("\n ").append(column).append(",");
});
sql = sb.substring(0, sb.length() - 1) + "\n );";
return sql;
}
private static String getOracleType(Class<?> type, SQLColumn column) {
return " VARCHAR2(" + column.size() + ")";
}
private static String getMySQLType(Class<?> type, SQLColumn column) {
if (type.equals(String.class)) {
return "varchar(" + column.size() + ")";
} else if (type.equals(Integer.class)) {
return "int";
} else if (type.equals(Long.class)) {
return "long";
} else if (type.equals(Boolean.class)) {
return "tinyint(1)";
} else if (type.equals(Byte.class)) {
return "tinyint(4)";
} else {
return "varchar(" + column.size() + ")";
}
}
private static String getComment(SQLColumn column) {
String comment = "";
if (column.desc().length() > 0) {
comment = " COMMENT '" + column.desc() + "'";
}
return comment;
}
}
public class StringCaseUtils {
/**
* 将驼峰式命名的字符串转换为下划线大写方式。如果转换前的驼峰式命名的字符串为空,则返回空字符串。
* camelCase -> SNAKE_CASE
*
* @param name 转换前的驼峰式命名的字符串
* @return 转换后下划线大写方式命名的字符串
*/
public static String camelCase2UpperSnackCase(String name) {
StringBuilder result = new StringBuilder();
if (name != null && name.length() > 0) {
// 将第一个字符处理成大写
result.append(name.substring(0, 1).toUpperCase());
// 循环处理其余字符
for (int i = 1; i < name.length(); i++) {
String s = name.substring(i, i + 1);
// 在大写字母前添加下划线
if (s.equals(s.toUpperCase()) && !Character.isDigit(s.charAt(0))) {
result.append("_");
}
// 其他字符直接转成大写
result.append(s.toUpperCase());
}
}
return result.toString();
}
/**
* 将驼峰式命名的字符串转换为下划线小写方式。如果转换前的驼峰式命名的字符串为空,则返回空字符串。
* camelCase -> snake_case
*
* @param name 转换前的驼峰式命名的字符串
* @return snack_case
*/
public static String camelCase2LowerSnackCase(String name) {
StringBuilder result = new StringBuilder();
if (name != null && name.length() > 0) {
// 循环处理其余字符
for (int i = 0; i < name.length(); i++) {
String s = name.substring(i, i + 1);
// 第一个字母就是大写转成小写
if (i == 0 && s.equals(s.toUpperCase())) {
result.append(s.toLowerCase());
continue;
}
// 在大写字母前添加下划线并转换成小写
if (s.equals(s.toUpperCase()) && !Character.isDigit(s.charAt(0))) {
result.append("_").append(s.toLowerCase());
continue;
}
result.append(s);
}
}
return result.toString();
}
}
3 使用
@Data
@DBTable
public class Student {
@SQLColumn(constraints = @Constraints(primaryKey = true), desc = "学号")
private String studentId;
@SQLColumn
private Integer age;
@SQLColumn(desc = "姓名")
private String studentName;
@SQLColumn(desc = "0 女 1 男")
private Boolean sex;
}
public class Main {
public static void main(String[] args) {
String sql = TableCreator.getMySQLSql(Student.class);
System.out.println(sql);
String sql1 = TableCreator.getOracleSql(Student.class);
System.out.println(sql1);
}
}