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: false
    show-sql: false
    open-in-view: false
    properties:
      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();
	}

	@Bean
	public 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: false
  mode: ${app.init.mode}
  model-package: org.ccframe.subsys.*.domain.entity
  index-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) @ColumnNotNull
	private String userAvatar;

	@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 32) @ColumnNotNull
	private String userName;

	@ColumnType(value = MysqlTypeConstant.VARCHAR, length = 128) @ColumnNotNull
	private 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;
    
}

总结

AutoTable能够很好的兼容JPA的格式，实现自动建表的迁移。

但是有几个注意点：

1）索引需要进行命名，hibernate的是采用自动前缀+25位字符来自动实现索引的命名，我们不用去关心索引的名称。而迁移到AutoTable需要去起个不重复的名字。这个问题不大

2）hibernate采用方言的模式，可以兼容大部分数据库。而AutoTable的字段类型，需要指定数据库类型，如果要切换数据库，需要做实体代码定义的改动