liquibase使用

1. 创建表

drop database if exists mybatis;
create database mybatis;
use mybatis;

create table mybatis.CUSTOMERS (
  ID bigint not null primary key,
  NAME varchar(15) not null,
  EMAIL varchar(128) not null,
  PASSWORD varchar(8) not null,  
  PHONE int ,  
  ADDRESS varchar(255),
  SEX char(1) ,
  IS_MARRIED bit,
  DESCRIPTION text,
  IMAGE blob,
  BIRTHDAY date,
  REGISTERED_TIME timestamp
);


select * from mybatis.CUSTOMERS;

2. 配置pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>com.example</groupId>
        <artifactId>middleware</artifactId>
        <version>0.0.1-SNAPSHOT</version>
    </parent>
    <artifactId>liquibase</artifactId>

    <properties>
        <jdbc.driver>com.mysql.cj.jdbc.Driver</jdbc.driver>
        <jdbc.url>jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=utf8&amp;serverTimezone=GMT&amp;useSSL=false&amp;allowPublicKeyRetrieval=true</jdbc.url>
        <jdbc.username>root</jdbc.username>
        <jdbc.password>1234</jdbc.password>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>

            <plugin>
                <groupId>org.liquibase</groupId>
                <artifactId>liquibase-maven-plugin</artifactId>
                <version>3.5.3</version>
                <configuration>
                    <!--指定执行主文件 -->
                    <changeLogFile>${basedir}/src/main/resources/conf/liquibase/master_changelog.xml</changeLogFile>
                    <diffChangeLogFile>${basedir}/src/main/resources/conf/liquibase/changelog/${maven.build.timestamp}_changelog.xml</diffChangeLogFile>
                    <outputChangeLogFile>${basedir}/src/main/resources/conf/liquibase/changelog/changelog_original.xml</outputChangeLogFile>

                    <driver>${jdbc.driver}</driver>
                    <url>${jdbc.url}</url>
                    <username>${jdbc.username}</username>
                    <password>${jdbc.password}</password>

                    <dropFirst>false</dropFirst>
                    <defaultSchemaName />
                    <referenceUrl>hibernate:spring:com.jaguar.myapp.domain?dialect=&amp;hibernate.ejb.naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringNamingStrategy</referenceUrl>
                    <verbose>true</verbose>
                    <logging>debug</logging>

                    <!-- 是否需要弹出确认框 -->
                    <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
                    <!--输出文件的编码 -->
                    <outputFileEncoding>UTF-8</outputFileEncoding>
                    <!--执行的时候是否显示详细的参数信息 -->
                    <verbose>true</verbose>
                    <!--是否每次都重新加载properties -->
                    <propertyFileWillOverride>true</propertyFileWillOverride>
                    <rollbackTag>${project.version}</rollbackTag>
                    <tag>${project.version}</tag>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

        <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>3.6.1</version>
        </dependency>
    </dependencies>
</project>

3. 根据数据库反向生成changeLog文件    mvn liquibase:generateChangeLog

创建空changelog_original.xml

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

执行 mvn liquibase:generateChangeLog

changelog_original.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <changeSet author="kd (generated)" id="1529903520054-1">
        <createTable tableName="customers">
            <column name="ID" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="NAME" type="VARCHAR(15)">
                <constraints nullable="false"/>
            </column>
            <column name="EMAIL" type="VARCHAR(128)">
                <constraints nullable="false"/>
            </column>
            <column name="PASSWORD" type="VARCHAR(8)">
                <constraints nullable="false"/>
            </column>
            <column name="PHONE" type="INT"/>
            <column name="ADDRESS" type="VARCHAR(255)"/>
            <column name="SEX" type="CHAR(1)"/>
            <column name="IS_MARRIED" type="BIT(1)"/>
            <column name="DESCRIPTION" type="TEXT"/>
            <column name="IMAGE" type="BLOB"/>
            <column name="BIRTHDAY" type="date"/>
            <column name="REGISTERED_TIME" type="TIMESTAMP(26)"/>
        </createTable>
    </changeSet>
    <changeSet author="kd (generated)" id="1529903520054-2">
        <addPrimaryKey columnNames="ID" constraintName="PRIMARY" tableName="customers"/>
    </changeSet>
</databaseChangeLog>

4. 清空当前数据库，包括liquibase的版本信息 mvn liquibase:dropAll

5. 将xml的改变更新到数据库     mvn liquibase:update

(1) 指定执行主文件 master_changelog.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.5.xsd">

    <!-- mvn liquibase:update -->
    <include file="conf/liquibase/changelog/00000000000000_initial_common.xml" relativeToChangelogFile="false"/>
</databaseChangeLog>

(2) 初始化的文件 00000000000000_initial_common.xml

拷贝自changelog_original.xml

两个重要改动

a) 定义了autoIncrement， 关联

b) 将TIMESTAMP(26) 改为TIMESTAMP

<?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.5.xsd">

    <property name="now" value="now()" dbms="mysql,h2"/>
    <property name="now" value="current_timestamp" dbms="postgresql"/>
    <property name="now" value="sysdate" dbms="oracle"/>

    <property name="autoIncrement" value="true" dbms="mysql,h2,postgresql,oracle"/>

     <changeSet author="kd (generated)" id="1529903520054-1">
        <createTable tableName="customers">
            <column name="ID" type="BIGINT" autoIncrement="${autoIncrement}">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="NAME" type="VARCHAR(15)">
                <constraints nullable="false"/>
            </column>
            <column name="EMAIL" type="VARCHAR(128)">
                <constraints nullable="false"/>
            </column>
            <column name="PASSWORD" type="VARCHAR(8)">
                <constraints nullable="false"/>
            </column>
            <column name="PHONE" type="INT"/>
            <column name="ADDRESS" type="VARCHAR(255)"/>
            <column name="SEX" type="CHAR(1)"/>
            <column name="IS_MARRIED" type="BIT(1)"/>
            <column name="DESCRIPTION" type="TEXT"/>
            <column name="IMAGE" type="BLOB"/>
            <column name="BIRTHDAY" type="date"/>
            <column name="REGISTERED_TIME" type="TIMESTAMP"/>
        </createTable>
    </changeSet>


    <changeSet id="00000000000000-05" author="shj">
        <sqlFile path="conf/liquibase/preloaddata/dml.sql"/>
    </changeSet>
</databaseChangeLog>

(3) dml.sql

Insert into mybatis.CUSTOMERS (NAME,EMAIL,PASSWORD, PHONE,  ADDRESS,SEX,IS_MARRIED,DESCRIPTION,IMAGE,BIRTHDAY,REGISTERED_TIME) 
values ('customer','customer@customer.com','1234',123,'customer address','女',1,'customer description',null,now(),now());

执行结果

alter table mybatis.CUSTOMERS add test varchar(25);

再次update...

6. 根据数据库反向生成changeLog文件

执行 mvn liquibase:dbDoc

最常用的命令说明：

update（将xml的改变更新到数据库）

rollback（回滚到某一版本或者某一时刻，必须要带上rollbackTag参数）

dbDoc （生成数据库文档）

dropAll（慎用，清空当前数据库，包括liquibase的版本信息）

generateChangeLog（根据数据库反向生成changeLog文件）

tag（为当前数据库打上标签）