Spring Boot进阶 之 DB Migration

本文的示例代码参考FlywayDemo

目录

• 开始

• 迁移

• 部署

开始

spring init -dweb,mysql,data-jpa,flyway --build gradle FlywayDemo && cd FlywayDemo

本文数据库迁移基于Flyway 类型方案还有Liquibase

• 创建模型

vim src/main/java/com/example/FlywayDemo/User.java

package com.example.FlywayDemo;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotBlank
    @Column(unique = true)
    @Size(min = 1, max = 100)
    private String username;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }
}

• 持久化接口

vim src/main/java/com/example/FlywayDemo/UserRepository.java

package com.example.FlywayDemo;

import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Integer> {
}

• 控制器逻辑

vim src/main/java/com/example/FlywayDemo/UsersController.java

package com.example.FlywayDemo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UsersController {
    @Autowired
    private UserRepository userRepository;

    @GetMapping("/users")
    public Iterable<User> users() {
        return userRepository.findAll();
    }
}

• 自动化配置

vim src/main/resources/application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/flyway?userSSL=false
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

迁移

mkdir -p src/main/resources/db/migration

vim src/main/resources/db/migration/V1_0_0__init.sql

CREATE TABLE users (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  username varchar(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY UK_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (username) VALUES ('Test01');

关于MySQL字符集 更多参考MySQL字符集

docker run --name flyway-demo -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.17

docker exec -i flyway-demo mysql -uroot -p123456  <<< "CREATE DATABASE IF NOT EXISTS flyway DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;"

关于Docker的使用 更多参考Docker入门

• 测试

./gradlew bootrun

curl localhost:8080/users | json

[
  {
    "id": 1,
    "username": "Test01"
  }
]

这里使用nodejs的json工具格式化数据: npm i -g json

vim src/main/resources/db/migration/V1_0_1__alter_user_username_to_name.sql

ALTER TABLE users CHANGE COLUMN username name VARCHAR(100) NOT NULL AFTER id;

INSERT INTO users (name) VALUES ('Test02');

vim src/main/java/com/example/FlywayDemo/User.java

package com.example.FlywayDemo;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotBlank
    @Column(unique = true)
    @Size(min = 1, max = 100)
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

• 测试

./gradlew bootrun

curl localhost:8080/users | json

[
  {
    "id": 1,
    "name": "Test01"
  },
  {
    "id": 2,
    "name": "Test02"
  }
]

部署

vim src/main/resources/db/migration/V1_0_2__alter_user_name_to_username.sql

ALTER TABLE users CHANGE COLUMN name username VARCHAR(100) NOT NULL AFTER id;

INSERT INTO users (username) VALUES ('Test03');

vim src/main/java/com/example/FlywayDemo/User.java

package com.example.FlywayDemo;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotBlank
    @Column(unique = true)
    @Size(min = 1, max = 100)
    private String username;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }
}

./gradlew build

java -jar build/libs/FlywayDemo-0.0.1-SNAPSHOT.jar

在服务启动时会自动执行迁移脚本

• 测试

curl localhost:8080/users | json

[
  {
    "id": 1,
    "username": "Test01"
  },
  {
    "id": 2,
    "username": "Test02"
  },
  {
    "id": 3,
    "username": "Test03"
  }
]

参考

• Flyway Doc

• Spring Boot - Flyway

• Spring Boot Database Migrations with Flyway