The note records springboot Integrate liquibase after , How to base on liquibase ChangeLogFile Modify and roll back the database
Reference resources ：
baeldung.com
JHipster
1. utilize changeLog File to modify the database
- introduce liquibase rely on
- stay resource New under the directory db.changelog-master.xml As a changeset file
- modify application Join in liquibase Configuration of , Main configuration changeset file location
- Run the project , You can modify the database according to the content of the changeset file
master.xml Simple format ：
<?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:pro="http://www.liquibase.org/xml/ns/pro" 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/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.10.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd">
<changeSet author="chunmiaoz (generated)" id="1604474279717-1">
<createTable tableName="user">
<column name="id" type="BIGINT">
<constraints nullable="false" primaryKey="true"/>
</column>
<column name="phone" type="VARCHAR(11)"/>
<column name="username" type="VARCHAR(255)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
2. Generate from an existing database table changeLog file
- Create tables in the database
- Create a new one liquibase.properties And configure the database address 、 User name, password 、 drive
- There is liquibase.properties Open in the directory of cmd, Enter the command
liquibase --changeLogFile=dbchangelog.xml generateChangeLog
It will automatically generate a dbchangelog.xml The target file of
Liquibase The corresponding type of change set file can be generated according to the suffix name of the target file , Such as .yaml Will generate yaml Format
liquibase.properties:
# Enter the path for your changelog file.
changeLogFile=dbchangelog.xml
#### Enter the Target database 'url' information ####
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC&createDatabaseIfNotExist=true
# Enter the username for your Target database.
username: root
# Enter the password for your Target database.
password: 123456
3. From using JPA Derived jar Package generation changeLog file
- Create a new project , introduce jpa rely on
- modify application, To configure jpa Of ddl-auto The attribute is create
- New entity class
- Package the project as jar file
- stay jar File directory , newly build liquibase.properties And configure the corresponding properties
- Open in this directory cmd, Enter the command
Liquibase --logLevel=INFO --defaultsFile=liquibase.properties generateChangeLog
It can be generated automatically dbchangelog.xml file
Liquibase.properties：
# jar Package address
classpath=liquibase-demo-0.0.1-SNAPSHOT.jar
# Enter the path for your changelog file.
changeLogFile=dbchangelog.xml
#### Enter the Target database 'url' information ####
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC&createDatabaseIfNotExist=true
# Enter the username for your Target database.
username: root
# Enter the password for your Target database.
password: 123456
4. Roll back Liquibase Generated database
utilize liquibase The order of
liquibase update
Generated database form , You can use the command to roll back the database
liquibase rollbackCount 1 // Roll back a record
liquibase rollbackToDate YYYY-MM-DD HH:MM:SS // Roll back to the specified date
5. Springboot Integrated liquibase Rollback operation
By learning from jhipster Of liquibase Configuration structure completes this function
Premise ：liquibase When rolling back , requirement databasechangelog Consistent with the change set file .
- In the project ,resource Directory configuration master.xml Master changeset file , The goal is to make the child change set include Come in
<?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:pro="http://www.liquibase.org/xml/ns/pro" 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/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.10.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd">
<include file="db/changelog/202011051610-added-person.xml" relativeToChangelogFile="false"></include>
</databaseChangeLog>
- newly build 202011051610-added-person.xml Child changeset files , The position is src\main\resources\db\changelog\db.changelog-master.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:pro="http://www.liquibase.org/xml/ns/pro" 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/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.10.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd">
<changeSet author="chunmiaoz (generated)" id="1604474279717-1">
<createTable tableName="user">
<column name="id" type="BIGINT">
<constraints nullable="false" primaryKey="true"/>
</column>
<column name="phone" type="VARCHAR(11)"/>
<column name="username" type="VARCHAR(255)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet id="202011041617-added-person" author="Chunmiao">
<createTable tableName="person">
<column name="id" type="INT">
<constraints primaryKey="true" nullable="false" unique="true" ></constraints>
</column>
<column name="name" type="VARCHAR(255)">
<constraints nullable="false"></constraints>
</column>
<column name="address" type="VARCHAR(255)"></column>
<column name="city" type="VARCHAR(255)"></column>
</createTable>
</changeSet>
<changeSet id="202011041621-added-company" author="Chunmiao">
<createTable tableName="company">
<column name="id" type="INT">
<constraints primaryKey="true" nullable="false" unique="true"></constraints>
</column>
<column name="name" type="VARCHAR(255)">
<constraints nullable="false"></constraints>
</column>
<column name="address" type="VARCHAR(255)"></column>
<column name="city" type="VARCHAR(255)"></column>
</createTable>
</changeSet>
<changeSet id="202011041621-alert-person" author="Chunmiao">
<addColumn tableName="person">
<column name="country" type="VARCHAR(2)">
</column>
</addColumn>
<rollback>
<dropColumn tableName="person" columnName="country"></dropColumn>
</rollback>
</changeSet>
</databaseChangeLog>
- modify pom.xml, by maven increase liquibase plug-in unit
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<configuration>
<changeLogFile>${basedir}/src/main/resources/db/changelog/db.changelog-master.xml</changeLogFile>
<url>jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8</url>
<username>root</username>
<password>123456</password>
</configuration>
</plugin>
</plugins>
- Run the project , You can find liquibase The database table will be created automatically
- Perform a rollback operation , Use... On the command line maven command
mvn liquibase:rollback -Dliquibase.rollbackCount=1 - utilize maven To perform a changeset task, you can use the command
mvn liquibase:update
Known problems ：
stay springBoot in , because liquibase update By springBoot Accomplished , Manually on the command line changeLog Perform a rollback operation , Although it will prompt success , But the data in the database is not actually rolled back .
reason ：
springBoot in changelogFile The position is classpath:db/changelog/db.changelog-master.xml
And on the command line , Cannot represent file location as classpath: In the form of , Different file names will be liquibase Think of it as different version management
resolvent ：
take changeSet Defined in the child changeset , Introduce a child changeset in the master changeset , So no matter in what circumstances ,changeSet The paths are represented as the same as those configured in the master changeset , Thus avoiding the conflict of different paths