Spring Boot Test中H2数据库表清空与ID重置方案咨询

问题背景 #

使用H2数据库进行应用集成测试，需要在每个测试方法执行前清空表数据并重置ID字段的自增序列。尝试使用TRUNCATE TABLE book, reader RESTART IDENTITY语句时执行失败，且希望避免每次测试重建Schema（资源消耗大），也不想使用@DirtiesContext注解。

当前测试配置注解：

package com.example.booklibrary.controllers.integration.annotation;

import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlGroup;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@SpringBootTest
@ActiveProfiles("test")
@Sql(scripts = "classpath:db/schema.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_CLASS)
@Sql(scripts = "classpath:db/reset-data.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
@AutoConfigureMockMvc
public @interface ControllerIT {}

Schema定义（schema.sql）：

DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS reader;

CREATE TABLE reader
(
    id   BIGSERIAL,
    name VARCHAR(255) NOT NULL,
    CONSTRAINT readers_PK PRIMARY KEY (id)
);

CREATE TABLE book
(
    id        BIGSERIAL,
    name      VARCHAR(255) NOT NULL,
    author    VARCHAR(255) NOT NULL,
    reader_id BIGINT,
    CONSTRAINT book_PK PRIMARY KEY (id),
    CONSTRAINT book_readers_FK FOREIGN KEY (reader_id) REFERENCES reader (id) ON UPDATE CASCADE ON DELETE RESTRICT
);

原重置脚本（reset-data.sql）：

TRUNCATE TABLE book,reader RESTART IDENTITY;

执行报错信息：

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [db/reset-data.sql]: TRUNCATE TABLE book,reader RESTART IDENTITY
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:282)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:54)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.execute(ResourceDatabasePopulator.java:269)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.lambda$executeSqlScripts$9(SqlScriptsTestExecutionListener.java:361)
    at org.springframework.transaction.support.TransactionOperations.lambda$executeWithoutResult$0(TransactionOperations.java:68)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
    at org.springframework.transaction.support.TransactionOperations.executeWithoutResult(TransactionOperations.java:67)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:361)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.lambda$executeSqlScripts$4(SqlScriptsTestExecutionListener.java:274)
    at java.base/java.lang.Iterable.forEach(Iterable.java:75)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:274)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:221)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.beforeTestMethod(SqlScriptsTestExecutionListener.java:164)
    at org.springframework.test.context.TestContextManager.beforeTestMethod(TestContextManager.java:320)
    at org.springframework.test.context.junit.jupiter.SpringExtension.beforeEach(SpringExtension.java:240)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)

错误原因 #

1. 外键约束顺序问题：book表通过reader_id关联reader表的主键，直接同时TRUNCATE两个表时，H2会先尝试处理reader表，导致因存在关联数据（外键约束ON DELETE RESTRICT）而报错。
2. TRUNCATE语法适配问题：H2中针对多表的RESTART IDENTITY合并写法无法正确识别每个表的序列重置指令。

解决方案 #

修改reset-data.sql脚本，调整TRUNCATE顺序并适配H2语法：

方案1：按依赖顺序分开TRUNCATE（推荐） #

先清空依赖父表的子表（book），再清空父表（reader），并分别添加RESTART IDENTITY重置自增序列：

TRUNCATE TABLE book RESTART IDENTITY;
TRUNCATE TABLE reader RESTART IDENTITY;

方案2：使用CASCADE处理外键约束 #

如果不想调整顺序，可以通过CASCADE让TRUNCATE自动处理关联数据，但会临时忽略ON DELETE RESTRICT的约束：

TRUNCATE TABLE reader, book RESTART IDENTITY CASCADE;

验证说明 #

修改后，每个测试方法执行前，脚本会先清空book和reader的数据，并重置各自的BIGSERIAL序列，确保每次测试的ID从初始值开始，同时避免了外键约束导致的执行错误，且无需重建Schema或使用@DirtiesContext。

内容的提问来源于stack exchange，提问作者yekashtalian