Unable to return Hibernate resultStream from controller.

[hohonuuli]

I have a controller method that returns a JPA result stream. It looks like:

    @Get("/")
    @Transactional
    public Flowable<VideoSequence> findAll() {
        Stream<VideoSequence> all = repository.findAll(); // JPA resultStream
        Flowable<VideoSequence> flow = Observable.fromIterable(all::iterator)
                .observeOn(Schedulers.io())
                .toFlowable(BackpressureStrategy.BUFFER);
        return flow;
    }

I've tried every permutation of Stream/Observable/Flowable I can think of, but whenever this method is called, the hibernate session/transaction is closed before any data is written. The relevant debug logs are:

DEBUG org.springframework.orm.hibernate5.HibernateTransactionManager - Initiating transaction commit
DEBUG org.springframework.orm.hibernate5.HibernateTransactionManager - Committing Hibernate transaction on Session [SessionImpl(64512790<open>)]
DEBUG org.springframework.orm.hibernate5.HibernateTransactionManager - Closing Hibernate Session [SessionImpl(64512790<open>)] after transaction
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 90007, SQLState: 90007
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - The object is already closed [90007-196]
ERROR io.micronaut.http.netty.stream.HttpStreamsHandler - Error occurred writing stream response: could not advance using next()
    org.hibernate.exception.GenericJDBCException: could not advance using next()
[...]
    Caused by: org.h2.jdbc.JdbcSQLException: The object is already closed [90007-196]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

If I eagerly fetch all items, like below, then it works fine. But that's not what I want as the data will get very large and the http clients will typically timeout:

// This works ok
Stream<VideoSequence> all = repository.findAll().collect(Collectors.toList()).stream()

Any suggestions or tips? Am I missing something? If it helps there's a project that demonstrates this error. To recreate it just do the following:

1. git clone https://github.com/hohonuuli/vampiresquid.git
2. cd vampiresquid
3. Make sure you're using Java 11. I'm using some of it's features.
4. gradle test --tests org.mbari.m3.vampiresquid.controllers.VideoSequenceControllerImplSpec --info

Thanks!!

[graemerocher]

Currently the transaction is closed after commit, supporting this would require keeping the connection and transaction open until the stream is complete. Currently that is not implemented.

[fssou]

@hohonuuli Try use @Transaction(readOnly = true)