Hello,

we have huge amounts of data, that are usually read and updated like this:



In hibernate versions before 5.2, this was no problem, since cursor queries lived as long a they were not closed
manually. However in hibernate 5.2 this does not work, since all queries are close on commit, resulting in an Excepton in scroll.next():

org.hibernate.exception.GenericJDBCException: could not advance using next()

In older hibernate version there was an option like

ConnectionReleaseMode.ON_CLOSE

so that cursor queries survived the commmit.
But in hibernate 5 the ConnectionReleaseMode.ON_CLOSE does still close the cursor queries.

We probable missed something?

I don't think your approach is a valid one for the following reasons:

1. There is no need to call session.update. The entity should be already managed by Hibernate, so every change is automatically picked by the dirty checking mechanism and propagated to the DB during flushing.
2. Why do you call commit after every entity modification? The whole point of the write-behind caching mechanism offered by the Hibernate Session is to group multiple entity state transitions into an atomic unit of work. You should batch multiple modifications to reduce the number of database roundtrips.
3. Why do you use Scrolling anyway? In most cases, pagination renders better results than scrolling/streaming.
4. ON_CLOSE is not a correct way to handle the connection release, AFTER_TRANSACTION and AFTER_STATEMENT are much better since a connection should not be kept outside the scope of a transactional context.

Therefore, you should rethink your current data access layer. But there are even more optimizations you can do like statement caching, using subentities to reduce fetching size.

Thanks for answering me.

1. and 2. Just wanted to keep the example small and simple.

3. and 4. Before I confront our application engineers with such a big change in their application logic,
I like to be certain that there is not a more simple solution.

What strikes me is, that hibernate 5 is different than hibernate < 5 and pure JDBC.
And falling back to JDBC is, what seems to be the only option here:

Open the cursor queries in a JDBC connection and transform the data in hibernate.
That doesnt look nice, but the change in application logic is small.