Loading...

本篇文章使用的是 Hibernate 框架，根据实体自动生成建表 SQL 语句。

# 引入所需要的依赖

	<dependencies>
	<dependency>
	<groupId>javax.persistence</groupId>
	<artifactId>javax.persistence-api</artifactId>
	<version>2.2</version>
	</dependency>

	<dependency>
	<groupId>io.swagger</groupId>
	<artifactId>swagger-annotations</artifactId>
	<version>1.6.5</version>
	</dependency>

	<dependency>
	<groupId>com.fasterxml.jackson.core</groupId>
	<artifactId>jackson-annotations</artifactId>
	<version>2.13.2</version>
	</dependency>

	<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-core</artifactId>
	<version>5.6.7.Final</version>
	</dependency>

	<dependency>
	<groupId>org.apache.commons</groupId>
	<artifactId>commons-lang3</artifactId>
	<version>3.10</version>
	</dependency>
	</dependencies>

# 实体类

Type

	//import 省略...

	@ApiModel(value = "通用类型")
	public class Type {

	private String code;

	private String name;

	//set/get 省略...
	}

AbstractEntity

	@MappedSuperclass
	public class AbstractEntity {

	@Id
	private String id;

	@ApiModelProperty(value = "创建时间")
	@Column(name = "create_time")
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	private LocalDateTime createTime;

	@ApiModelProperty(value = "数据状态")
	@Column(name = "status")
	private String status;

	}

Student

	@Entity
	@Table(schema = "demo", name = "student")
	@AttributeOverrides({
	@AttributeOverride(name = "id",
	column = @Column(name = "id", updatable = false, length = 40))
	})
	@ApiModel(value = "学生实体")
	public class StudentEntity extends AbstractEntity{

	@ApiModelProperty(value = "学号")
	@Column(name = "code")
	private String code;

	@ApiModelProperty(value = "学生姓名")
	@Column(name = "name")
	private String name;

	@ApiModelProperty(value = "学生性别")
	@Embedded
	@AttributeOverrides({
	@AttributeOverride(name = "code", column = @Column(name = "sex_code")),
	@AttributeOverride(name = "name", column = @Column(name = "sex_name"))
	})
	private Type sex;

	@org.hibernate.annotations.Type(type = "jsonb")
	@ApiModelProperty(value = "其他信息")
	@Column(name = "other_info", columnDefinition = "jsonb")
	private Map<String, Object> otherInfo;

	}

