IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

Spring Boot实现数据库迁移案例

wen java案例 2

本文目录导读：

Spring Boot实现数据库迁移案例

  1. 项目配置
  2. 创建迁移脚本
  3. Java实体类
  4. Service实现
  5. 控制器
  6. 测试
  7. 迁移回滚处理
  8. 运行配置
  9. 注意事项

我来为您提供一个完整的Spring Boot数据库迁移案例，使用Flyway作为迁移工具。

项目配置

添加依赖 (pom.xml)

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

application.yml 配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: validate  # 使用validate验证实体与数据库的一致性
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
  flyway:
    enabled: true
    baseline-on-migrate: true
    locations: classpath:db/migration
    encoding: UTF-8
    # 自定义变量
    placeholders:
      TABLE_PREFIX: t_

创建迁移脚本

V1__Create_user_table.sql

-- 创建用户表
CREATE TABLE `${TABLE_PREFIX}user` (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(200) NOT NULL,
    full_name VARCHAR(100),
    email_verified BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uk_username UNIQUE (username),
    CONSTRAINT uk_email UNIQUE (email)
) ENGINE=InnoDB;
-- Create indexes
CREATE INDEX idx_user_status ON `${TABLE_PREFIX}user`(status);
CREATE INDEX idx_user_created_at ON `${TABLE_PREFIX}user`(created_at);
-- Insert initial admin user (password is hashed)
INSERT INTO `${TABLE_PREFIX}user` (username, email, password, full_name, status) VALUES 
('admin', 'admin@example.com', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Admin', 'ACTIVE');

V2__Create_role_and_permission_tables.sql

-- 创建角色表
CREATE TABLE `${TABLE_PREFIX}role` (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- 创建权限表
CREATE TABLE `${TABLE_PREFIX}permission` (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- 创建用户角色关联表
CREATE TABLE `${TABLE_PREFIX}user_role` (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES `${TABLE_PREFIX}user`(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES `${TABLE_PREFIX}role`(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- 创建角色权限关联表
CREATE TABLE `${TABLE_PREFIX}role_permission` (
    role_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES `${TABLE_PREFIX}role`(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES `${TABLE_PREFIX}permission`(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- 插入初始化数据
INSERT INTO `${TABLE_PREFIX}role` (name, description) VALUES 
('ADMIN', 'Administrator role with full permissions'),
('USER', 'Regular user role');
INSERT INTO `${TABLE_PREFIX}permission` (name, description) VALUES 
('READ', 'Read access'),
('WRITE', 'Write access'),
('DELETE', 'Delete access'),
('ADMIN', 'Admin access');
-- Assign all permissions to admin role
INSERT INTO `${TABLE_PREFIX}role_permission` (role_id, permission_id)
SELECT r.id, p.id FROM `${TABLE_PREFIX}role` r CROSS JOIN `${TABLE_PREFIX}permission` p WHERE r.name = 'ADMIN';
-- Assign READ and WRITE to user role
INSERT INTO `${TABLE_PREFIX}role_permission` (role_id, permission_id)
SELECT r.id, p.id FROM `${TABLE_PREFIX}role` r, `${TABLE_PREFIX}permission` p WHERE r.name = 'USER' AND p.name IN ('READ', 'WRITE');
-- Link admin user to admin role
INSERT INTO `${TABLE_PREFIX}user_role` (user_id, role_id)
SELECT u.id, r.id FROM `${TABLE_PREFIX}user` u, `${TABLE_PREFIX}role` r WHERE u.username = 'admin' AND r.name = 'ADMIN';

V3__Add_address_table.sql

-- 添加地址表
CREATE TABLE `${TABLE_PREFIX}address` (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    address_line1 VARCHAR(200) NOT NULL,
    address_line2 VARCHAR(200),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES `${TABLE_PREFIX}user`(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX idx_address_user_id ON `${TABLE_PREFIX}address`(user_id);

V4__Add_updated_fields.sql

-- 修改用户表：增加字段
ALTER TABLE `${TABLE_PREFIX}user` 
    ADD COLUMN phone VARCHAR(20) AFTER email,
    ADD COLUMN last_login TIMESTAMP NULL AFTER status,
    ADD COLUMN deleted_at TIMESTAMP NULL;
-- 修改角色表：增加字段
ALTER TABLE `${TABLE_PREFIX}role`
    ADD COLUMN is_system TINYINT(1) DEFAULT 0 AFTER description;
-- 更新现有数据
UPDATE `${TABLE_PREFIX}role` SET is_system = 1 WHERE name IN ('ADMIN', 'USER');
-- 创建用户表索引
CREATE INDEX idx_user_email ON `${TABLE_PREFIX}user`(email);
CREATE INDEX idx_user_phone ON `${TABLE_PREFIX}user`(phone);

Java实体类

User.java

package com.example.migration.entity;
import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import java.time.LocalDateTime;
import java.util.HashSet;
import java.util.Set;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "t_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false, unique = true)
    private String username;
    @Column(nullable = false, unique = true)
    private String email;
    private String phone;
    @Column(nullable = false)
    private String password;
    @Column(name = "full_name")
    private String fullName;
    @Column(name = "email_verified")
    private Boolean emailVerified = false;
    @Enumerated(EnumType.STRING)
    private UserStatus status = UserStatus.ACTIVE;
    @Column(name = "last_login")
    private LocalDateTime lastLogin;
    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;
    @Column(name = "deleted_at")
    private LocalDateTime deletedAt;
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "t_user_role",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private Set<Role> roles = new HashSet<>();
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<Address> addresses = new HashSet<>();
    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
        updatedAt = LocalDateTime.now();
    }
    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now();
    }
}

Role.java

package com.example.migration.entity;
import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import java.time.LocalDateTime;
import java.util.HashSet;
import java.util.Set;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "t_role")
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false, unique = true)
    private String name;
    private String description;
    @Column(name = "is_system")
    private Boolean system = false;
    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "t_role_permission",
        joinColumns = @JoinColumn(name = "role_id"),
        inverseJoinColumns = @JoinColumn(name = "permission_id")
    )
    private Set<Permission> permissions = new HashSet<>();
    @ManyToMany(mappedBy = "roles")
    private Set<User> users = new HashSet<>();
    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
    }
}

UserStatus.java

package com.example.migration.entity;
public enum UserStatus {
    ACTIVE,
    INACTIVE,
    PENDING,
    SUSPENDED
}

Address.java

package com.example.migration.entity;
import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import java.time.LocalDateTime;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "t_address")
public class Address {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;
    @Column(name = "address_line1", nullable = false)
    private String addressLine1;
    @Column(name = "address_line2")
    private String addressLine2;
    @Column(nullable = false)
    private String city;
    private String state;
    @Column(name = "postal_code")
    private String postalCode;
    @Column(nullable = false)
    private String country;
    @Column(name = "is_primary")
    private Boolean primary = false;
    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;
    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
        updatedAt = LocalDateTime.now();
    }
    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now();
    }
}

