spring hibernate java.sql.SQLException: The result set is closed

Question

I have a problem with Spring application and getting data from database with DTO. I got "java.sql.SQLException: The result set is closed". Database is Firebird (Jaybird version 3.0.3).

My dto file:

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;
    }
}

My Repository file:

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);
}

When in repository I define @Query like this:

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

it works and I get response with JSON (of course only field fakturaId is not null, but I get response 200)

when I define @Query like this:

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

using constructor FakturaListDTO(Faktura fakt) I got response 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 query is ok, I have spring.jpa.show-sql=true and SQL in output is executing on DB.

In ERROR.log I got "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)

The error suggests that the result set is already closed. This could happen if Hibernate closed it, or if the connection is auto-commit and the statement execution was interleaved with executing another statement on the same connection. What is your data source and JPA config? What happens if you use connection property `defaultResultSetHoldable=true`? — Mark Rotteveel, Apr 25 '18 at 14:57
Adding "defaultResultSetHoldable=true" to JDBC url helped. Now I have: spring.datasource.url=jdbc:firebirdsql://localhost:3050/pension?charSet=utf8&defaultResultSetHoldable=true and it's working fine. — Olek, Apr 27 '18 at 12:00
Then it might be better to call this from a `@Transactional` annotated method instead. It looks like Hibernate is doing things here in auto-commit mode and somehow executing multiple queries on the same connection, and that seems to break with Jaybirds default holdability (close on commit) and its strict adherence to the JDBC requirements of object lifetime in auto-commit mode. I will need to investigate this in more detail to be sure. — Mark Rotteveel, Apr 27 '18 at 12:04

score 4 · Accepted Answer · answered Apr 30 '18 at 09:51

I finally choosen the second solution with adding annotiation @Transactional in file FakturaRepository.java

package ekoncept.repositories;

import ...

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

and I removed defaultResultSetHoldable=true from spring.datasource.url

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

Everything is working fine now.

score 2 · Answer 2 · edited Apr 27 '18 at 12:41

2

Adding defaultResultSetHoldable=true to spring.datasource.url helped. Now it looks like this:

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

and it's working fine - I got a JSON answer with data.

edited Apr 27 '18 at 12:41

Mark Rotteveel

100,966
191
140
197

answered Apr 27 '18 at 12:10

Olek

319
8
24

Just be aware that using `defaultResultSetHoldable` has its overhead as it will load the entire result set into memory before it is processed. As I suggested on the comments, consider trying to use `@Transactional` instead. – Mark Rotteveel Apr 27 '18 at 12:40

spring hibernate java.sql.SQLException: The result set is closed

2 Answers2