Spring Boot 如何在junit测试用例中忽略内存数据库自定义查询更新的跳过锁定

nvbavucw  于 2023-01-17  发布在  Spring
关注(0)|答案(1)|浏览(138)

我正在编写junit测试用例,我已经在内存h2数据库中配置了这些用例。我正在测试的功能实际上从数据库中挑选一些记录,并对记录进行一些处理。当代码挑选记录时,它会锁定这些记录以进行更新,这样就没有其他进程可以访问这些记录。这件事工作正常,但这是导致测试用例的问题,因为我正在使用内存数据库的测试用例。如何解决此问题我。

@Transactional(propagation = Propagation.MANDATORY)
  @Query(value = "select * from employee where account_id = ?1 limit ?2 for update skip locked", nativeQuery = true)
  public List<Employee> getEmployeesByAccountId(String accountId, Integer limit);

请参见上述代码,这会导致测试用例出现问题。
错误:

Caused by:
    org.hibernate.exception.SQLGrammarException: could not prepare statement
        at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2104)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2034)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
        at org.hibernate.loader.Loader.doQuery(Loader.java:953)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
        at org.hibernate.loader.Loader.doList(Loader.java:2815)
        at org.hibernate.loader.Loader.doList(Loader.java:2797)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2629)
        at org.hibernate.loader.Loader.list(Loader.java:2624)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2123)
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1134)
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:173)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1526)
        at org.hibernate.query.Query.getResultList(Query.java:165)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
        at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618)
        at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
        ... 19 more

        Caused by:
        org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT * FROM EMPLOYEE WHERE ACCOUNT_ID = ? LIMIT ? FOR UPDATE SKIP[*] LOCKED"; SQL statement:
        select * from employee where account_id = ? limit ? for update skip locked [42000-200]
            at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
            at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
            at org.h2.message.DbException.get(DbException.java:205)
            at org.h2.message.DbException.get(DbException.java:181)
            at org.h2.message.DbException.getSyntaxError(DbException.java:229)
            at org.h2.command.Parser.getSyntaxError(Parser.java:1051)
            at org.h2.command.Parser.prepareCommand(Parser.java:741)
            at org.h2.engine.Session.prepareLocal(Session.java:657)
            at org.h2.engine.Session.prepareCommand(Session.java:595)
            at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)
            at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)
            at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:352)
            at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:316)
            at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
            at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
            at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)

谢谢

6g8kf2rb

6g8kf2rb1#

我找到了一个解决方案,它不是解决方案,但对我很有效。
1.在src/test包中创建了与src相同的重复存储库类,但没有for update skip locked查询。
@Transactional(propagation = Propagation.MANDATORY)
@Query(value = "select * from employee where account_id = ?1 limit ?2 ", nativeQuery = true)
public List<Employee> getEmployeesByAccountId(String accountId, Integer limit);
1.因此,无论何时执行测试用例,它都将从测试包中创建的存储库执行查询。
1.它服务的目的,我测试的单一单位,而处理。
1.缺点是Junit测试无法测试同时阅读同一行的功能。

相关问题