Service实现

UserService.java

package com.example.migration.service;
import com.example.migration.entity.User;
import com.example.migration.repository.UserRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
import java.util.Optional;
@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;
    @Transactional(readOnly = true)
    public Optional<User> findByUsername(String username) {
        return userRepository.findByUsername(username);
    }
    @Transactional(readOnly = true)
    public Optional<User> findByEmail(String email) {
        return userRepository.findByEmail(email);
    }
    @Transactional(readOnly = true)
    public List<User> findAll() {
        return userRepository.findAll();
    }
    @Transactional
    public User createUser(User user) {
        if (userRepository.existsByUsername(user.getUsername())) {
            throw new RuntimeException("Username already exists");
        }
        if (userRepository.existsByEmail(user.getEmail())) {
            throw new RuntimeException("Email already exists");
        }
        return userRepository.save(user);
    }
    @Transactional
    public User updateUser(User user) {
        return userRepository.save(user);
    }
    @Transactional
    public void deleteUser(Long id) {
        userRepository.deleteById(id);
    }
    @Transactional
    public void softDeleteUser(Long id) {
        userRepository.findById(id).ifPresent(user -> {
            userRepository.softDelete(id);
        });
    }
}

UserRepository.java

package com.example.migration.repository;
import com.example.migration.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByUsername(String username);
    Optional<User> findByEmail(String email);
    boolean existsByUsername(String username);
    boolean existsByEmail(String email);
    @Modifying
    @Query("UPDATE User u SET u.deletedAt = CURRENT_TIMESTAMP WHERE u.id = :id")
    void softDelete(@Param("id") Long id);
    @Query("SELECT u FROM User u WHERE u.deletedAt IS NULL")
    List<User> findAllActiveUsers();
}

