SpringBoot 整合 liquibase

最新推荐文章于 2024-08-26 08:23:54 发布

长庚~Richarlie

最新推荐文章于 2024-08-26 08:23:54 发布

阅读量565

点赞数

分类专栏：数据库文章标签： liquibase 数据库差异

原文链接：https://cloud.tencent.com/developer/article/1972849

版权

数据库专栏收录该内容

76 篇文章 7 订阅

订阅专栏

LiquiBase是一个用于数据库重构和迁移的开源工具，通过日志文件的形式记录数据库的变更，然后执行日志文件中的修改，将数据库更新或回滚到一致的状态。它的目标是提供一种数据库类型无关的解决方案，通过执行schema类型的文件来达到迁移。其有点主要有以下：

支持几乎所有主流的数据库，如MySQL, PostgreSQL, Oracle, Sql Server, DB2等；
支持多开发者的协作维护；
日志文件支持多种格式，如XML, YAML, JSON, SQL等；
支持多种运行方式，如命令行、Spring集成、Maven插件、Gradle插件等。

liquibase 官方文档地址：http://www.liquibase.org/documentation/index.html

一、引入依赖

先在 pom 文件里引入依赖

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

复制

二、指定配置文件位置

在代码中新建一个 LiquibaseConfig 类，用于配置 Liquibase，指定配置文件的位置。

import javax.sql.DataSource;
import liquibase.integration.spring.SpringLiquibase;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class LiquibaseConfig {
  @Bean
  public SpringLiquibase liquibase(DataSource dataSource) {
    SpringLiquibase liquibase = new SpringLiquibase();
    liquibase.setDataSource(dataSource);
    //指定changelog的位置，这里使用的一个master文件引用其他文件的方式
    liquibase.setChangeLog("classpath:liquibase/master.xml");
    liquibase.setContexts("development,test,production");
    liquibase.setShouldRun(true);
    return liquibase;
  }
}

复制

三、编写配置文件

目录结构：

src/main/resources 下新建一个文件夹：liquibase，用来存放跟 liquibase 相关的文件。

master.xml

然后在 liquibase 文件夹下新建 master.xml 作为主文件。

<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">

<span class="hljs-tag"><<span class="hljs-name">includeAll</span> <span class="hljs-attr">path</span>=<span class="hljs-string">"liquibase/changelogs/"</span> <span class="hljs-attr">relativeToChangelogFile</span>=<span class="hljs-string">"false"</span>/></span>

</databaseChangeLog>

复制

includeAll 标签可以把一个文件夹下的所有 changelog 都加载进来。如果单个加载可以用 include。

includeAll 标签里有两个属性：path 和 relativeToChangelogFile。

Attribute	Description
file	Name of the file to import required
relativeToChangelogFile	Is the file path relative to the root changelog file rather than to the classpath. Defaults to “false” since 1.9

path （在 include 标签里是 file）：指定要加载的文件或文件夹位置

relativeToChangelogFile ：文件位置的路径是否相对于 root changelog 是相对路径，默认 false，即相对于 classpath 是相对路径。

changelog

另在 liquibase 文件夹下新建 changelogs 文件夹用来存放 changelog。

这里新建一个 changelog-1.0.xml

<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">

<span class="hljs-tag"><<span class="hljs-name">changeSet</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"20190713-01"</span> <span class="hljs-attr">author</span>=<span class="hljs-string">"solo"</span>></span>
    <span class="hljs-tag"><<span class="hljs-name">createTable</span> <span class="hljs-attr">tableName</span>=<span class="hljs-string">"project_info"</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目id"</span>></span>
            <span class="hljs-tag"><<span class="hljs-name">constraints</span> <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">"true"</span> <span class="hljs-attr">nullable</span>=<span class="hljs-string">"false"</span>/></span>
        <span class="hljs-tag"></<span class="hljs-name">column</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_name"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(255)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目名字"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_difficulty"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"float"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目难度"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"category_id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目类型类目编号"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_status"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"int(11)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目状态, 0招募中，1 进行中，2已完成，3失败，4延期，5删除"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_desc"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(512)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目简介"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_creater_id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目创建者id"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"team_id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目所属团队id"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"create_time"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"bigint(64)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"创建时间"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"update_time"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"bigint(64)"</span> <span class="hljs-attr">encoding</span>=<span class="hljs-string">"utf8"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"更新时间"</span>/></span>
    <span class="hljs-tag"></<span class="hljs-name">createTable</span>></span>
<span class="hljs-tag"></<span class="hljs-name">changeSet</span>></span>

