测试UserRepository时遇_User_Seq表不存在错误求助

安全阿岳

2026-7-16

问题排查：H2测试库中找不到_USER_SEQ表导致的SQL语法错误

问题现象

测试UserRepository时持续抛出org.springframework.dao.InvalidDataAccessResourceUsageException，核心错误为org.h2.jdbc.JdbcSQLSyntaxErrorException: 找不到表"_USER_SEQ"（数据库为空），执行select next_val as id_val from _user_seq for update语句失败。

相关代码与配置

UserRepositoryTest

package com.david.caterest.repository;

import com.david.caterest.entity.User;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;

import java.util.Optional;

import static org.assertj.core.api.Assertions.assertThat;

@DataJpaTest
class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    void shouldFindStudentWhenGivenExistentEmail() {
        // given
        String email = "test@gmail.com";
        User user = new User();
        user.setEmail(email);
        userRepository.save(user);
        // when
        Optional<User> result = userRepository.findByEmail(email);

        // then
        assertThat(result).isPresent();
        assertThat(result.get()).isEqualTo(user);
    }

    @Disabled
    @Test
    void shouldNotFindStudentWhenGivenNonexistentEmail() {
        
    }
    
}

UserRepository

public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByDisplayNameAndPassword(String username, String password);
    Optional<User> findByDisplayName(String username);
    Optional<User> findByEmail(String email);
    Boolean existsByEmail(String email);
}

User实体类

package com.david.caterest.entity;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

@Setter
@Getter
@RequiredArgsConstructor
@Entity
@Table(name = "_user")
public class User implements UserDetails {

    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Lob
    @Column(length = 31_457_280) // 30MB
    private Byte[] profilePicture;

    private String displayName;
    private String firstName;
    private String lastName;

    @Column(unique = true)
    private String email;

    private String password;

    private String biography;
    private LocalDate dateOfBirth;
    private String telephoneNumber;
    private String city;
    private String country;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "user")
    private List<Picture> pictures = new ArrayList<>();

    @Enumerated(EnumType.STRING)
    private Role role;

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        return List.of(new SimpleGrantedAuthority(role.name()));
    }

    public String getUsername() {
        return email;
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return true;
    }
}

测试环境application.properties

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.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.properties.hibernate.format_sql=true

spring.servlet.multipart.max-file-size=10MB
server.error.include-message=always

完整堆栈信息

org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work; SQL [n/a]

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:256)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:232)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:163)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:218)
    at jdk.proxy2/jdk.proxy2.$Proxy117.save(Unknown Source)
    at com.david.caterest.repository.UserRepositoryTest.shouldFindStudentWhenGivenExistentEmail(UserRepositoryTest.java:26)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
...
...
Caused by: org.hibernate.exception.SQLGrammarException: error performing isolated work
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcIsolationDelegate.delegateWork(JdbcIsolationDelegate.java:79)
    at org.hibernate.id.enhanced.TableStructure$1.getNextValue(TableStructure.java:135)
    at org.hibernate.id.enhanced.PooledOptimizer.generate(PooledOptimizer.java:73)
    at org.hibernate.id.enhanced.SequenceStyleGenerator.generate(SequenceStyleGenerator.java:536)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:114)
    at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:184)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:129)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:53)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:737)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:721)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:577)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311)
    at jdk.proxy2/jdk.proxy2.$Proxy108.persist(Unknown Source)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:613)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:577)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:288)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516)
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:77)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 76 more
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "_USER_SEQ" not found (this database is empty); SQL statement:
select next_val as id_val from _user_seq for update [42104-214]

原因分析

方言不匹配：测试环境使用H2数据库，但配置中指定了org.hibernate.dialect.MySQLDialect，导致Hibernate按照MySQL的规则生成序列表，而H2无法识别或创建该表。
主键生成策略冲突：GenerationType.AUTO会根据方言自动选择主键生成方式，MySQL使用自增主键，H2默认可能使用序列，但方言错误导致Hibernate尝试访问不存在的序列表。

解决方案

方案1：修改Hibernate方言为H2适配版本

修改测试环境application.properties中的方言配置：

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

方案2：指定主键生成策略为IDENTITY

修改User实体类的主键注解，强制使用自增主键，避免序列表依赖：

@Id 
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

方案3：验证H2依赖配置

确保H2依赖的scope为test，且版本与Spring Boot版本兼容，示例Maven依赖：

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

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

测试UserRepository时遇_User_Seq表不存在错误求助

问题现象 #

相关代码与配置 #

UserRepositoryTest #

UserRepository #

User实体类 #

测试环境application.properties #

完整堆栈信息 #

原因分析 #

解决方案 #

方案1：修改Hibernate方言为H2适配版本 #

方案2：指定主键生成策略为IDENTITY #

方案3：验证H2依赖配置 #