Specifying updatability, scrollability, and holdability for
ResultSets in JDBC applications
You use special parameters in the Connection.prepareStatement or Connection.createStatement methods
to specify the updatability, scrollability, and holdability of a ResultSet.
Before you begin
If you plan to update ResultSet objects,
ensure that the enableExtendedDescribe property is not set, or is
set to DB2BaseDataSource.YES (2). Updates of ResultSet objects
do not work correctly unless extended describe capability is enabled.
About this task
By default, ResultSet objects are not
scrollable and not updatable. The default holdability depends on the
data source, and can be determined from the DatabaseMetaData.getResultSetHoldability method.
These steps change the scrollability, updatability, and holdability
attributes for a ResultSet.
Procedure
If the SELECT statement that defines the ResultSet has
no input parameters, invoke the createStatement method
to create a Statement object. Otherwise, invoke
the prepareStatement method to create a PreparedStatement object.
You need to specify forms of the createStatement or prepareStatement methods
that include the resultSetType, resultSetConcurrency,
or resultSetHoldability parameters.
The form
of the createStatement method that supports scrollability
and updatability is:
createStatement(int resultSetType, int resultSetConcurrency);
The
form of the createStatement method that supports
scrollability, updatability, and holdability is:
createStatement(int resultSetType, int resultSetConcurrency,
int resultSetHoldability);
The form
of the prepareStatement method that supports scrollability
and updatability is:
prepareStatement(String sql, int resultSetType,
int resultSetConcurrency);
The form
of the prepareStatement method that supports scrollability,
updatability, and holdability is:
prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability);
Important: In a prepareStatement method
invocation in which the first parameter is a CALL statement, you cannot
specify the scrollability, updatability, or holdability of result
sets that are returned from a stored procedure. Those characteristics
are determined by the stored procedure code, when it declares the
cursors for the result sets that are returned. If you use the prepareStatement method
to prepare a CALL statement, and the prepareStatement call
includes the scrollability, updatability, or holdability parameters,
the IBM® Data Server Driver for JDBC and
SQLJ does
not use those parameter values. A prepareStatement method
with scrollability, updatability, or holdability parameters applies
only to preparation of SQL statements other than the CALL statement.
The
following table contains a list of valid values for resultSetType and resultSetConcurrency.
Table 1. Valid combinations of resultSetType and resultSetConcurrency
for ResultSets
This value does not apply to connections to IBM
Informix®.
resultSetHoldability has two possible
values: HOLD_CURSORS_OVER_COMMIT and CLOSE_CURSORS_AT_COMMIT.
Either of these values can be specified with any valid combination
of resultSetConcurrency and resultSetHoldability.
The value that you set overrides the default holdability for the connection.
Restriction: If
the ResultSet is scrollable, and the ResultSet is
used to select columns from a table on a Db2® on Linux®, UNIX, and Windows systems server,
the SELECT list of the SELECT statement that defines the ResultSet cannot
include columns with the following data types:
LONG VARCHAR
LONG VARGRAPHIC
BLOB
CLOB
XML
A distinct type that is based on any of the previous data types
in this list
A structured type
If the SELECT statement has input parameters, invoke setXXX methods
to pass values to the input parameters.
Invoke the executeQuery method to obtain
the result table from the SELECT statement in a ResultSet object.
For each row that you want to access:
Position the cursor using one of the methods that are
listed in the following table.
Restriction: If resultSetType is TYPE_FORWARD_ONLY,
only ResultSet.next is valid.
Table 2. ResultSet
methods for positioning a scrollable cursor
This method does not apply to connections to IBM
Informix.
If the cursor is before the first row of the ResultSet,
this method positions the cursor on the first row.
If the cursor is after the last row of the ResultSet,
this method positions the cursor on the last row.
If the absolute value of n is
greater than the number of rows in the result set, this method positions
the cursor after the last row if n is positive,
or before the first row if n is negative.
The cursor must be on a valid row of the ResultSet before
you can use this method. If the cursor is before the first row or
after the last row, the method throws an SQLException.
Suppose that m is the number
of rows in the ResultSet and x is
the current row number in the ResultSet. If n>0
and x+n>m,
the driver positions the cursor after the last row. If n<0
and x+n<1, the driver positions
the cursor before the first row.
If you need to know the current cursor position, use
the getRow, isFirst, isLast, isBeforeFirst,
or isAfterLast method to obtain this information.
If you specified a resultSetType value
of TYPE_SCROLL_SENSITIVE in step 1, and you need to see the latest
values of the current row, invoke the refreshRow method.
Recommendation: Because refreshing the rows of a ResultSet can
have a detrimental effect on the performance of your applications,
you should invoke refreshRowonly when
you need to see the latest data.
Perform one or more of the following operations:
To retrieve data from each column of the current row of the ResultSet object,
use getXXX methods.
To update the current row from the underlying table, use updateXXX methods
to assign column values to the current row of the ResultSet.
Then use updateRow to update the corresponding
row of the underlying table. If you decide that you do not want to
update the underlying table, invoke the cancelRowUpdates method
instead of the updateRow method.
The resultSetConcurrency value
for the ResultSet must be CONCUR_UPDATABLE for
you to use these methods.
To delete the current row from the underlying table, use the deleteRow method.
Invoking deleteRow causes the driver to replace
the current row of the ResultSet with a hole.
The resultSetConcurrency value
for the ResultSet must be CONCUR_UPDATABLE for
you to use this method.
Invoke the close method to close the ResultSet object.
Invoke the close method to close the Statement or PreparedStatement object.
Example
The following code demonstrates how
to retrieve all rows from the employee table in reverse order, and
update the phone number for employee number "000010". The
numbers to the right of selected statements correspond to the previously-described
steps.Figure 1. Using a
scrollable cursor
String s;
String stmtsrc;
Connection con;
Statement stmt;
ResultSet rs;
…
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE); 1
// Create a Statement object
// for a scrollable, updatable
// ResultSet
stmtsrc = "SELECT EMPNO, PHONENO FROM EMPLOYEE " +
"FOR UPDATE OF PHONENO";
rs = stmt.executeQuery(stmtsrc); // Create the ResultSet 3
rs.afterLast(); // Position the cursor at the end of
// the ResultSet 4a
while (rs.previous()) { // Position the cursor backward
s = rs.getString("EMPNO"); // Retrieve the employee number 4d
// (column 1 in the result
// table)
System.out.println("Employee number = " + s);
// Print the column value
if (s.compareTo("000010") == 0) { // Look for employee 000010
rs.updateString("PHONENO","4657"); // Update their phone number
rs.updateRow(); // Update the row
}
}
rs.close(); // Close the ResultSet 5
stmt.close(); // Close the Statement 6