# 自动生成 SQL 语句类

	package com.example.demo.utils;

	import com.example.demo.entity.StudentEntity;
	import io.swagger.annotations.ApiModel;
	import org.apache.commons.lang3.StringUtils;
	import org.hibernate.annotations.Type;

	import javax.persistence.AttributeOverride;
	import javax.persistence.AttributeOverrides;
	import javax.persistence.Column;
	import javax.persistence.Embedded;
	import javax.persistence.Table;
	import java.io.FileWriter;
	import java.lang.reflect.Field;
	import java.math.BigDecimal;
	import java.time.LocalDate;
	import java.time.LocalDateTime;
	import java.time.LocalTime;
	import java.util.Arrays;
	import java.util.Map;
	import java.util.stream.Collectors;

	public class CreateTableUtils {

	public static void main(String[] args) {
	// 需要生成 SQl 的类
	Class[] classes;
	classes = new Class[]{
	StudentEntity.class
	};

	StringBuilder fileSql = new StringBuilder();
	for (Class cls :classes){
	// 构建 DDL 语句
	StringBuilder sql = createDDLByClass(cls);
	fileSql.append(sql);
	}

	// 写入文件
	try(FileWriter writer = new FileWriter("CREATE_TABLE_" + System.currentTimeMillis() + ".sql")) {
	writer.write(fileSql.toString());
	System.out.println(fileSql);
	}catch (Exception e){
	e.printStackTrace();
	}
	}

	private static StringBuilder createDDLByClass(Class cls) {
	StringBuilder sql = new StringBuilder();

	handleClass(cls, sql);
	return sql;
	}

	private static void handleClass(Class cls, StringBuilder sql) {
	// 获取表头信息
	Object tableObject = cls.getAnnotation(Table.class);
	Object apiModelObject = cls.getAnnotation(ApiModel.class);

	Primary primary = new Primary();

	// 处理 sql 表头
	buildTable(tableObject, apiModelObject, sql);
	// 处理表头的继承字段
	buildTableAttrs(cls, primary, sql);
	// 处理字段
	buildTableColumn(cls, sql);
	// 处理父类继承字段
	buildSuperClass(cls, sql);
	// 处理主键
	buildPrimary(primary, sql);
	}

	private static void buildPrimary(Primary primary, StringBuilder sql) {
	if (primary != null && StringUtils.isNotBlank(primary.getPrimary1())) {
	if (StringUtils.isNotBlank(primary.getPrimary1())) {
	if (StringUtils.isNotBlank(primary.getPrimary2())) {
	// 兼容双主键
	sql.append("\n\tPRIMARY KEY (").append(primary.getPrimary1()).append(",").append(primary.getPrimary2()).append("));\n");
	} else {
	sql.append("\n\tPRIMARY KEY (").append(primary.getPrimary1()).append("));\n");
	}
	} else {
	if (sql.length() > 1) {
	sql.delete(sql.length() - 1, sql.length());
	}
	sql.append("\n\t);\n");
	}
	}
	}

	private static void buildSuperClass(Class cls, StringBuilder sql) {
	if (cls.getSuperclass() != null) {
	handleClass(cls.getSuperclass(), sql);
	}
	}

	private static void buildTableColumn(Class cls, StringBuilder sql) {
	Field[] fields = cls.getDeclaredFields();
	for (Field field : fields){
	Column column = field.getAnnotation(Column.class);
	AttributeOverrides attributeOverrides = field.getAnnotation(AttributeOverrides.class);
	Embedded embedded = field.getAnnotation(Embedded.class);
	org.hibernate.annotations.Type type = field.getAnnotation(org.hibernate.annotations.Type.class);
	Class fdCls = field.getType();


	/**
	* 处理本类标准字段
	* 如:
	* @ApiModelProperty (value = "数据编码")
	* @Column (name = "code", length = 100)
	* private String code;
	*/
	buildColumn(column, type, fdCls, sql);

	/**
	* 处理标准扩展字段
	* 如:
	* @Embedded
	* @AttributeOverrides ({
	* @AttributeOverride (name = "code", column = @Column (name = "demo_code")),
	* @AttributeOverride (name = "name", column = @Column (name = "demo_name"))
	* })
	* private Type demo;
	*/
	if (embedded != null && attributeOverrides != null) {
	handleAttrs(field.getType(), attributeOverrides, sql);
	}

	/**
	* 处理父类的扩展字段
	* 如:
	* @ApiModelProperty (value = "属性扩展")
	* @Embedded
	* private Extend extend;
	*/
	if (embedded != null && attributeOverrides == null) {
	handleClass(field.getType(), sql);
	}
	}
	}

	/**
	* 处理表头继承的字段
	*
	* @param cls 实体类
	* @param primary 主键
	* @param sql sql
	*/
	private static void buildTableAttrs(Class cls, Primary primary, StringBuilder sql) {
	Object attrsObject = cls.getAnnotation(AttributeOverrides.class);
	if (attrsObject != null){
	AttributeOverrides attrs = (AttributeOverrides) attrsObject;
	Class superClass = cls.getSuperclass();
	handleAttrs(superClass, attrs, sql);


	for (AttributeOverride arr : attrs.value()) {
	if ("id".equals(arr.name())) {
	primary.setPrimary1(arr.column().name());
	}
	// 兼容双主键
	if ("tid".equals(arr.name())) {
	primary.setPrimary2(arr.column().name());
	}
	}
	if (StringUtils.isEmpty(primary.getPrimary1())) {
	primary = null;
	}
	}
	}

	private static void handleAttrs(Class cls, AttributeOverrides attr, StringBuilder sql) {
	if (attr == null) return;

	handleAttrByClass(cls, attr.value(), sql);
	}

	private static void handleAttrByClass(Class cls, AttributeOverride attrs[], StringBuilder sql) {
	Map<String, Field> fieldMap = Arrays.stream(cls.getDeclaredFields()).collect(Collectors.toMap(Field::getName, c -> c, (l, r) -> l));
	for (AttributeOverride attr : attrs){
	// 兼容双主键
	if (StringUtils.equals(attr.name(), "")) continue;

	fieldToColumn(attr.column(), fieldMap, attr.name(), sql);
	}
	}

	private static void fieldToColumn(Column column, Map<String, Field> fieldMap, String name, StringBuilder sql) {
	if (name.contains(".")){
	if (fieldMap.get(StringUtils.substringBefore(name, ".")) != null){
	Field field = fieldMap.get(StringUtils.substringBefore(name, "."));
	handleAttrsNoAttributeOverrides(field.getType(), StringUtils.substringBefore(name, "."), column, sql);
	}
	}else {
	if (fieldMap.get(name) != null){
	Field field = fieldMap.get(name);
	Type type = field.getAnnotation(Type.class);

	buildColumn(column, type, field.getType(), sql);
	}else {
	handleAttr(column, sql);
	}
	}
	}

	/**
	* 构建列字段
	*
	* @param column 列
	* @param type 类型
	* @param cls
	* @param sql
	*/
	private static void buildColumn(Column column, Type type, Class<?> cls, StringBuilder sql) {
	if (column != null) {
	if (cls.equals(String.class)) {
	if (column.length() == 255 && StringUtils.endsWith(column.name(), "id")) {
	sql.append("\n\t").append(column.name()).append(" varchar(40),");
	} else if (column.length() == 255 && StringUtils.endsWith(column.name(), "code")) {
	sql.append("\n\t").append(column.name()).append(" varchar(100),");
	} else if (column.length() == 255 && StringUtils.endsWith(column.name(), "name")) {
	sql.append("\n\t").append(column.name()).append(" varchar(150),");
	} else if (StringUtils.endsWith(column.name(), "status")) {
	sql.append("\n\t").append(column.name()).append(" varchar(").append(column.length()).append(") DEFAULT 'ACTIVE',");
	}else {
	sql.append("\n\t").append(column.name()).append(" varchar(").append(column.length()).append("),");
	}
	}
	if (cls.equals(String[].class)) {
	sql.append("\n\t").append(column.name()).append(" varchar(").append(column.length()).append(") [],");
	}
	if (cls.equals(Boolean.TYPE) \|\| cls.equals(Boolean.class)) {
	sql.append("\n\t").append(column.name()).append(" bool,");
	}
	if (cls.equals(Integer.TYPE) \|\| cls.equals(Integer.class)) {
	if (StringUtils.endsWith(column.name(), "_")) {
	sql.append("\n\t").append(column.name()).append(" int4 DEFAULT 0,");
	} else {
	sql.append("\n\t").append(column.name()).append(" int4,");
	}
	}
	if (cls.equals(Long.TYPE) \|\| cls.equals(Long.class)) {
	if (StringUtils.endsWith(column.name(), "_")) {
	sql.append("\n\t").append(column.name()).append(" int8 DEFAULT 0,");
	} else {
	sql.append("\n\t").append(column.name()).append(" int8,");
	}
	}
	if (cls.equals(Float.TYPE) \|\| cls.equals(Float.class)) {
	sql.append("\n\t").append(column.name()).append(" float,");
	}
	if (cls.equals(Double.TYPE) \|\| cls.equals(Double.class)) {
	sql.append("\n\t").append(column.name()).append(" double precision,");
	}
	if (cls.equals(LocalDateTime.class)) {
	if (StringUtils.endsWith(column.name(), "_")) {
	sql.append("\n\t").append(column.name()).append(" timestamp(6) DEFAULT now(),");
	} else {
	sql.append("\n\t").append(column.name()).append(" timestamp(6),");
	}
	}
	if (cls.equals(LocalDate.class)) {
	sql.append("\n\t").append(column.name()).append(" date,");
	}
	if (cls.equals(LocalTime.class)) {
	sql.append("\n\t").append(column.name()).append(" time,");
	}
	if (cls.equals(BigDecimal.class)) {
	sql.append("\n\t").append(column.name()).append(" numeric(24,6),");
	}
	if (type != null && type.type().equals("jsonb")) {
	sql.append("\n\t").append(column.name()).append(" jsonb,");
	}
	}
	}

	public static void handleAttr(Column arr, StringBuilder sql) {
	if (arr == null) {
	return;
	}

	// 兼容双主键.
	if (StringUtils.equals(arr.name(), "tid")) {
	return;
	}

	if (arr.length() == 255 && StringUtils.endsWith(arr.name(), "id")) {
	sql.append("\n\t").append(arr.name()).append(" varchar(40),");
	} else if (arr.length() == 255 && StringUtils.endsWith(arr.name(), "code")) {
	sql.append("\n\t").append(arr.name()).append(" varchar(100),");
	} else if (arr.length() == 255 && StringUtils.endsWith(arr.name(), "name")) {
	sql.append("\n\t").append(arr.name()).append(" varchar(150),");
	} else if (StringUtils.endsWith(arr.name(), "status")) {
	sql.append("\n\t").append(arr.name()).append(" varchar(").append(arr.length()).append(") DEFAULT 'ACTIVE',");
	} else if (StringUtils.endsWith(arr.name(), "edition")) {
	sql.append("\n\t").append(arr.name()).append(" varchar(").append(arr.length()).append(") DEFAULT '0',");
	} else {
	sql.append("\n\t").append(arr.name()).append(" varchar(").append(arr.length()).append("),");
	}
	}

	private static void handleAttrsNoAttributeOverrides(Class<?> cls, String substringBefore, Column column, StringBuilder sql) {
	Map<String, Field> fieldMap = Arrays.stream(cls.getDeclaredFields()).collect(Collectors.toMap(Field::getName, c -> c, (l, r) -> l));
	fieldToColumn(column, fieldMap, substringBefore, sql);
	}

	/**
	* 构建表头
	*
	* @param tableObject table
	* @param apiModelObject apiModel
	* @param sql sql
	*/
	private static void buildTable(Object tableObject, Object apiModelObject, StringBuilder sql) {
	if (tableObject != null){
	Table table = (Table) tableObject;
	if (apiModelObject != null){
	ApiModel apiModel = (ApiModel) apiModelObject;
	sql.append("\n-- ").append(apiModel.value()).append("表");
	}

	sql.append("\nDROP TABLE IF EXISTS ").append(table.schema()).append(".").append(table.name()).append(";");
	sql.append("\nCREATE TABLE ").append(table.schema()).append(".").append(table.name()).append("(");
	}
	}

	static class Primary {

	private String primary1;
	private String primary2;

	public String getPrimary1() {
	return primary1;
	}

	public void setPrimary1(String primary1) {
	this.primary1 = primary1;
	}

	public String getPrimary2() {
	return primary2;
	}

	public void setPrimary2(String primary2) {
	this.primary2 = primary2;
	}
	}
	}

# 输出 SQL

	-- 学生实体表
	DROP TABLE IF EXISTS demo.student;
	CREATE TABLE demo.student(
	id varchar(40),
	code varchar(100),
	name varchar(150),
	sex_code varchar(100),
	sex_name varchar(150),
	other_info jsonb,
	create_time timestamp(6),
	status varchar(255) DEFAULT 'ACTIVE',
	PRIMARY KEY (id));