Apply fetch-size optimization for result streaming with MySQL

Hibernate 5.2 introduced Query.stream to consume results as stream. A user wanting to benefit from iterative streaming is required to set Query.setFetchSize(Integer.MIN_VALUE) otherwise the ResultSet is entirely stored in memory:

Quote from MySQL JDBC docs:

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and, due to the design of the MySQL [...] If you are working with ResultSets that have a large number of rows [...] you can tell the driver to stream the results back one row at a time.

Users could participate from this optimization if the query yields to a Read-Only cursor and Forward-Only result set by defaulting to Query.setFetchSize(Integer.MIN_VALUE) (on query level) if no other fetch size settings are applied.

Doing so imposes a caveat:

There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

Reference

• https://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html

• https://dev.mysql.com/doc/connector-j/6.0/en/connector-j-reference-implementation-notes.html