SpringBoot 整合 Liquibase

一、 引入依赖

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>4.6.2</version>
</dependency>

二、 配置

1. 单数据源

application.yml

spring:
  liquibase:
    enabled: true
    change-log: classpath:/changelog/changelog-master.xml

changelog-master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog 
  		xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <include file="classpath:/changelog/changelog-1.0.xml"/>
</databaseChangeLog>

2. 多数据源

application.yml

# 多数据源配置
spring:
  datasource:
    admin:
      username: root
      password: 123456
      jdbc-url: jdbc:mysql://localhost:3306/admin
      driver-class-name: com.mysql.jdbc.Driver
      liquibase:
        change-log: classpath:changelog/admin/changelog-master.xml
    web:
      username: root
      password: 123456
      jdbc-url: jdbc:mysql://localhost:3306/web
      driver-class-name: com.mysql.jdbc.Driver
      liquibase:
        change-log: classpath:changelog/web/changelog-master.xml

admin/changelog-master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <include file="classpath:/changelog/admin/changelog-1.0.xml"/>
</databaseChangeLog>

web/changelog-master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <include file="classpath:/changelog/web/changelog-1.0.xml"/>
</databaseChangeLog>

三、changelog-1.0.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd">
  
    <changeSet id="20210101-1" author="cnbai">
        <createTable tableName="t_book">
            <column name="id" type="int(8)" remarks="主键">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="varchar(200)" remarks="书名"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

四、注册 liquibase bean

1. 单数据源

@Configuration
public class LiquibaseConfiguration() {
    
    @Bean
    @ConfigurationProperties(prefix = "spring.liquibase")
    public LiquibaseProperties getLiquibaseProperties() {
        return new LiquibaseProperties();
    }

    @Bean
    public SpringLiquibase liquibase(DataSource dataSource) {
        LiquibaseProperties liquibaseProperties = getLiquibaseProperties();
        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setChangeLog(liquibaseProperties.getChangeLog());
        liquibase.setDataSource(dataSource);       
        liquibase.setShouldRun(true);
        return liquibase;
    }
}

2. 多数据源

@Configuration
public class LiquibaseConfiguration() {

    /**
     *  admin   
     */
    @Bean
    public SpringLiquibase adminLiquibase(AdminDataSource dataSource) {
        SpringLiquibase liquibase = new SpringLiquibase();
        // Liquibase文件路径
        liquibase.setChangeLog("classpath:changelog/admin/changelog-master.xml");
        liquibase.setDataSource(dataSource);
        liquibase.setShouldRun(true);
        liquibase.setResourceLoader(new DefaultResourceLoader());
        // 覆盖Liquibase changelog表名
        liquibase.setDatabaseChangeLogTable("admin_changelog_table");
        liquibase.setDatabaseChangeLogLockTable("admin_changelog_lock_table");
        return liquibase;
    }
    
    /**
     *  web   
     */
    @Bean
    public SpringLiquibase webLiquibase(WebDataSource dataSource) {
      SpringLiquibase liquibase = new SpringLiquibase();
      liquibase.setChangeLog("classpath:changelog/web/changelog-master.xml");
      liquibase.setDataSource(dataSource);
      liquibase.setShouldRun(true);
      liquibase.setResourceLoader(new DefaultResourceLoader());
      liquibase.setDatabaseChangeLogTable("web_changelog_table");
      liquibase.setDatabaseChangeLogLockTable("web_changelog_lock_table");
      return liquibase;
    }
}

五、常用的语法

1. 建表

<changeSet id="20210101-1" author="cnbai">
    <!-- 判断表是否存在 -->
    <preConditions onFail="MARK_RAN">
        <not>
           <tableExists tableName="t_book"/>
        </not>
    </preConditions>
    <!-- comment 不能放在 preConditions 之前，会报错 -->
    <comment>创建新表</comment>
    <!-- createTable 是用于创建新表， createIndex 用于创建索引 -->
    <createTable tableName="t_book" remarks="书">
        <column name="id" type="char(32)" remarks="主键">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(200)" remarks="书名"/>
        <column name="page" type="integer" remarks="书页" defaultValueNumeric="0"/>
        <column name="publish_time" type="timestamp without time zone" remarks="发布时间" defaultValueComputed="now()"/>
        <column name="remark" type="text" remarks="备注"/>
        <column name="is_edit" type="boolean" remarks="true-可编辑，false-不可编辑" defaultValueBoolean="true"/>
        <column name="id_del" type="varchar(3)" remarks="删除标记（1-已删除，0-未删除）" defaultValue="0"/>
    </createTable>
    <createIndex tableName="t_book" indexName="idx_book_name">
        <column name="name"/>
    </createIndex>
