SpringBoot集成Flyway实现数据库版本管理

目录结构：

pom文件：

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>5.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.16</version>
        </dependency>

    </dependencies>

application.yml文件：

server:
  port: 8088

spring:
  profiles:
    active: dev

application-dev.yml文件：

spring:
  datasource:
    url: jdbc:mysql://localhost/flywaytest?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    flyway:
      baseline-on-migrate: true
      locations: classpath:db/migration # 迁移脚本的路径
      enabled: true # 是否开启flyway
      validationQuery: SELECT 1
    type: com.alibaba.druid.pool.DruidDataSource

启动类：

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.flyway.FlywayAutoConfiguration;

@SpringBootApplication(exclude = {FlywayAutoConfiguration.class})
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

不使用SpringBoot自动配置的Flyway；

数据源配置，自动创建数据库：

package com.example.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

@Configuration
@Primary //在同样的DataSource中，首先使用被标注的DataSource
public class DataSourceConfig {
    @Value("${spring.datasource.url}")
    private String datasourceUrl;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    @Bean     //声明其为Bean实例
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(datasourceUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);

        try {
            Class.forName(driverClassName);
            String url01 = datasourceUrl.substring(0, datasourceUrl.indexOf("?"));
            String url02 = url01.substring(0, url01.lastIndexOf("/"))+"?serverTimezone=GMT%2B8";
            String datasourceName = url01.substring(url01.lastIndexOf("/") + 1);
            // 连接已经存在的数据库，如：mysql
            Connection connection = DriverManager.getConnection(url02, username, password);
            Statement statement = connection.createStatement();
            // 创建数据库
            statement.executeUpdate("create database if not exists `" + datasourceName + "` default character set utf8 COLLATE utf8_general_ci");
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return datasource;
    }
}

Flyway配置：

package com.example.demo.config;

import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;

@Configuration
public class FlywayConfig {
    @Autowired
    private DataSource dataSource;
    @Value("${spring.datasource.flyway.locations}")
    private String[] locations;

    @PostConstruct
    public void migrate() {
        Flyway flyway = Flyway.configure()
                .dataSource(dataSource)
                .locations(locations)
                .baselineOnMigrate(true)
                .load();
        flyway.repair();
        flyway.migrate();
    }
}

将sql脚本放在db.migration目录下：

脚本命名规则：V版本号__描述.sql，注意版本号与描述之间是两个下划线；

比如：V1.0__初始化脚本.sql

CREATE TABLE `stu`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

V1.1__添加数据.sql:

insert into stu(id,age) values (1,1);
insert into stu(id,age) values (2,2);

V1.2__添加一列.sql:

#增加一列
alter table stu add column name varchar(255);

V1.3__添加一列.sql：

alter table stu add column address varchar(255);

工程启动后Flyway会检查数据中的flyway_schema_history表中的版本号与db.migration目录下的脚本的版本号作比较，若db.migration目录下的脚本版本号更大，则执行这个脚本

执行结果：

stu表：

flyway_schema_history表记录执行的脚本：

当数据库的表结构有变动时只需要在db.migration目录下添加sql脚本就可以在工程启动时更新表结构；