Spring Hibernate Java.sql.SQLException: 结果集已关闭

Question

我有一个关于Spring应用程序和使用DTO从数据库获取数据的问题。我遇到了“java.sql.SQLException：结果集已关闭”的错误。数据库是Firebird（Jaybird版本3.0.3）。

我的DTO文件：

package ekoncept.dto;

import ...

public class FakturaListDTO {

    private Integer fakturaId;

    @Getter @Setter private String fakturaPrefix;
    ...
    @Getter @Setter private BigDecimal fakturaZaplacono;

    @Id
    @Column(name = "FAKTURA_ID")
    public Integer getFakturaId() {
        return fakturaId;
    }

    public void setFakturaId(Integer fakturaId) {
        this.fakturaId = fakturaId;
    }

    public FakturaListDTO(Faktura fakt) {          // not working
        this.fakturaId = fakt.getFakturaId();
        this.fakturaPrefix = fakt.getFakturaPrefix();
        ...
        this.fakturaZaplacono = fakt.getFakturaZaplacono();
    }

    public FakturaListDTO(Integer faktId) {  // working
        this.fakturaId = faktId;
    }
}

我的仓库文件：

package ekoncept.repositories;

import ...

@Repository
public interface FakturaRepository extends JpaRepository<Faktura, Integer> {

// this is NOT working:
//@Query("select new ekoncept.dto.FakturaListDTO(f) " +
//            " from Faktura f " +
//            " where f.fakturaData between :data_od and :data_do")

// this is working
@Query("select new ekoncept.dto.FakturaListDTO(f.fakturaId) " +
        " from Faktura f " +
        " where f.fakturaData between :data_od and :data_do")
    List<FakturaListDTO> listByFakturaData(@Param("data_od") Date dataOd, @Param("data_do") Date dataDo);
}

当我在代码库中定义@Query时，就像这样：

@Query("select new ekoncept.dto.FakturaListDTO(f.fakturaId)"+
       " from Faktura f " +
       " where f.fakturaData between :data_od and :data_do")

我成功地获取了JSON响应（当然，只有字段fakturaId不为空，但我收到了200的响应）。

当我像这样定义@Query时：

@Query("select new ekoncept.dto.FakturaListDTO(f)"+
       " from Faktura f " +
       " where f.fakturaData between :data_od and :data_do")

使用构造函数FakturaListDTO（Faktura fakt）时，我收到了500的响应：

"status": 500,
  "error": "Internal Server Error",
  "exception": "org.springframework.dao.InvalidDataAccessResourceUsageException",
  "message": "could not execute query; SQL [select faktura0_.faktura_id as col_0_0_ from faktura faktura0_ where faktura0_.faktura_data between ? and ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query",
  "path": "/api/faktura/getFakturaList"

SQL查询没问题，我设置了spring.jpa.show-sql=true，输出的SQL正在数据库上执行。

在ERROR.log中我看到了"The result set is closed"错误：

2018-04-25 13:47:50 - The result set is closed
2018-04-25 13:47:50 - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select faktura0_.faktura_id as col_0_0_ from faktura faktura0_ where faktura0_.faktura_data between ? and ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause
java.sql.SQLException: The result set is closed
    at org.firebirdsql.jdbc.AbstractResultSet.checkOpen(AbstractResultSet.java:287)
    at org.firebirdsql.jdbc.AbstractResultSet.checkCursorMove(AbstractResultSet.java:275)
    at org.firebirdsql.jdbc.AbstractResultSet.next(AbstractResultSet.java:350)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:968)
    at org.hibernate.loader.Loader.doQuery(Loader.java:930)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
    at org.hibernate.loader.Loader.doList(Loader.java:2610)
    at org.hibernate.loader.Loader.doList(Loader.java:2593)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2422)
    at org.hibernate.loader.Loader.list(Loader.java:2417)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:123)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:87)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:499)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:477)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy107.listByFakturaData(Unknown Source)
    at ekoncept.controller.FakturaController.listByDates(FakturaController.java:75)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:635)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

- Olek

---

错误提示表明结果集已经关闭。这可能是因为Hibernate关闭了它，或者连接是自动提交的，并且语句执行与在同一连接上执行另一个语句交错。你的数据源和JPA配置是什么？如果使用连接属性defaultResultSetHoldable=true会发生什么？ - Mark Rotteveel

将"defaultResultSetHoldable=true"添加到JDBC URL中有所帮助。 现在我的配置如下：spring.datasource.url=jdbc:firebirdsql://localhost:3050/pension?charSet=utf8&defaultResultSetHoldable=true一切都正常工作。 - Olek

那么最好是从一个带有@Transactional注解的方法中调用它。看起来Hibernate在这里以自动提交模式执行操作，并且在同一个连接上执行多个查询，这似乎与Jaybird的默认可持续性（提交后关闭）以及其对自动提交模式下对象生命周期的JDBC要求严格遵守不符。我需要更详细地调查一下才能确定。 - Mark Rotteveel

Answer 1

我最终选择了第二个解决方案，即在文件FakturaRepository.java中添加注释@Transactional。

package ekoncept.repositories;

import ...

@Repository
@Transactional
public interface FakturaRepository extends JpaRepository<Faktura, Integer> {
...
}

我从spring.datasource.url中移除了defaultResultSetHoldable=true

spring.datasource.url=jdbc:firebirdsql://localhost:3050/pension?charSet=utf8

现在一切都正常工作。

- Olek

Answer 2

将 defaultResultSetHoldable=true 添加到 spring.datasource.url 中有所帮助。现在看起来像这样：

spring.datasource.url=jdbc:firebirdsql://localhost:3050/pension?charSet=utf8&defaultResultSetHoldable=true

现在它正常工作了-我得到了带有数据的JSON答案。

- Olek

---

只需注意使用defaultResultSetHoldable会有一些开销，因为它会在处理之前将整个结果集加载到内存中。正如我在评论中建议的那样，考虑尝试使用@Transactional代替。 - Mark Rotteveel