Liquibase是一个数据库表结构迭代演进变更的管理工具。开发人员可以不是直接针对某个特定的数据库编写SQL的创建、更新或删除数据库对象,而是在通过XML、YAML、JSON等文件中定义描述他们所需的数据库表结构的变更这些变更可以包含tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data。
官网地址:
内置的数据库变更配置文件方式有四种:
1、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"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
<preConditions>
<runningAs username="liquibase"/>
</preConditions>
<changeSet id="1" author="nvoxland">
<createTable tableName="person">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="firstname" type="varchar(50)"/>
<column name="lastname" type="varchar(50)">
<constraints nullable="false"/>
</column>
<column name="state" type="char(2)"/>
</createTable>
</changeSet>
<changeSet id="2" author="nvoxland">
<addColumn tableName="person">
<column name="username" type="varchar(8)"/>
</addColumn>
</changeSet>
<changeSet id="3" author="nvoxland">
<addLookupTable
existingTableName="person" existingColumnName="state"
newTableName="state" newColumnName="id" newColumnDataType="char(2)"/>
</changeSet>
</databaseChangeLog>
2、YAML格式
databaseChangeLog:
- preConditions:
- runningAs:
username: liquibase
- changeSet:
id: 1
author: nvoxland
changes:
- createTable:
tableName: person
columns:
- column:
name: id
type: int
autoIncrement: true
constraints:
primaryKey: true
nullable: false
- column:
name: firstname
type: varchar(50)
- column:
name: lastname
type: varchar(50)
constraints:
nullable: false
- column:
name: state
type: char(2)
- changeSet:
id: 2
author: nvoxland
changes:
- addColumn:
tableName: person
columns:
- column:
name: username
type: varchar(8)
- changeSet:
id: 3
author: nvoxland
changes:
- addLookupTable:
existingTableName: person
existingColumnName:state
newTableName: state
newColumnName: id
newColumnDataType: char(2)
3、JSON格式
{
"databaseChangeLog": [
{
"preConditions": [
{
"runningAs": {
"username": "liquibase"
}
}
]
},
{
"changeSet": {
"id": "1",
"author": "nvoxland",
"changes": [
{
"createTable": {
"tableName": "person",
"columns": [
{
"column": {
"name": "id",
"type": "int",
"autoIncrement": true,
"constraints": {
"primaryKey": true,
"nullable": false
},
}
},
{
"column": {
"name": "firstname",
"type": "varchar(50)"
}
},
{
"column": {
"name": "lastname",
"type": "varchar(50)"
"constraints": {
"nullable": false
},
}
},
{
"column": {
"name": "state",
"type": "char(2)"
}
}
]
}
}
]
}
},
{
"changeSet": {
"id": "2",
"author": "nvoxland",
"changes": [
{
"addColumn": {
"tableName": "person",
"columns": [
{
"column": {
"name": "username",
"type": "varchar(8)"
}
}
]
}
}
]
}
},
{
"changeSet": {
"id": "3",
"author": "nvoxland",
"changes": [
{
"addLookupTable": {
"tableName": "person",
"existingTableName": "person",
"existingColumnName":"state",
"newTableName": "state",
"newColumnName": "id",
"newColumnDataType": "char(2)",
}
}
]
}
}
]
}
4、SQL格式
--liquibase formatted sql
--changeset nvoxland:1
create table person (
id int not null primary key,
firstname varchar(80),
lastname varchar(80) not null,
state varchar(2)
);
--changeset nvoxland:2
alter table person add column username varchar(8)
--changeset nvoxland:3
create table state AS SELECT DISTINCT state AS id FROM person WHERE state IS NOT NULL;
alter table state modify id char(2) NOT NULL;
alter table state add primary key(id);
alter table person add constraint fk_person_state foreign key (state) references state(id);
附:数据库持续交付能力提供者Datical
Datical官网:https://www.datical.com/liquibase/
实例说明:
由于使用了两种数据库(H2和MySQL)来验证,故同时加载两种数据库驱动
依赖配置
<?xml version="1.0" encoding="UTF-8"?>
<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>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.feng</groupId>
<artifactId>liqiubase-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>liqiubase-demo</name>
<description>Liqiubase测试</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
LiqiubaseConfig.java
import liquibase.integration.spring.SpringLiquibase;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class LiquibaseConfig {
@Bean
public SpringLiquibase liquibase(DataSource dataSource) {
SpringLiquibase springLiquibase = new SpringLiquibase();
springLiquibase.setDataSource(dataSource);// 数据源直接在application.yml或application.properties中配置
springLiquibase.setChangeLog("classpath:db/db.changelog-master.yml");
springLiquibase.setContexts("development,test,production");
springLiquibase.setShouldRun(true);
return springLiquibase;
}
}
application.yml(使用MySQL时)配置
spring:
application:
name: liqiubase-demo
h2:
console:
enabled: true
# datasource:
# url: jdbc:h2:file:~/.h2/testdb
datasource:
driver-class-name: com.mysql.jdbc.Driver
username: root
password:
url: jdbc:mysql://localhost:3309/liqiubase-demo?useSSL=false&useUnicode=true&characterEncoding=UTF-8
liquibase:
enabled: true
server:
port: 62
application.yml(使用H2时)配置
spring:
application:
name: liqiubase-demo
h2:
console:
enabled: true #用于启动H2 Web管理界面
datasource:
url: jdbc:h2:file:~/.h2/testdb
# datasource:
# driver-class-name: com.mysql.jdbc.Driver
# username: root
# password:
# url: jdbc:mysql://localhost:3309/liqiubase-demo?useSSL=false&useUnicode=true&characterEncoding=UTF-8
liquibase:
enabled: true
server:
port: 62
resources/db/db.changelog-master.yml
databaseChangeLog:
- changeSet:
id: 1
author: nvoxland
changes:
- createTable:
tableName: person
columns:
- column:
name: id
type: int
autoIncrement: true
constraints:
primaryKey: true
nullable: false
- column:
name: firstname
type: varchar(50)
- column:
name: lastname
type: varchar(50)
constraints:
nullable: false
- column:
name: state
type: char(2)
- changeSet:
id: 2
author: nvoxland
changes:
- addColumn:
tableName: person
columns:
- column:
name: username
type: varchar(8)
- changeSet:
id: 3
author: nvoxland
changes:
- addLookupTable:
existingTableName: person
existingColumnName: state
newTableName: state
newColumnName: id
newColumnDataType: char(2)
启动项目之后,目标数据库(MySQL)根据更变日志,自动创建表
另:H2数据库的管理界面路径 http://localhost:62/h2-console
说明:相应的截图没有上传,见谅
源码:https://github.com/jianbo-feng/public-repository (觉得可以的大佬,点个赞)