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表中信息立即同步到内存中。
