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
}