<span class="hljs-tag"><<span class="hljs-name">changeSet</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"20190713-02"</span> <span class="hljs-attr">author</span>=<span class="hljs-string">"solo"</span>></span>
    <span class="hljs-tag"><<span class="hljs-name">createTable</span> <span class="hljs-attr">tableName</span>=<span class="hljs-string">"project_category"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目类型表"</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目类型id"</span>></span>
            <span class="hljs-tag"><<span class="hljs-name">constraints</span> <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">"true"</span> <span class="hljs-attr">nullable</span>=<span class="hljs-string">"false"</span>/></span>
        <span class="hljs-tag"></<span class="hljs-name">column</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"name"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(255)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"类目类型名称"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"status"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"int(11)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"状态。1正常，2删除"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"remark"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(255)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"备注"</span>/></span>
    <span class="hljs-tag"></<span class="hljs-name">createTable</span>></span>
<span class="hljs-tag"></<span class="hljs-name">changeSet</span>></span>

<span class="hljs-tag"><<span class="hljs-name">changeSet</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"20190713-03"</span> <span class="hljs-attr">author</span>=<span class="hljs-string">"solo"</span>></span>
    <span class="hljs-tag"><<span class="hljs-name">createTable</span> <span class="hljs-attr">tableName</span>=<span class="hljs-string">"project_like_user"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目点赞表"</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"主键id"</span>></span>
            <span class="hljs-tag"><<span class="hljs-name">constraints</span> <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">"true"</span> <span class="hljs-attr">nullable</span>=<span class="hljs-string">"false"</span>/></span>
        <span class="hljs-tag"></<span class="hljs-name">column</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目id"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"user_id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"点赞的用户id"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"status"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"int(11)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"点赞状态，0 取消点赞，1点赞"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"type"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"int(11)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"类型 1点赞"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"create_time"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"bigint(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"创建时间"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"update_time"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"bigint(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"更新时间"</span>/></span>
    <span class="hljs-tag"></<span class="hljs-name">createTable</span>></span>
<span class="hljs-tag"></<span class="hljs-name">changeSet</span>></span>

<span class="hljs-tag"><<span class="hljs-name">changeSet</span> <span class="hljs-attr">id</span>=<span class="hljs-string">"20190713-04"</span> <span class="hljs-attr">author</span>=<span class="hljs-string">"solo"</span>></span>
    <span class="hljs-tag"><<span class="hljs-name">createTable</span> <span class="hljs-attr">tableName</span>=<span class="hljs-string">"project_picture"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目图片表"</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"图片id"</span>></span>
            <span class="hljs-tag"><<span class="hljs-name">constraints</span> <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">"true"</span> <span class="hljs-attr">nullable</span>=<span class="hljs-string">"false"</span>/></span>
        <span class="hljs-tag"></<span class="hljs-name">column</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"project_id"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"项目id"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"picture_url"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"图片地址"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"picture_url_32"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"图片地址32位"</span>/></span>
        <span class="hljs-tag"><<span class="hljs-name">column</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"picture_url_64"</span> <span class="hljs-attr">type</span>=<span class="hljs-string">"varchar(64)"</span> <span class="hljs-attr">remarks</span>=<span class="hljs-string">"图片地址64位"</span>/></span>
    <span class="hljs-tag"></<span class="hljs-name">createTable</span>></span>
<span class="hljs-tag"></<span class="hljs-name">changeSet</span>></span>

</databaseChangeLog>

复制

如果你的项目一开始就用了 liquibase，那可以像上面这样写，把建表语句都写在 changelog 里。

如果一开始没用，后期想引入 liquibase，可以把以前的数据库导出成 sql，然后引入 sql 文件。方式如下：

<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">

<span class="hljs-tag"><<span class="hljs-name">include</span> <span class="hljs-attr">file</span>=<span class="hljs-string">"liquibase/changelogs/project.sql"</span> <span class="hljs-attr">relativeToChangelogFile</span>=<span class="hljs-string">"false"</span>/></span>

</databaseChangeLog>

复制

直接把项目导出的数据库文件 project.sql 通过 include 标签引进来。

如果 <include> 的方式 sql 文件报错，可以换种方式引入，用 <sqlFile> 标签

<?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">

<changeSet id=<span class="hljs-string">"1"</span> author=<span class="hljs-string">"solo"</span>>
    <sqlFile path=<span class="hljs-string">"classpath:/liquibase/changelogs/project.sql"</span> encoding=<span class="hljs-string">"UTF-8"</span> />
</changeSet>

</databaseChangeLog>