SpringBoot使用flyway初始化数据库

spring:

  profiles:

  include: database

spring:

  datasource:

  url: jdbc:mysql: //yourIp :3306 /spring_boot_series ?useUnicode= true &characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries= true &serverTimezone=GMT

  username: yourUsername

  password: yourPassword

  driver-class-name: com.mysql.cj.jdbc.Driver

  # 自动读取spring.datasource配置进行迁移操作

  flyway:

  # 版本迁移位置

  locations: classpath:db

  baseline-version: 1.0.2

  init-sqls:

  - SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;

  - SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

  - SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE =

  'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' ;

  - CREATE SCHEMA IF NOT EXISTS `spring_boot_series` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

package io.wilson.flyway;

import com.zaxxer.hikari.HikariDataSource;

import lombok.AllArgsConstructor;

import lombok.extern.slf4j.Slf4j;

import org.springframework.boot.autoconfigure.flyway.FlywayProperties;

import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;

import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

/**

  * @author Wilson

  */

@Slf4j

@Component

@AllArgsConstructor

public class DatabaseInitializer {

  private final FlywayProperties flywayProperties;

  private final DataSourceProperties dataSourceProperties;

  @PostConstruct

  public void init() throws SQLException {

  log.info( "DatabaseInitializer uses flyway init-sqls to initiate database" );

  String url = dataSourceProperties.getUrl();

  // jdbc url最后一个 '/' 用于分割具体 schema?参数

  int lastSplitIndex = url.lastIndexOf( '/' );

  // 获取spring.datasource.url具体数据库schema前的jdbc url

  String addressUrl = url.substring( 0 , lastSplitIndex);

  // 直连数据库地址:jdbc:mysql://yourIp:port

  HikariDataSource dataSource = new HikariDataSource();

  dataSource.setJdbcUrl(addressUrl);

  dataSource.setUsername(dataSourceProperties.getUsername());

  dataSource.setPassword(dataSourceProperties.getPassword());

  Connection connection = dataSource.getConnection();

  Statement statement = connection.createStatement();

  for (String sql : flywayProperties.getInitSqls()) {

  // 通过flyway的init-sqls配置进行建库与数据库配置

  // executeUpdate:执行给定的SQL语句，该语句可以是INSERT，UPDATE或DELETE语句或不返回任何内容的SQL语句，例如SQL DDL语句。

  statement.executeUpdate(sql);

  }

  statement.close();

  connection.close();

  dataSource.close();

  log.info( "DatabaseInitializer initialize completed" );

  }

}

package io.wilson.flyway;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

/**

  * @author Wilson

  */

@SpringBootApplication

@MapperScan ( "io.wilson.flyway.mapper" )

public class FlywayApplication {

  public static void main(String[] args) {

  SpringApplication.run(FlywayApplication. class , args);

  }

}