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.
-
Without knowing more about the problem you're trying to solve I may be making a sweeping generalization but this sounds like quite a strange thing to do with potential performance issues if the driver implementation actually does do remote call for each row (which as pointed out in the answers below won't necessarily be the case).Nick Holt– Nick Holt2009-06-12 08:29:58 +00:00Commented Jun 12, 2009 at 8:29
Add a comment
|
5 Answers
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.
2 Comments
Nick Holt
With the implication being that you can't rely on the one row retrieved per call to ResultSet.next() and therefore, imho, this probably isn't the solution to the problem you're trying to solve.
Matt Solnit
Hi Nick. Thanks for your comment. I tried to re-phrase the answer to explain that, in theory, setFetchSize() is the answer, but again, it depends on which database you're using. This might be the right answer if, for example, the OP is using Oracle.
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
}
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).
Comments
I think what you would want to do is defer the actually loading of the ResultSet itself. You would need to implement that manually.
Comments
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.
2 Comments
Nick Holt
I think the down votes are because it doesn't answer the question, not because the advice is wrong per se.
Jens Schauder
And Hibernate does actually the opposite by giving you a List.