Apply fetch-size optimization for result streaming with MySQL
Description
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
Environment
MySQL, Forward-only resultsets, Read-only statements
Activity
We could do the same for PostgreSQL, although we need to set a positive value.
After doing some measurements, the cost of streaming is much higher than fetching the whole ResultSet, which is the default behavior.
That being said, the current default is much more appealing that streaming.
