Spring Data JPA测试抛出InvalidDataAccessResourceUsageException求助

我在Spring中为Repository的自定义查询编写单元测试，使用H2内存数据库创建Employee并对自定义查询返回的结果做断言。调用Repository的save方法时抛出异常：
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
尝试过StackOverflow相关方案但未解决，期望测试能创建Employee并通过bossId查询后断言两个列表相等。

单元测试代码 #

package com.stg.mlindow.javabootrestcert.stgbootrestcert.employee;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.TestPropertySource;

import java.util.ArrayList;
import java.util.List;

import static org.junit.jupiter.api.Assertions.*;


@AutoConfigureTestDatabase
@ActiveProfiles("test")
@TestPropertySource(locations = "classpath:application-test.properties")
@DataJpaTest
class EmployeeRepositoryTest {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Test
    void findAllByBossId() {
        Employee employee = new Employee(
                "CEO",
                "Bruce",
                "Wayne",
                null
        );
        employeeRepository.save(employee);

        List<Employee> employees = employeeRepository.findAllByBossId(null);
        List<Employee> expected = new ArrayList<Employee>() {
            {
                add(employee);
            }
        };

        assertIterableEquals(expected, employees);
    }
}

Pom.xml配置 #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.7.7</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.stg.mlindow.javabootrestcert</groupId>
   <artifactId>stg-boot-rest-cert</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>stg-boot-rest-cert</name>
   <description>Demo project for Spring Boot</description>
   <properties>
      <java.version>1.8</java.version>
   </properties>
   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-actuator</artifactId>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-data-jpa</artifactId>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-security</artifactId>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>postgresql</artifactId>
         <scope>runtime</scope>
      </dependency>
      <dependency>
         <groupId>com.h2database</groupId>
         <artifactId>h2</artifactId>
         <scope>test</scope>
      </dependency>
      <dependency>
         <groupId>javax.xml.bind</groupId>
         <artifactId>jaxb-api</artifactId>
         <version>2.3.1</version>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
      </dependency>
      <dependency>
         <groupId>org.springframework.security</groupId>
         <artifactId>spring-security-test</artifactId>
         <scope>test</scope>
      </dependency>
      <dependency>
         <groupId>io.jsonwebtoken</groupId>
         <artifactId>jjwt</artifactId>
         <version>0.9.1</version>
      </dependency>
      <dependency>
         <groupId>org.jetbrains</groupId>
         <artifactId>annotations</artifactId>
         <version>23.1.0</version>
         <scope>compile</scope>
      </dependency>
   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
         </plugin>
      </plugins>
   </build>

</project>

application-test.properties配置 #

##### Database #####
spring.datasource.url=jdbc:h2://mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL81Dialect

Repository代码 #

package com.stg.mlindow.javabootrestcert.stgbootrestcert.employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import java.math.BigInteger;
import java.util.List;
import java.util.Optional;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, BigInteger>,
        PagingAndSortingRepository<Employee, BigInteger> {
    List<Employee> findAllByBossId(BigInteger bossId);
}

1. 核心问题：数据库方言不匹配 #

测试用H2内存数据库，但配置了PostgreSQL的方言org.hibernate.dialect.PostgreSQL81Dialect，导致Hibernate生成PostgreSQL专属SQL语法，H2无法识别，触发SQL语法错误。

2. 修复步骤 #

修改application-test.properties中的方言配置，替换为H2对应的方言：

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect

3. 其他潜在问题检查 #

• 实体类主键类型匹配：确保Employee实体类的主键字段类型也是BigInteger，并正确标注@Id和生成策略，示例：

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private BigInteger id;
    private String position;
    private String firstName;
    private String lastName;
    private BigInteger bossId;

    // 对应构造方法、getter/setter
}

• 空值字段权限：实体类中bossId字段需允许为null，避免标注@Column(nullable = false)，否则插入null值会触发约束错误。

4. 测试代码优化 #

save方法会返回持久化后的对象（包含生成的主键），用返回对象构建预期列表，避免对象引用不一致导致断言失败：

@Test
void findAllByBossId() {
    Employee employee = new Employee("CEO", "Bruce", "Wayne", null);
    Employee savedEmployee = employeeRepository.save(employee);

    List<Employee> employees = employeeRepository.findAllByBossId(null);
    List<Employee> expected = Collections.singletonList(savedEmployee);

    assertIterableEquals(expected, employees);
}

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