Streaming query results closes prematurely - Spring Data JPA and Hibernate

Question

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.

Answer 1

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