Getting Up To Date with JDBC API

Some More Advanced Functionality

Scrollable ResultSets
Starting with JDBC 2.0, a number of new features were added that relate to the flexibility of result sets. One of the new features added in JDBC 2.0 is the scrollability of result sets. JDBC 1.0 result sets are limited in such a way that each row in the result set may only be visited once and the result set has to be traversed from front to end.

ResultSet myResults = myStmt.executeQuery(myQueryString);

// visit each item once...

while(myResults.next()) {

    // process the results

}

JDBC 2.0 result sets may be one of three different types with respect to scrollability. Result sets of type TYPE_FORWARD_ONLY are similar to JDBC 1.0 result sets. The two new types of result sets are TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE. Both of these result set types are scrollable. TYPE_SCROLL_INSENSITIVE result sets are scrollable, and are not sensitive to changes made by other transactions. TYPE_SCROLL_SENSITIVE result sets are also scrollable, but they are sensitive to changes made by other transactions. If a result set is one of the scrollable types, then there are a number of methods that will scroll the result set cursor around in more flexible ways than simply calling next() to visit each item sequentially.

// retrieve results from the database
ResultSet results = myStatement.executeQuery(myQueryString);

// moves the cursor to the third row in the set
results.absolute(3);

// moves the cursor to the fifth row from the end
results.absolute(-5);

// moves the cursor backwards one row
results.previous();

// moves the cursor forward 4 rows
results.relative(4);

// moves the cursor backward 2 rows
results.relative(-2);

// moves the cursor to the first row in the result set
results.first();

// moves the cursor to the last row in the result set
results.last();

All of these methods return a boolean that indicates whether or not the cursor is actually on a row in the result set. For example, if the result contains 10 rows and code tries to move the cursor to the 12^th row, then the cursor is left in a position that is after the last row. The methods afterLast() and beforeFirst() can be used to query check this condition also.

ResultSet results = myStatement.executeQuery(myQueryString);

// assume that results contains 10 rows of information

// this will work fine and returns true
results.absolute(5);

// this will move the cursor off the end of the result
// set and will return false
results.relative(7);

// this will also return true now
boolean isAfterLastRow = results.afterLast();

None of this works with result sets of type TYPE_FORWARD_ONLY. The Statement object that returned the result set determines the result set's type. There is an overloaded version of createStatement() in the Connection interface that accepts two integer arguments. The first integer is a result set scrollability type and the second is a result set concurrency type (concurrency will be discussed shortly).

// ResultSet objects returned from this statement 

// should be scrollable, should not be sensitive 

// to changes made by other transactions and should 

// be read only

Statement myStatement = 

  conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 

                             ResultSet.CONCUR_READ_ONLY);

Note that the arguments being passed to the createStatement() method here are only requests for ResultSets to be of the specified type. Not all JDBC drivers support all of these ResultSet types. The ResultSet methods getType() and getConcurrency() will return the ResultSet's actual type and concurrency, respectively.

Updatable ResultSets
Result sets have one of two concurrency types. A read only result set is of type CONCUR_READ_ONLY and an updatable result set is of type CONCUR_UPDATABLE. JDBC 1.0 result sets are all of type CONCUR_READ_ONLY. Updatable result sets allow changes to be made to the data using Java code instead of using SQL statements. This approach may be much more natural for Java developers.

The ResultSet interface defines a number of updateXXX() methods for updating the contents of a result set. There are 2 forms of each of these update methods.

updateInt(int columnIndex, int i);

updateInt(String columnName, int i);



updateString(int columnIndex, String s);

updateString(String columnName, String s);

etc...

The first argument is either the column index that is being updated or a string that represents the name of the column being updated. The index corresponds to the order that the column names were specified in the select statement.

String sql = "select part_no, description, qty_needed from stock where qty_on_hand < 10";
ResultSet results = stmt.executeQuery(sql);
while(results.next()) {
  // update the qty_needed column with the value 500
  results.updateInt(3, 500);

  // send the change to the database
  results.updateRow();
}

After modifying values in a row of a result set, a call should be made to either updateRow() to send the update to the database or cancelRowUpdates() to effectively undo the update.