英文:
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'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'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 = "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.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 = "app_user")
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论