Java Reference
In-Depth Information
To use this data, code must scroll through the result set:
PreparedStatement ps = c . prepareStatement (...);
ResultSet rs = ps . executeQuery ();
while ( rs . next ()) {
... read the current row ...
The question here is where that data for the 200,448 rows lives. If the entire set of data is re-
turned during the executeQuery() call, then the application will have a very large chunk of
live data in its heap, possibly causing GC and other issues. Instead, if only one row of data is
returned from the call to the next() method, there will be a lot of back-and-forth traffic
between the application and the database as the result set is processed.
As usual, there is no correct answer here; in some cases it will be more efficient to keep the
bulk of the data in the database and retrieve it as needed, while in other cases it will be more
efficient to load all the data at once when the query is executed. To control this, use the
setFetchSize() method on the PreparedStatement object to let the JDBC driver know
how many rows at a time it should transfer.
The default value for this varies by JDBC driver; for example, in Oracle's JDBC drivers, the
default value is 10. When the executeQuery() method is called in the loop shown above,
the database will return 10 rows of data, which will be buffered internally by the JDBC
driver. Each of the first 10 calls to the next() method will process one of those buffered
rows. The 11th call will return to the database to retrieve another 10 rows, and so on.
I've recommended using the setFetchSize() method here on the (prepared) statement object,
but that method also exists on the ResultSet interface. In either case, the size is just a hint. The
JDBC driver is free to ignore that value, or round it to some other value, or anything else it wants
to do. There are no assurances either way, but setting the value before the query is executed is
more likely to result in the hint being honored.
Some JDBC drivers also allow you to set a default fetch size when the connection is created by
passing a property to the getConnection() method of the DriverManager . Consult your
vendor's documentation if that path seems easier to manage.
Search WWH ::

Custom Search