org.springframework.dao.InvalidDataAccessResourceUsageException:执行隔离工作时出错；SQL

huangapple
go评论108阅读模式
英文:

org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work; SQL

问题

我想测试我的UserRepository，但我一直收到这个错误 org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work; SQL [n/a] 我不知道我哪里出错了。我想在我的测试中使用H2作为数据库，所以h2依赖范围是测试。我在想可能的原因是这个

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]

但我不知道，求帮助。

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);
//        when(userRepository.findByEmail(email)).thenReturn(Optional.of(user));
        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;

//    @NotBlank
    private String displayName;

//    @NotBlank
    private String firstName;

//    @NotBlank
    private String lastName;

//    @NotBlank
    @Column(unique = true)
    private String email;

//    @NotBlank
    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 (test):

spring.datasource.url=jdbc:h2://mem:db;DB_CLOSE_DELAY=-1
#spring.datasource.url=jdbc:h2:mem:test_db
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

Stacktrace:

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.translateExceptionIfNecessary(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.entityIs

<details>
<summary>英文:</summary>

I want to test my UserRepository, but I keep getting this error ```org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work; SQL [n/a]```` I don&#39;t know where I went wrong. I want to use H2 as the database for my tests, hence why h2 dependency scope is test. I was thinking perhaps the reason is this

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]

But I don&#39;t know, please help.
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 = &quot;test@gmail.com&quot;;
User user = new User();
user.setEmail(email);

// when(userRepository.findByEmail(email)).thenReturn(Optional.of(user));
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;

// @NotBlank
private String displayName;

// @NotBlank
private String firstName;

// @NotBlank
private String lastName;

// @NotBlank
@Column(unique = true)
private String email;

// @NotBlank
private String password;

private String biography;
private LocalDate dateOfBirth;
private String telephoneNumber;
private String city;
private String country;
@OneToMany(cascade = CascadeType.ALL, mappedBy = &quot;user&quot;)
private List&lt;Picture&gt; pictures = new ArrayList&lt;&gt;();
@Enumerated(EnumType.STRING)
private Role role;
@Override
public Collection&lt;? extends GrantedAuthority&gt; 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 (test):

spring.datasource.url=jdbc:h2://mem:db;DB_CLOSE_DELAY=-1
#spring.datasource.url=jdbc:h2:mem:test_db
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

Stacktrace:

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]


</details>
# 答案1
**得分**: 1
这个问题似乎与Hibernate和你的H2数据库的配置错误有关。当你使用`spring.jpa.hibernate.ddl-auto=create-drop`属性时，Hibernate会根据`@Entity`类创建必要的表格。然而，似乎你的用户序列（`_USER_SEQ`）的创建失败了。
主要问题在于你的表格和列命名。在SQL中，以下划线`_`开头的标识符被视为系统标识符，应该避免使用。当Hibernate尝试创建带有系统标识符的表格时，可能会失败或导致意外行为。
尝试将表格名从`_user`更改为不带下划线的名称，比如`user`或`app_user`：
```java
@Table(name = "app_user")

这个更改还应该影响Hibernate用于id字段的@GeneratedValue的序列名称。

此外，你正在使用org.hibernate.dialect.MySQLDialect，这是用于MySQL的，但你的数据库是H2。你应该使用适用于H2数据库的正确方言：

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
英文:

This issue seems to be related to a misconfiguration with Hibernate and your H2 database. When you use the spring.jpa.hibernate.ddl-auto=create-drop property, Hibernate will create the necessary tables based on the @Entity classes. However, it seems that the creation of your user sequence (_USER_SEQ) is failing.

The main issue is your table and column naming. In SQL, identifiers that start with an underscore _ are considered as system identifiers and should be avoided. When Hibernate tries to create a table with a system identifier, it might fail or cause unexpected behaviors.

Try changing the table name from _user to something without an underscore like user or app_user:

@Table(name = &quot;app_user&quot;)

This change should also affect the sequence name that Hibernate will use for the @GeneratedValue of your id field.

Also, you're using org.hibernate.dialect.MySQLDialect which is for MySQL, but your database is H2. You should use the appropriate dialect for your H2 database:

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

huangapple
  • 本文由 发表于 2023年7月14日 01:53:22
  • 转载请务必保留本文链接：https://go.coder-hub.com/76682077.html
  • java
  • junit
  • spring
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定