You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Spring Boot测试环境配置H2数据库遇外键约束异常求助

avatar
云原生阿岚
2026-7-25
Spring Boot集成测试外键约束违反问题排查

问题描述

开发Spring Boot演示项目，提供REST API实现实体CRUD操作，已编写单元测试与集成测试保障流程正确性。为隔离开发环境，测试环境配置了独立的H2内存数据库（test profile），但运行集成测试时抛出异常：执行data-test.sql第8条插入语句时，多对多关联表employees_experiences触发外键约束违反。

相关配置文件

src/main/resources/application.properties 

spring.datasource.url=jdbc:postgresql://localhost:5432/employee-management-system
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

spring.sql.init.mode=always
spring.sql.init.schema-locations=classpath:data-dev.sql

src/test/resources/application-test.properties 

spring.datasource.url=jdbc:h2:mem:employee-management-system-test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.h2.console.enabled=true

spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

logging.level.sql=DEBUG
spring.sql.init.mode=always
spring.sql.init.schema-locations=classpath:schema-test.sql

测试类代码

EmployeeRestControllerIntegrationTest.java 

@ActiveProfiles("test")
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@Sql(scripts = {"classpath:data-test.sql"})
class EmployeeRestControllerIntegrationTest {

    @Autowired
    private TestRestTemplate template;

    @Autowired
    private EmployeeRepository employeeRepository;

    @Autowired
    private ObjectMapper objectMapper;

    @Spy
    private ModelMapper modelMapper;

    private EmployeeDto employeeDto1;
    private EmployeeDto employeeDto2;
    private List<EmployeeDto> employeeDtos;

    @BeforeEach
    void setUp() {
        employeeDto1 = modelMapper.map(getMockedEmployee1(), EmployeeDto.class);
        employeeDto2 = modelMapper.map(getMockedEmployee2(), EmployeeDto.class);
        employeeDtos = modelMapper.map(getMockedEmployees(), new TypeToken<List<EmployeeDto>>() {}.getType());
    }

    @AfterEach
    void tearDown() {
        employeeRepository.deleteAll();
    }

    @Test
    void getAllEmployees_shouldReturnListOfEmployees() throws Exception {
        ResponseEntity<String> response = template.getForEntity("/api/employees", String.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.OK);
        assertThat(response.getHeaders().getContentType()).isEqualTo(APPLICATION_JSON);
        List<EmployeeDto> result = objectMapper.readValue(response.getBody(), new TypeReference<>() {});
        Collections.reverse(employeeDtos);
        assertThat(result).isEqualTo(employeeDtos);
    }

    @Test
    void getEmployeeById_withValidId_shouldReturnEmployeeWithGivenId() {
        ResponseEntity<EmployeeDto> response = template.getForEntity("/api/employees/1", EmployeeDto.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.OK);
        assertThat(response.getHeaders().getContentType()).isEqualTo(APPLICATION_JSON);
        assertThat(response.getBody()).isEqualTo(employeeDto1);
    }

    @Test
    void getEmployeeById_withInvalidId_shouldThrowException() {
        Long id = 999L;
        ResponseEntity<String> response = template.getForEntity("/api/employees/999", String.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.NOT_FOUND);
        assertThat(response.getHeaders().getContentType()).isEqualTo(APPLICATION_JSON);
        assertThat(response.getBody()).isEqualTo("Resource not found: " + String.format(EMPLOYEE_NOT_FOUND, id));
    }

    @Test
    void saveEmployee_shouldAddEmployeeToList() {
        ResponseEntity<EmployeeDto> response = template.postForEntity("/api/employees", employeeDto1, EmployeeDto.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.CREATED);
        assertThat(response.getHeaders().getContentType()).isEqualTo(APPLICATION_JSON);
        assertThat(response.getBody()).isEqualTo(employeeDto1);
    }

    @Test
    void updateEmployeeById_withValidId_shouldUpdateEmployeeWithGivenId() {
        Long id = 1L;
        EmployeeDto employeeDto = employeeDto2;
        employeeDto.setId(id);
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(APPLICATION_JSON);
        ResponseEntity<EmployeeDto> response = template.exchange("/api/employees/1", HttpMethod.PUT, new HttpEntity<>(employeeDto2, headers), EmployeeDto.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.OK);
        assertThat(response.getHeaders().getContentType()).isEqualTo(APPLICATION_JSON);
        assertThat(response.getBody()).isEqualTo(employeeDto);
    }

    @Test
    void updateEmployeeById_withInvalidId_shouldThrowException() {
        Long id = 999L;
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(APPLICATION_JSON);
        ResponseEntity<String> response = template.exchange("/api/employees/999", HttpMethod.PUT, new HttpEntity<>(employeeDto2, headers), String.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.NOT_FOUND);
        assertThat(response.getHeaders().getContentType()).isEqualTo(APPLICATION_JSON);
        assertThat(response.getBody()).isEqualTo("Resource not found: " + String.format(EMPLOYEE_NOT_FOUND, id));
    }

