痛点
之前一直使用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的字段类型,需要指定数据库类型,如果要切换数据库,需要做实体代码定义的改动