控制器

UserController.java

package com.example.migration.controller;
import com.example.migration.entity.User;
import com.example.migration.service.UserService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Optional;
@RestController
@RequestMapping("/api/users")
@RequiredArgsConstructor
public class UserController {
    private final UserService userService;
    @GetMapping
    public ResponseEntity<List<User>> getAllUsers() {
        return ResponseEntity.ok(userService.findAll());
    }
    @GetMapping("/{id}")
    public ResponseEntity<User> getUserById(@PathVariable Long id) {
        return userService.findByUsername(String.valueOf(id))
            .map(ResponseEntity::ok)
            .orElse(ResponseEntity.notFound().build());
    }
    @PostMapping
    public ResponseEntity<User> createUser(@RequestBody User user) {
        try {
            User createdUser = userService.createUser(user);
            return ResponseEntity.status(HttpStatus.CREATED).body(createdUser);
        } catch (RuntimeException e) {
            return ResponseEntity.badRequest().build();
        }
    }
    @PutMapping("/{id}")
    public ResponseEntity<User> updateUser(@PathVariable Long id, @RequestBody User user) {
        user.setId(id);
        return ResponseEntity.ok(userService.updateUser(user));
    }
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteUser(@PathVariable Long id) {
        userService.deleteUser(id);
        return ResponseEntity.noContent().build();
    }
    @DeleteMapping("/{id}/soft")
    public ResponseEntity<Void> softDeleteUser(@PathVariable Long id) {
        userService.softDeleteUser(id);
        return ResponseEntity.noContent().build();
    }
}

测试

单元测试

package com.example.migration;
import com.example.migration.entity.User;
import com.example.migration.repository.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import static org.assertj.core.api.Assertions.assertThat;
@SpringBootTest
@ActiveProfiles("test")
class MigrationApplicationTests {
    @Autowired
    private UserRepository userRepository;
    @Test
    void contextLoads() {
    }
    @Test
    void testFindUserByUsername() {
        User user = userRepository.findByUsername("admin").orElse(null);
        assertThat(user).isNotNull();
        assertThat(user.getUsername()).isEqualTo("admin");
    }
    @Test
    void testCreateUser() {
        User user = new User();
        user.setUsername("testuser");
        user.setEmail("test@example.com");
        user.setPassword("password123");
        user.setFullName("Test User");
        User savedUser = userRepository.save(user);
        assertThat(savedUser.getId()).isNotNull();
        assertThat(savedUser.getUsername()).isEqualTo("testuser");
    }
}

迁移回滚处理

可选的回滚脚本

-- V1__Create_user_table.sql 的回滚脚本
-- 放在 db/migration/undo 目录下
DROP TABLE IF EXISTS t_user;

运行配置

<!-- 添加插件支持 -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>9.22.1</version>
    <configuration>
        <url>jdbc:mysql://localhost:3306/mydb</url>
        <user>root</user>
        <password>password</password>
        <locations>
            <location>classpath:db/migration</location>
        </locations>
    </configuration>
</plugin>

注意事项

  1. 版本命名规范：V1描述.sql, V2描述.sql (数字递增，双下划线分隔)
  2. 不可修改已发布的迁移文件：一旦执行过的迁移文件禁止修改
  3. 回滚策略：Flyway本身不支持回滚，可通过编写反向SQL脚本或使用validate来检测
  4. 多环境配置：针对不同环境可以配置不同的迁移目录
  5. 生产环境安全：在生产环境上执行迁移前，务必备份数据库

这个案例展示了如何使用Spring Boot + Flyway实现完整的数据库迁移流程，包括创建表、修改表结构、添加数据等常见操作。

上一篇Spring Boot整合Flyway案例

下一篇Spring Boot整合Liquibase案例

相关文章

抱歉，评论功能暂时关闭!