SpringBoot+Flyway

Flyway

数据库版本管理工具，支持数据库版本自动升级，支持多种数据库Oracle, SQL Server, SQL Azure, DB2, DB2 z/OS, MySQL(including Amazon RDS), MariaDB, Google Cloud SQL, PostgreSQL(including Amazon RDS and Heroku), Redshift, Vertica, H2, Hsql, Derby, SQLite, SAP HANA, solidDB, Sybase ASE and Phoenix。

SpringBoot+Flyway项目结构

project

pom.xml添加依赖

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <scope>test</scope>
    </dependency>
    
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.flywaydb/flyway-core -->
    <dependency>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-core</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

UserService.java

package com.inverseli.learning.service;

/**
 * @author liyuhao
 * @date 2018年9月28日下午4:23:01
 */
public interface UserService {
    
    void create(String name, Integer age);

    void deleteByName(String name);

    Integer getAllUsers() throws Exception;

    void deleteAllUsers();

}

UserServiceImpl.java

注意JdbcTemplate是org.springframework.jdbc.core包下的，别导错了。

package com.inverseli.learning.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * @author liyuhao
 * @date 2018年9月28日下午4:24:13
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void create(String name, Integer age) {
            jdbcTemplate.update("insert into user(NAME, AGE) values(?, ?)", name, age);
    }

    @Override
    public void deleteByName(String name) {
            jdbcTemplate.update("delete from user where NAME = ?", name);
    }


    @Override
    public void deleteAllUsers() {
            jdbcTemplate.update("delete from user");
    }

    @Override
    public Integer getAllUsers() {
        return jdbcTemplate.queryForObject("select count(1) from user", Integer.class);
    }
}

ApplicationTest.java

package com.inverseli.learning;

/**
 * @author liyuhao
 * @date 2018年9月28日下午5:05:02
 */

import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.inverseli.learning.service.UserService;


@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class ApplicationTest {

    @Autowired
    private UserService userSerivce;

    @Before
    public void setUp() {
        // 准备，清空user表
        userSerivce.deleteAllUsers();
    }

    @Test
    public void test() throws Exception {
        // 插入5个用户
        userSerivce.create("a", 1);
        userSerivce.create("b", 2);
        userSerivce.create("c", 3);
        userSerivce.create("d", 4);
        userSerivce.create("e", 5);

        // 查数据库，应该有5个用户
        Assert.assertEquals(5, userSerivce.getAllUsers().intValue());

        // 删除两个用户
        userSerivce.deleteByName("a");
        userSerivce.deleteByName("e");

        // 查数据库，应该有5个用户
        Assert.assertEquals(3, userSerivce.getAllUsers().intValue());

    }
}

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=etoak
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# 注意这个flyway路径配置
spring.flyway.locations=classpath:/db

db/V1__Base_version.sql

DROP TABLE IF EXISTS user ;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` int(5) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

踩坑

Flyway重要的一个表flyway_schema_history

mysql

sql

判断版本是否一致的字段 "checksum"

错误1

Detected failed migration to version 1 (Base version)

原因：修改了db/V1__Base_version.sql文件，修改了sql文件的内容，造成版本不一致。
解决：删除数据库中的元数据，或者直接删除flyway_schema_history表

mysql 8.0版本问题

错误1

java.math.BigInteger cannot be cast to java.lang.Long

原因：可能是mysql-connector-java版本太低
解决：mysql-connector-java升级到5.1.47

错误2

caching_sha2_password，数据库连接不上

原因：mysql8.登录验证方式改变，mysql_native_password变为 caching_sha2_password。
解决：输入ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY '111111';，然后调用下命令FLUSH PRIVILEGES;，将user表中信息立即同步到内存中。

SpringBoot+Flyway