Streaming query results closes prematurely - Spring Data JPA and Hibernate

10,899

I posted my question on the Spring Data JPA JIRA as a bug report and the issue was apparently observed before. After some discussion on there I now use @Transactional on the Stream related code to resolve the issue as a workaround. Thanks goes to Oliver Gierke for pointing this out here: https://jira.spring.io/browse/DATAJPA-989?focusedCommentId=133710&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-133710

I have pushed the solution to the bug in the latest commit to my sample bug repository here: https://github.com/agsimeonov/stream-bug/commit/9da536d0a9d921787f6d2d4d75720d363ba0358b

Share:
10,899

Related videos on Youtube

Alexander Simeonov
Author by

Alexander Simeonov

Updated on June 04, 2022

Comments

  • Alexander Simeonov
    Alexander Simeonov almost 2 years

    Here is a repository with the code in this question exibiting the bug: https://github.com/agsimeonov/stream-bug

    I have been attempting to stream query results with Spring Data JPA and Hibernate using the following piece of code (data.txt is a file with 3000 lines with a number on each line):

    try (Stream<Customer> stream = repository.streamAll()) {
      stream.forEach(customer -> {
        try {
          File data = new File(getClass().getClassLoader().getResource("data.txt").getFile());
          try (BufferedReader reader = new BufferedReader(new FileReader(data))) {
            while (reader.readLine() != null) {
              // Do stuff for the current customer
            }
          }
        } catch (IOException e) {}
        System.out.println(customer);
      });
    }
    

    Here is the domain object:

    @Entity
    @Table(name = "customer")
    public class Customer {
    
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
      private String firstName;
      private String lastName;
    
      public Customer() {}
    
      public Customer(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
      }
    
      @Override
      public String toString() {
        return String.format("Customer[id=%d, firstName='%s', lastName='%s']", id, firstName, lastName);
      }
    }
    

    And here is the repository:

    public interface CustomerRepository extends JpaRepository<Customer, Long> {
      @Query("SELECT c FROM Customer c")
      Stream<Customer> streamAll();
    }
    

    Doing this result in the following error:

    org.hibernate.exception.GenericJDBCException: could not advance using next()
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
        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.internal.ScrollableResultsImpl.convert(ScrollableResultsImpl.java:69)
        at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:104)
        at org.springframework.data.jpa.provider.PersistenceProvider$HibernateScrollableResultsIterator.hasNext(PersistenceProvider.java:454)
        at java.util.Iterator.forEachRemaining(Iterator.java:115)
        at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
        at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:580)
        at stream.bug.StreamBugApplication.lambda$0(StreamBugApplication.java:34)
        at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800)
        at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:784)
        at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:771)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175)
        at stream.bug.StreamBugApplication.main(StreamBugApplication.java:22)
    Caused by: org.h2.jdbc.JdbcSQLException: The object is already closed [90007-193]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
        at org.h2.message.DbException.get(DbException.java:179)
        at org.h2.message.DbException.get(DbException.java:155)
        at org.h2.message.DbException.get(DbException.java:144)
        at org.h2.jdbc.JdbcResultSet.checkClosed(JdbcResultSet.java:3202)
        at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:129)
        at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:99)
        ... 12 more
    

    I have spent a lot of time debugging this and I have finally managed to create a small spring-boot example application exhibiting the streaming bug: https://github.com/agsimeonov/stream-bug

    I know a few things for sure:

    First - This bug has nothing to do with the underlying database. While I am using H2 in the example project I have tried with Postgres and the bug still occurs with a very similar error, note I use tomcat connection pooling in my other project, I have tried different connection pools so it is definitely not the connection pool or the underlying database causing this. Here is an example trace with postgres and tomcat connection pooling as you may notice it is very similar:

    org.hibernate.exception.GenericJDBCException: could not advance using next()
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
      at org.hibernate.internal.ScrollableResultsImpl.convert(ScrollableResultsImpl.java:69)
      at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:104)
      at org.springframework.data.jpa.provider.PersistenceProvider$HibernateScrollableResultsIterator.hasNext(PersistenceProvider.java:454)
      at java.util.Iterator.forEachRemaining(Iterator.java:115)
      at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
      at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
      at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
      at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
      at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
      at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
      at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
      at com.trove.sunstone.attributefusion.services.impl.PhysicalServiceImpl.match(PhysicalServiceImpl.java:130)
      at com.trove.sunstone.attributefusion.AppRunner.main(AppRunner.java:31)
      Suppressed: java.lang.reflect.UndeclaredThrowableException
        at com.sun.proxy.$Proxy238.hashCode(Unknown Source)
        at java.util.HashMap.hash(HashMap.java:338)
        at java.util.HashMap.get(HashMap.java:556)
        at org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl.release(ResourceRegistryStandardImpl.java:76)
        at org.hibernate.internal.AbstractScrollableResults.close(AbstractScrollableResults.java:104)
        at org.springframework.data.jpa.provider.PersistenceProvider$HibernateScrollableResultsIterator.close(PersistenceProvider.java:465)
        at org.springframework.data.util.StreamUtils$CloseableIteratorDisposingRunnable.run(StreamUtils.java:96)
        at java.util.stream.AbstractPipeline.close(AbstractPipeline.java:323)
        at com.trove.sunstone.attributefusion.services.impl.PhysicalServiceImpl.match(PhysicalServiceImpl.java:137)
        ... 1 more
      Caused by: java.sql.SQLException: Statement closed.
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:224)
        ... 10 more
    Caused by: org.postgresql.util.PSQLException: This ResultSet is closed.
      at org.postgresql.jdbc.PgResultSet.checkClosed(PgResultSet.java:2740)
      at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1817)
      at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:99)
      ... 11 more
    

    Second - the odd part is that removing the following lines from the forEach() in the stream results in the stream finishing properly. This leads me to believe it may be some sort of timing issue, however I have attempted to replicate it with Thread.sleep() instead of file reading with no success. As a side note data.txt is a file with 3000 lines with a number on each line.

    try {
      File data = new File(getClass().getClassLoader().getResource("data.txt").getFile());
      try (BufferedReader reader = new BufferedReader(new FileReader(data))) {
        while (reader.readLine() != null) {
          // Do stuff for the current customer
        }
      }
    } catch (IOException e) {}
    

    Third - Replacing:

    Stream<Customer> stream = repository.streamAll()
    

    With:

    Stream<Customer> stream = repository.findAll().stream()
    

    Fixes the issue so this is definitely a bug with streaming and/or ScrollableResults as loading all the data into a list makes the application finish with no errors, however for my current project I need to use Streams directly so using findAll() is not an option.

    If someone has encountered this issue and been able to fix it please let me know. Also please feel free to check out, fork, and/or change code in the provided repository which can help resolve this issue. I have created this project as a demo that should be used to illustrate the bug.

  • Guido Medina
    Guido Medina almost 5 years
    I ran into the same issue, thank you all for the question & answer.
  • Triple S
    Triple S about 2 years
    In my situation the stream is exposed via a REST endpoint, thus that was the place @Transactional had to be added. But this does not seem to solve the problem if you use Jersey for the REST endpoints! Switching to Spring MVC solved it (with using @Transactional).