</changeSet>

2. 修改字段类型

<changeSet id="20210101-2" author="cnbai">
   <preConditions onFail="MARK_RAN">
      <columnExists tableName="t_user" columnName="sex"/>
   </preConditions>
   <modifyDataType tableName="t_user" columnName="sex" newDataType="varchar(100)" />
</changeSet>

3. 注解重置

<changeSet id="20210101-3" author="cnbai">
   <preConditions onFail="MARK_RAN">
      <columnExists tableName="t_user" columnName="isDelete"/>
   </preConditions>
   <setColumnRemarks tableName="t_user" columnName="isDelete" remarks="是否删除" ></setColumnRemarks>
</changeSet>

4. 删除字段

<changeSet id="20210101-4" author="cnbai">
   <dropColumn tableName="t_user" columnName="address" ></dropColumn>
</changeSet>

5. 增加字段

<changeSet id="20210101-5-1" author="cnbai">
     <!-- 判断字段是否存在 -->
     <preConditions onFail="MARK_RAN">
         <not>
            <columnExists tableName="t_user" columnName="age"/>
         </not>
     </preConditions>
     <comment>增加字段age</comment>
     <addColumn tableName="t_user">
         <column name="age" type="int(2)" remarks="年龄">
         	<!-- 不为空，如果原数据表有数据，增加字段时设置不能为空会报错 -->
         	<constraints nullable="false"/>
         </column>
    </addColumn>
</changeSet>

<changeSet id="20210101-5-2" author="cnbai">
     <!-- 判断字段是否存在 -->
     <preConditions onFail="MARK_RAN">
         <not>
            <columnExists tableName="t_user" columnName="city"/>
         </not>
     </preConditions>
     <comment>增加字段city</comment>
     <addColumn tableName="t_user">
         <column name="city" type="varchar(20)" remarks="城市" defaultValue="西安"/>
    </addColumn>
</changeSet>

6. 修改字段名称

<changeSet id="20210101-6" author="cnbai">
   <preConditions onFail="MARK_RAN">
      <columnExists tableName="t_user" columnName="user_name"/>
   </preConditions>
   <renameColumn tableName="t_user" oldColumnName="user_name" newColumnName="name"/>
</changeSet>

7. 创建唯一约束

<changeSet id="20210101-7" author="cnbai">
    <addUniqueConstraint tableName="t_user" columnNames="authentication_id" />
</changeSet>

8. 创建复合索引

<changeSet id="20210101-8" author="cnbai">
    <createIndex tableName="t_user" indexName="u_index_name_age" unique="true">
        <column name="name"/>
        <column name="age"/>
    </createIndex>
</changeSet>

9. 创建全局序列号

<changeSet id="20210101-9" author="cnbai">
    <createSequence sequenceName="user_sequence" startValue="1" />
</changeSet>

10. 增加数据

<changeSet id="20210101-10" author="cnbai">
    <!-- 判断数据是否存在 -->
    <preConditions onFail="MARK_RAN">
        <sqlCheck expectedResult="0">
           select count(id) from t_user where id = '3'
        </sqlCheck>
    </preConditions>
    <comment>增加数据</comment>
    <insert tableName="t_user">
        <column name="id" value="3" />
        <column name="name" value="王五" />
        <column name="age" value="12" />
    </insert>
</changeSet>

11. sql语句

<!-- runOnChange：每个 changeSet 不能修改，只有引用文件时可以修改文件内容，当 sql 文件发生变化时，执行这些变化 -->
<changeSet id="20210101-11" author="cnbai" runOnChange="true">
    <comment>初始化用户数据</comment>
    <sqlFile path="classpath:changelog/sql/initUser.sql"/>
</changeSet>

DELETE FROM t_user WHERE id != '0';

INSERT INTO t_user ("id", "name") VALUES ('1', 'cnbai');