springboot项目兼容liquibase、h2base
项目之初是springboot+liquibase+mysql,后来考虑不通过远程数据库本地直接可以执行,因此就引入了h2base
maven引入:
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.200</version>
</dependency>
1.多数据源配置h2数据库(dynamic 数据源)
dynamic:
primary: master #设置默认的数据源或者数据源组,默认值即为master
strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
datasource:
master:
driver-class-name: org.h2.Driver
url: jdbc:h2:file:./db/test;MODE=MYSQL
username: root
password:
2.修改liquibase
-
properties
url=jdbc:h2:file:./db/test username=root password= outputChangeLogFile=src/main/resources/liquibase/changelog/changeLog.xml driver=org.h2.Driver liquibase.enabled=true liquibase.change-log=classpath:changeLog.xml spring.jpa.hibernate.ddl-auto=none
启动项目,报错:
-
changelog.xml报错,comment 表、字段注解插入语句报错
-
解决办法:druid wallFilter 校验问题,对应application-dev.yml中
-
druid: #注意是在dynamic层配置 filters: stat wall: comment-allow: true
-
-
changelog.xml中创建表,mysql true false默认转换为0,1 数据类型为varchar(1),但是h2base中将其转为字符串,因此超出字段范围报错
- 解决办法:将对应字段转换为BOOLEAN
-
changelog.xml中插入数据时,json类型字段在h2base中不识别,类型转换报错,mysql没问题
- 解决办法:将对应字段转换为varchar(100)sys_menu,sys_dict_data问题解决
-
quartz对应表约束问题(h2对应约束有变动 changelog.xml)
- 解决办法:查询quartz多种数据库表结构(https://gitee.com/JiangBeiQingYi/quartzJiQunShuJuKuBiaoJieGou)做对应修改(联合主键)
针对h2,mysql数据库执行changelog.xml兼容问题(不同字段不同处理)
changeSet中加入,对于不同数据库加入不同的changeset操作
<preConditions onFail="CONTINUE">
<dbms type="mysql"/>
</preConditions>
onFail 四个参数说明: (默认: HALT)
HALT | Halts the execution of the entire changelog (default). HALT can be put outside a changeset (e.g. at the beginning of the changelog).停止整个changelog行为,HALT可以放在changeset外部,例如在changelog的开头 |
---|---|
CONTINUE | Skips over the changeset. Execution of the changeset will be attempted again on the next update. Continues with the changelog.跳过changeset,下次更新时会再次尝试执行changeset,继续执行changelog |
MARK_RAN | Skips over the changeset but mark it as executed. Continues with the changelog.跳过changeset,但是将其标记为已执行,继续执行changelog |
WARN | Sends a warning and continues executing the changeset / changelog as normal. WARN can be put outside a changeset (e.g. at the beginning of the changelog).发送警告并继续正常执行changeset、changelog 警告可以放在changelog的开头 |
3.遗留问题(已解决)
项目启动过程没有问题了,大部分功能也检测没有问题
-
-
代码生成问题
因为不同数据库的information_schema.tables是不同的,因此在代码生成导入表的过程中,查询不到对应的表,mysql数据库语句:
<select id="selectDbTablePage" resultMap="GenTableResult"> select table_name, table_comment, create_time, update_time from information_schema.tables where table_schema = (select database()) <foreach item="item" index="index" collection="list"> AND table_name NOT LIKE #{item} </foreach> AND table_name NOT IN (select table_name from gen_table) <if test="genTable.tableName != null and genTable.tableName != ''"> AND lower(table_name) like lower(concat('%', #{genTable.tableName}, '%')) </if> <if test="genTable.tableComment != null and genTable.tableComment != ''"> AND lower(table_comment) like lower(concat('%', #{genTable.tableComment}, '%')) </if> <if test="genTable.beginTime != null and genTable.beginTime != ''"><!-- 开始时间检索 --> AND date_format(create_time,'%y%m%d') >= date_format(#genTable.{beginTime},'%y%m%d') </if> <if test="genTable.endTime != null and genTable.endTime != ''"><!-- 结束时间检索 --> AND date_format(create_time,'%y%m%d') <= date_format(#{genTable.endTime},'%y%m%d') </if> </select>
但是h2数据库中,information_schema.tables中只包含table_name,因此需要做对应修改,但是如果又切换其他数据库,如何做兼容处理???
h2base 中表字段都为大写,查询可导入表sql
SELECT * FROM information_schema.TABLES WHERE table_schema = 'PUBLIC' AND table_name NOT LIKE 'QRTZ_%' AND table_name NOT LIKE 'GEN_%' AND table_name NOT LIKE 'SYS_%' AND table_name NOT LIKE 'DATABASECHANGELOG%' AND table_name NOT IN (SELECT table_name FROM gen_table)
mysql:
SELECT * FROM information_schema.TABLES WHERE table_schema = (SELECT DATABASE()) AND table_name NOT LIKE 'qrtz_%' AND table_name NOT LIKE 'gen_%' AND table_name NOT LIKE 'sys_%' AND table_name NOT LIKE 'databasechangelog%' AND table_name NOT IN ( SELECT table_name FROM gen_table )
解决办法:
1.在代码中增加获取当前主数据源方法,然后对于代码生成的逻辑进行了mysql、h2数据库的分支逻辑操作,对应后端dao、 mapper.xml进行了区分操作。
2.liquibase中也对应增加了两种库的创建表和约束(在同一changelog中,对应changeset加入了preCondations操作,非对应数据库的changeset跳过)
-
4.调整
将之前通过代码中获取dataBase driver的方式换为通过 mybatis方言 databaseId来实现:
1.在启动类中添加Bean
@Bean
public DatabaseIdProvider getDatabaseIdProvider() {
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties properties = new Properties();
properties.setProperty("MySQL", "mysql");
properties.setProperty("H2", "h2");
databaseIdProvider.setProperties(properties);
return databaseIdProvider;
}
2.在对应mapper.xml中同样的语句中添加databaseid区别 目前支持mysql、h2
*扩展支持数据库只要在启动类中添加数据库类型,在对应mapper.xml中添加对应新库的同方法语句和databaID区