Java JDBC Lazy-Loaded ResultSet

Question

Is there a way to get a ResultSet you obtain from running a JDBC query to be lazily-loaded? I want each row to be loaded as I request it and not beforehand.

Answer 1

Short answer:

Use Statement.setFetchSize(1) before calling executeQuery().

Long answer:

This depends very much on which JDBC driver you are using. You might want to take a look at this page, which describes the behavior of MySQL, Oracle, SQL Server, and DB2.

Major take-aways:

• Each database (i.e. each JDBC driver) has its own default behavior.
• Some drivers will respect setFetchSize() without any caveats, whereas others require some "help".

MySQL is an especially strange case. See this article. It sounds like if you call setFetchSize(Integer.MIN_VALUE), then it will download the rows one at a time, but it's not perfectly clear.

Another example: here's the documentation for the PostgreSQL behavior. If auto-commit is turned on, then the ResultSet will fetch all the rows at once, but if it's off, then you can use setFetchSize() as expected.

One last thing to keep in mind: these JDBC driver settings only affect what happens on the client side. The server may still load the entire result set into memory, but you can control how the client downloads the results.

Answer 2

Could you not achieve this by setting the fetch size for your Statement to 1?

If you only fetch 1 row at a time each row shouldn't be loaded until you called next() on the ResultSet.

e.g.

Statement statement = connection.createStatement();
statement.setFetchSize(1);
ResultSet resultSet = statement.executeQuery("SELECT .....");
while (resultSet.next())
{
  // process results. each call to next() should fetch the next row
}

Answer 3

There is an answer provided here.

Quote:

The Presto JDBC driver never buffers the entire result set in memory. The server API will return at most ~1MB of data to the driver per request. The driver will not request more data from the server until that data is consumed (by calling the next() method on ResultSet an appropriate number of times).

Because of how the server API works, the driver fetch size is ignored (per the JDBC specification, it is only a hint).

Prove that the setFetchSize is ignored

Answer 4

I think what you would want to do is defer the actually loading of the ResultSet itself. You would need to implement that manually.

Answer 5

You will find this a LOT easier using hibernate. You will basically have to roll-your-own if you are using jdbc directly.

The fetching strategies in hibernate are highly configurable, and will most likely offer performance options you weren't even aware of.