Pagination is the best approach when dealing with UI/queries that will be limited to a smaller set of Results. However if i want to iterate over a full table, pagination doesn’t seem to be the best tool for the job.
Maybe it’s better to do the process in the DB altogether and avoid moving tons of data from the DB to the app just to do the processing in Java.
I’m not sure I agree with the current implementation being correct.
It is correct from an OLTP perspective.
Furthermore, this is functionality that has worked in previous versions up until 5.X. Seems silly to break functionality,
Nope. It just happened to work in 3.x. It was never designed or guaranteed to work as it did.
and force users to upgrade to work around an issue that could easily be fixe
If you think it’s worth having this feature, send us a Pull Request with the implementation. That’s the beauty of open source software development.
As for the Connection leak, this shouldn’t be a problem as long as all ResultSets and PreparedStatements are properly cleaned up upon connection close.
The connection should be closed when the Tx is ended. This is the correct way.
Hibernate has gone as far as to provide the ability to postpone releasing the connection until the session is closed. If this is enabled, why not honor the ResultSet.holdability?
Nope. The Connection is bound to the Tx, not to the Session. ResultSet.holdability and OLTP don’t mix well. ResultSet.holdability reminds me of mainframes and 2-tier applications, not web, enterprise apps or microservices over the Internet.
If configured to HOLD_CURSORS_OVER_COMMIT, why forcibly close them after each commit?
Because Hibernate has never guaranteed or ever considered this mode. If you really think you can do it without breaking the current functionality, I’m eager to review your Pull Request.