    @Test
    void deleteEmployeeById_withValidId_shouldRemoveEmployeeWithGivenIdFromList() {
        ResponseEntity<Void> response = template.exchange("/api/employees/1", HttpMethod.DELETE, new HttpEntity<>(null), Void.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.NO_CONTENT);
        ResponseEntity<Void> getResponse = template.exchange("/api/employees/1", HttpMethod.GET, null, Void.class);
        assertNotNull(getResponse);
        assertThat(getResponse.getStatusCode()).isEqualTo(HttpStatus.NOT_FOUND);
        ResponseEntity<Void> getAllResponse = template.exchange("/api/employee", HttpMethod.GET, null, Void.class);
        assertNotNull(getAllResponse);
    }

    @Test
    void deleteEmployeeById_withInvalidId_shouldThrowException() {
        Long id = 999L;
        ResponseEntity<String> response = template.exchange("/api/employees/999", HttpMethod.DELETE, new HttpEntity<>(null), String.class);
        assertNotNull(response);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.NOT_FOUND);
        assertThat(response.getHeaders().getContentType()).isEqualTo(APPLICATION_JSON);
        assertThat(response.getBody()).isEqualTo("Resource not found: " + String.format(EMPLOYEE_NOT_FOUND, id));
    }
}

测试异常信息 

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #8 of class path resource [data-test.sql]: INSERT INTO employees_experiences(employee_id, experience_id) VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 1), (3, 2), (4, 3), (4, 4), (5, 1), (5, 2), (6, 3), (6, 4), (7, 1), (7, 2), (8, 3), (8, 4), (9, 1), (9, 2), (10, 3), (10, 4), (11, 1), (11, 2), (12, 3), (12, 4), (13, 1), (13, 2), (14, 3), (14, 4), (15, 1), (15, 2), (16, 3), (16, 4), (17, 1), (17, 2), (18, 3), (18, 4), (19, 1), (19, 2), (20, 3), (20, 4)com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation: "FK9KRGPGEGMHONO5TCMUS7LBLJY: PUBLIC.EMPLOYEES_EXPERIENCES FOREIGN KEY(EMPLOYEE_ID) REFERENCES PUBLIC.EMPLOYEES(ID) (1)"; SQL statement:
INSERT INTO employees_experiences(employee_id, experience_id) VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 1), (3, 2), (4, 3), (4, 4), (5, 1), (5, 2), (6, 3), (6, 4), (7, 1), (7, 2), (8, 3), (8, 4), (9, 1), (9, 2), (10, 3), (10, 4), (11, 1), (11, 2), (12, 3), (12, 4), (13, 1), (13, 2), (14, 3), (14, 4), (15, 1), (15, 2), (16, 3), (16, 4), (17, 1), (17, 2), (18, 3), (18, 4), (19, 1), (19, 2), (20, 3), (20, 4)

排查方向与解决方案

  • 检查数据插入顺序
    外键约束要求插入关联表数据前，必须先插入employees和experiences表的对应记录。确认data-test.sql中，先执行主表的INSERT语句，再执行关联表的INSERT语句。

  • 验证测试数据的ID匹配
    核对employees表是否存在ID为1-20的记录，experiences表是否存在ID为1-4的记录。如果某个ID缺失，关联插入会直接触发外键约束错误。

  • 检查H2表结构与实体映射
    测试环境配置spring.jpa.hibernate.ddl-auto=validate，会验证实体类和数据库表结构一致性：

    • 确认Employee、Experience实体的多对多映射配置正确，关联表的外键字段类型和主表ID字段一致（比如都是Long）；
    • 可临时将ddl-auto改为create，让Hibernate自动生成表结构，排除手动编写的schema-test.sql可能存在的结构错误。

  • 确认SQL脚本执行时机
    @Sql注解默认在测试方法前执行脚本，可添加executionPhase=Sql.ExecutionPhase.BEFORE_TEST_METHOD明确执行时机；同时确保脚本内无事务控制语句干扰执行顺序。

  • 优化测试数据清理逻辑
    当前@AfterEach中employeeRepository.deleteAll()不会级联删除关联表数据，可能导致数据残留。建议给测试类添加@Transactional注解，让每个测试方法在事务中运行，测试结束后自动回滚，避免脏数据影响。

  • 适配H2与PostgreSQL的差异
    H2和PostgreSQL的ID生成策略（如serial/identity）可能存在差异：

    • 确认实体类ID生成器在H2环境下能正确生成指定ID；
    • 或在data-test.sql中明确插入指定ID的记录，避免自动生成的ID与脚本中的ID不匹配。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.25 22:27:01