Result Set Concurrency
The result set concurrency feature enables or disables whether the result set can be updated or not—that is, it indicates the concurrency mode of the result set. By default, the result set concurrency is set to ResultSet.CONCUR_READ_ONLY, which means the result set cannot be updated. Alternatively, updatability can be enabled by setting the result set concurrency to be ResultSet.CONCUR_UPDATABLE. This allows the result set to be modified.
The DatabaseMetaData.supportsResultSetConcurrency() method can be called to identify valid combinations of result set type and result set concurrency supported by the current JDBC driver (p. 1543).
Result Set Holdability
The result set holdability refers to whether the result set may remain open or be closed when the current transaction is committed (p. 1545). The default value for the result set holdability is actually database dependent. One option is Result-Set.HOLD_CURSORS_OVER_COMMIT, which allows the result sets (cursors) to remain open when the transaction is committed.
Alternatively, holdability can be set to ResultSet.CLOSE_CURSORS_AT_COMMIT, which indicates that ResultSet objects (cursors) should be closed upon commit. Closing cursors can result in better performance for some applications.
The DatabaseMetaData.supportsResultSetHoldability() method can be called to identify the holdability supported by the current JDBC driver (p. 1543).
Please note that not all databases and JDBC drivers support these customizations. Such customizations can be specified when the statement object is created by calling the relevant factory method of the Connection interface. Analogous methods with the same result set customizations are also available for a Statement and a Callable.
The following overloaded method is defined in the Connection interface:
PreparedStatement prepareStatement(String sql,
int resultSetType, int resultSetConcurrency,
int resultSetHoldability)
Example 24.6 illustrates using different ResultSet options. Note that in this example, the call to the updateRow() method will result in the column value of the underlying row in the database to be updated with the column value of the current row in the result set which was just updated. In other words, the updateRow() method commits the changes to the database and thereby the result set is also closed as we have specified ResultSet.CLOSE_CURSORS_AT_COMMIT. However, if the automatic commit is disabled, as shown at (1), the changes in the database due to the updateRow() method will not be committed and the result set will not be closed until an explicit call to the commit() method is executed, as shown at (2) (p. 1545).
Example 24.6 Using ResultSet Options
package dbDemo;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ResultSetCustomization {
public static void main(String[] args) {
final String jdbcURL = “jdbc:derby:musicDB”;
try (var connection = DriverManager.getConnection(jdbcURL);
var statement = connection.prepareStatement(
“select duration from compositions where title = ?”,
ResultSet.TYPE_FORWARD_ONLY, // Forward direction. May reflect
// database changes.
ResultSet.CONCUR_UPDATABLE, // Result set is updatable.
ResultSet.CLOSE_CURSORS_AT_COMMIT // Result set is closed on commit.
)) {
connection.setAutoCommit(false); // (1) Disables automatic commit.
statement.setString(1,”Vacation”);
try (ResultSet resultSet = statement.executeQuery();) {
if (resultSet.next()) { // Moves forward one row.
resultSet.updateInt(“duration”, 147); // Updates the current row
// in the result set.
resultSet.updateRow(); // Updates the underlying
// database.
System.out.println(“Updated”);
}
connection.commit(); // (2) Also closes the result set.
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Possible program output:
Updated
