AutoTable, Hibernate自动建立表替代方案

痛点

之前一直使用JPA为主要ORM技术栈，主要是因为Mybatis没有实体逆向建表功能。虽然Mybatis有从数据库建立实体，但是实际应用却没那么美好：当实体变更时，往往不会单独再建立一个数据库重新生成表，然后把表再逆向为实体。最终的结果往往是维护一份数据库SQL，再同时维护一份实体对象，两者没有自动建立关联。

方案

如果能够自动建立表，并自动维护系统初始的数据，该有多方便啊。

笔者实际的经验，十年前已经实现Hibernate自动建表+DBUnit自动初始数据（包括图片和相对数据，可见笔者其它文章）。

然而世界在发展，痛点终究有大牛出来解决，在Mybatis领域，最近出现了一个的替代解决方案：MybatisPlusExt，简称MPE。其中的自动建表已被MPE作者单独一个项目处理，叫做AutoTableAuto Table)自动维护表结构https://autotable.tangzc.com/

迁移步骤

配置文件

autotable也有springboot starter。重新建表的逻辑，也有JAP类似的参数，因此，很容易可以进行迁移，改动点如下：

JPA

spring:jpa:database-platform: ${app.dataSource.hibernateDialect}generate-ddl: falseshow-sql: falseopen-in-view: falseproperties:hibernate.jdbc.time_zone: ${app.timeZone:GMT+8}

以及config里的配置Bean:

	@Value("${app.init.mode:none}")private String initMode;@Bean@ConfigurationProperties(prefix = "spring.datasource")public JpaVendorAdapter jpaVendorAdapter() {return new HibernateJpaVendorAdapter();}@Beanpublic LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource, JpaVendorAdapter jpaVendorAdapter){LocalContainerEntityManagerFactoryBean bean=new LocalContainerEntityManagerFactoryBean();      bean.setDataSource(dataSource);bean.setPackagesToScan(new String[] {"org.ccframe.subsys.*.domain.entity"});bean.setJpaVendorAdapter(jpaVendorAdapter);bean.getJpaPropertyMap().put("hibernate.hbm2ddl.auto",initMode);return bean;}

AutoTable

先导入starter依赖：


implementation ("com.tangzc:auto-table-spring-boot-starter:1.7.4") // 自动建表

然后书写格式：

auto-table:show-banner: falsemode: ${app.init.mode}model-package: org.ccframe.subsys.*.domain.entityindex-prefix: IDX_

Entity实体

以一个典型的带普通和Unique索引的实体User为例：

JPA

JPA+Hibernate方案

@Entity
@Table(name = "SYS_USER", indexes = {@Index(columnList = "USER_MOBILE"),@Index(columnList = "USER_EMAIL"),
}, uniqueConstraints = {@UniqueConstraint(columnNames = {"PLATFORM_ID","LOGIN_ID","USER_PSW"}),
})
@Getter
@Setter
@ToString
public class User extends BaseEntity{private static final long serialVersionUID = 6662916002685367792L;public static final String USER_ID = "userId";public static final String PLATFORM_ID = "platformId";public static final String LOGIN_ID = "loginId";public static final String USER_HEAD_PICT_ID = "userHeadPictId";public static final String USER_NAME = "userName";public static final String USER_PSW = "userPsw";public static final String USER_MOBILE = "userMobile";public static final String USER_EMAIL = "userEmail";public static final String USER_STATUS_CODE = "userStatusCode";public static final String IF_ADMIN = "ifAdmin";public static final String ROLE_CODE_STR = "roleCodeStr";@Id@GenericGenerator(name = "userId", strategy = "org.ccframe.commons.base.RedisIDGenerator")@GeneratedValue(generator = "userId")@Column(name = "USER_ID", nullable = false, length = 10)private Integer userId;@Column(name = "PLATFORM_ID", nullable = false, length = 10)private Integer platformId;@Column(name = "LOGIN_ID", nullable = false, length = 38)@Field(type = FieldType.Keyword)private String loginId;@Column(name = "USER_HEAD_PICT_ID", nullable = true, length = 10)private java.lang.Integer userHeadPictId;@Column(name = "USER_NAME", nullable = false, length = 32)private String userName;@Column(name = "USER_PSW", nullable = false, length = 128)private String userPsw;@Column(name = "USER_MOBILE", nullable = true, length = 17)private String userMobile;@Column(name = "USER_EMAIL", nullable = true, length = 70)private String userEmail;@Column(name = "IF_ADMIN", nullable = false, length = 2)@Field(type = FieldType.Keyword)private String ifAdmin;@Column(name = "USER_STATUS_CODE", nullable = false, length = 2)private String userStatusCode;@Column(name = "ROLE_CODE_STR", nullable = false, length = 80)private String roleCodeStr;
}

AutoTable

mybatis-plus + autotable的方案（原来platformId重新命名为规范的tenantId）

@TableName("SYS_USER")
@AutoTable("SYS_USER")
@TableIndex(name = "UK66q7srks5eylhocxej5gs68mb", type= IndexTypeEnum.UNIQUE, fields = {"tenantId","loginId","userPsw"})
@TableIndex(name = "IDXbby41q9neesp2i6hatmlud01b", fields = "userMobile")
@TableIndex(name = "IDXhjkdbn8wxvwcdp7ohh7dch6i1", fields = "userEmail")
@Getter
@Setter
@ToString
public class User extends BaseEntity{private static final long serialVersionUID = 6662916002685367792L;public static final String USER_ID = "userId";public static final String TENANT_ID = "tenantId";public static final String LOGIN_ID = "loginId";public static final String USER_AVATAR = "userAvatar";public static final String USER_NAME = "userName";public static final String USER_PSW = "userPsw";public static final String USER_MOBILE = "userMobile";public static final String USER_EMAIL = "userEmail";public static final String USER_STATUS_CODE = "userStatusCode";public static final String IF_ADMIN = "ifAdmin";public static final String ROLE_CODE_STR = "roleCodeStr";@TableId(type = IdType.ASSIGN_ID)private Long userId;@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 38) @ColumnNotNull@Field(type = FieldType.Keyword)private String loginId;@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 48) @ColumnNotNullprivate String userAvatar;@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 32) @ColumnNotNullprivate String userName;@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 128) @ColumnNotNullprivate String userPsw;@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 17)private String userMobile;@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 70)private String userEmail;@Field(type = FieldType.Keyword)@ColumnType(value = MysqlTypeConstant.CHAR, length = 1)private String ifAdmin;@ColumnType(value = MysqlTypeConstant.CHAR, length = 1)private String userStatusCode;@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 80)private String roleCodeStr;}