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.

1 2 stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE);

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

Status

Assignee

Vlad Mihalcea

Reporter

Mark Paluch

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.0

Priority

Major