title: Streaming query results closes prematurely - Spring Data JPA and Hibernate
body: <p><strong>Here is a repository with the code in this question exibiting the bug:
  <a href="https://github.com/agsimeonov/stream-bug" rel="noreferrer">https://github.com/agsimeonov/stream-bug</a></strong></p>
  
  <p>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):</p>
  
  <pre><code>try (Stream&lt;Customer&gt; stream = repository.streamAll()) {
    stream.forEach(customer -&gt; {
      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);
    });
  }
  </code></pre>
  
  <p>Here is the domain object:</p>
  
  <pre><code>@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);
    }
  }
  </code></pre>
  
  <p>And here is the repository:</p>
  
  <pre><code>public interface CustomerRepository extends JpaRepository&lt;Customer, Long&gt; {
    @Query("SELECT c FROM Customer c")
    Stream&lt;Customer&gt; streamAll();
  }
  </code></pre>
  
  <p>Doing this result in the following error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:
  <a href="https://github.com/agsimeonov/stream-bug" rel="noreferrer">https://github.com/agsimeonov/stream-bug</a></p>
  
  <p><strong>I know a few things for sure:</strong></p>
  
  <p><strong>First</strong> - 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Second</strong> - 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.</p>
  
  <pre><code>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) {}
  </code></pre>
  
  <p><strong>Third</strong> - Replacing:</p>
  
  <pre><code>Stream&lt;Customer&gt; stream = repository.streamAll()
  </code></pre>
  
  <p>With:</p>
  
  <pre><code>Stream&lt;Customer&gt; stream = repository.findAll().stream()
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>