Optimizing the Fetch Size
To optimize the processing of rows in the result set that resulted from executing a query, only a certain number of these rows are fetched at a time from the database when they are needed by the result set. The default number to fetch, called the default fetch size, is set by the Statement object that created the result set.
The fetch size achieves a balance between the time it takes to fetch a row (depending on the row size), the total number of rows, and the number of round trips necessary between the database and the application to fetch all the rows.
However, we can improve the performance of processing the result set by providing a hint about the fetch size in the setFetchSize() method. Setting the fetch size only provides the JDBC driver with a hint as to the number of rows that should be fetched at a time from the database when more rows are needed by the result set. Setting the fetch size value to 0 is ignored by the JDBC driver, and it will then decide what fetch size to use. The fetch size can be set at the level of the Statement or the ResultSet.
PreparedStatement statement
= connection.prepareStatement(“select * from compositions”);
statement.setFetchSize(20); // Rows would be downloaded 20 at a time.
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()){
/* Rows are processed one at a time regardless of the fetch size */
if (/* some condition */) {
resultSet.setFetchSize(10); // Rows would be downloaded 10 at a time.
}
}
Calling the next() method will still only move the cursor to the next row, if there are still rows in the result set to traverse, regardless of the actual fetch size that is set. In other words, the program logic does not change in any way regardless of the fetch size optimization.
Extracting Column Values from the Current Row
The column values of the current row indicated by the cursor can be extracted using the get methods provided by the result set (see below).
String strValue1 = resultSet.getString(1); // Using column index.
String strValue2 = resultSet.getString(“column_name”); // Using column name.
We can also use the getObject() generic method, specifying either the column index or the column name together with the corresponding SQL type.
String strValue3 = resultSet.getObject(1, String.class);
String strValue4 = resultSet.getObject(“column_name”, String.class);
If the result set is not processed in a try-with-resources statement, consider closing the result set explicitly as soon as possible, after it has been processed.
resultSet.close();
