Migrate MySQL database by Flyway in SpringBoot

Thao tác với MySQL bằng Flyway trong SpringBoot

Mục tiêu bài viết:

Sử dụng Flyway quản lý các bảng và dữ liệu trong cơ sở dữ liệu MySQL trong SpringBoot

Tạo database:

create database demomigratebyflywaydb

Tạo project:

Cài đặt Command line tool:

Để cài đặt Command line tool chọn File -> Settings -> Plugins

Cấu trúc source code:

Thêm plugin flyway vào file pom.xml:

            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>4.2.0</version>
                <configuration>
                    <configFile>src/main/resources/config/flyway.properties</configFile>
                </configuration>
            </plugin>

Trong class application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/demomigratebyflywaydb
spring.datasource.username=root
spring.datasource.password=root

Trong file flyway.properties:

flyway.url=jdbc:mysql://localhost:3306/demomigratebyflywaydb
flyway.user=root
flyway.password=root

Tạo file V1__Create_employee_table.sql:

CREATE TABLE department(
    dep_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employee (
    id INT PRIMARY KEY  AUTO_INCREMENT,
    name VARCHAR (100) NOT NULL,
    address VARCHAR (256) NOT NULL,
    dep_id INT,
    FOREIGN KEY (dep_id) REFERENCES department(dep_id)
);

INSERT INTO department (dep_id, name) VALUES
    (1, 'Manager'),
    (2, 'IT');

INSERT INTO employee (id, name, address,dep_id) VALUES
  (1, 'Nguyen Van A', 'Ha Noi',1),
  (2, 'Tran Van B', 'Bac Giang',2),
  (3, 'Nguyen Thi C', 'Hai Duong',1);

Sau khi xong  build project:

Chọn Tools -> Run Command... hoặc nhấn Ctrl+Shift+X nhập lệnh migrate:

mvn flyway:migrate

Kết quả:

Sau đó generate entity class kết quả:

package com.fpt.demomigratebyflyway.entity;

import javax.persistence.*;
import java.util.Collection;
import java.util.Objects;

@Entity
public class Department {
    private int depId;
    private String name;
    private Collection<Employee> employeesByDepId;

    @Id
    @Column(name = "dep_id")
    public int getDepId() {
        return depId;
    }

    public void setDepId(int depId) {
        this.depId = depId;
    }

    @Basic
    @Column(name = "name")
    public String getName() {
        return name;
    }

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

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Department that = (Department) o;
        return depId == that.depId &&
                Objects.equals(name, that.name);
    }

    @Override
    public int hashCode() {
        return Objects.hash(depId, name);
    }

    @OneToMany(mappedBy = "departmentByDepId")
    public Collection<Employee> getEmployeesByDepId() {
        return employeesByDepId;
    }

    public void setEmployeesByDepId(Collection<Employee> employeesByDepId) {
        this.employeesByDepId = employeesByDepId;
    }
}

package com.fpt.demomigratebyflyway.entity;

import javax.persistence.*;
import java.util.Objects;

@Entity
public class Employee {
    private int id;
    private String name;
    private String address;
    private Department departmentByDepId;

    @Id
    @Column(name = "id")
    public int getId() {
        return id;
    }

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

    @Basic
    @Column(name = "name")
    public String getName() {
        return name;
    }

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

    @Basic
    @Column(name = "address")
    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Employee employee = (Employee) o;
        return id == employee.id &&
                Objects.equals(name, employee.name) &&
                Objects.equals(address, employee.address);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, address);
    }

    @ManyToOne
    @JoinColumn(name = "dep_id", referencedColumnName = "dep_id")
    public Department getDepartmentByDepId() {
        return departmentByDepId;
    }

    public void setDepartmentByDepId(Department departmentByDepId) {
        this.departmentByDepId = departmentByDepId;
    }
}

Link tham khảo generate entity class: Spring Boot generate entity class from MySQL Database using IntelliJ IDEA ULTIMATE

Lệnh xoá tất cả table trong database bằng command line:

mvn flyway:clean