Scrollable and Updatable Result Sets

As you have seen, the next method of the ResultSet class iterates over the rows in a result set. That is certainly adequate for a program that needs to analyze the data. However, consider a visual data display that shows a table or query result (see Figure 4-7). You usually want the user to be able to move both forward and backward in the result set. But JDBC 1 had no previous method. Programmers who wanted to implement backward iteration had to manually cache the result set data. The scrollable result set in JDBC 2 lets you move forward and backward through a result set and jump to any position in the result set.

Figure 4-7. A GUI view of a query result

Furthermore, once users see the contents of a result set displayed, they may be tempted to edit it. If you supply an editable view to your users, you must ensure that the user edits are posted back to the database. In JDBC 1, you had to program UPDATE statements. In JDBC 2, you can simply update the result set entries, and the database is automatically updated.

JDBC 2 delivers further enhancements to result sets, such as the capability of updating a result set with the most recent data if the data have been modified by another concurrent database connection. JDBC 3 adds yet another refinement, specifying the behavior of result sets when a transaction is committed. However, these advanced features are outside the scope of this introductory chapter. We refer you to the JDBC API Tutorial and Reference and the JDBC specification documents at http://java.sun.com/products/jdbc for more information.

Scrollable Result Sets

To obtain scrollable result sets from your queries, you must obtain a different Statement object with the method

 Statement stat = conn.createStatement(type, concurrency); 

For a prepared statement, use the call

 PreparedStatement stat = conn.prepareStatement(command, type, concurrency); 

The possible values of type and concurrency are listed in Table 4-7 and Table 4-8. You have the following choices:

• Do you want the result set to be scrollable or not? If not, use ResultSet.TYPE_FORWARD_ONLY.

• If the result set is scrollable, do you want it to be able to reflect changes in the database that occurred after the query that yielded it? (In our discussion, we assume the ResultSet.TYPE_SCROLL_INSENSITIVE setting for scrollable result sets. This assumes that the result set does not "sense" database changes that occurred after execution of the query.)

• Do you want to be able to update the database by editing the result set? (See the next section for details.)

Table 4-7. ResultSet Type Values

TYPE_FORWARD_ONLY	The result set is not scrollable.
TYPE_SCROLL_INSENSITIVE	The result set is scrollable but not sensitive to database changes.
TYPE_SCROLL_SENSITIVE	The result set is scrollable and sensitive to database changes.

Table 4-8. ResultSet Concurrency Values

CONCUR_READ_ONLY	The result set cannot be used to update the database.
CONCUR_UPDATABLE	The result set can be used to update the database.

For example, if you simply want to be able to scroll through a result set but you don't want to edit its data, you use:

 Statement stat = conn.createStatement(    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 

All result sets that are returned by method calls

 ResultSet rs = stat.executeQuery(query) 

are now scrollable. A scrollable result set has a cursor that indicates the current position.

NOTE

Actually, a database driver might not be able to honor your request for a scrollable or updatable cursor. (The supportsResultSetType and supportsResultSetConcurrency methods of the DatabaseMetaData class tell you which types and concurrency modes are supported by a particular database.) Even if a database supports all result set modes, a particular query might not be able to yield a result set with all the properties that you requested. (For example, the result set of a complex query may not be updatable.) In that case, the executeQuery method returns a ResultSet of lesser capabilities and adds an SQLWarning to the connection object. You can retrieve warnings with the getWarnings method of the Connection class. Alternatively, you can use the getType and getConcurrency methods of the ResultSet class to find out what mode a result set actually has. If you do not check the result set capabilities and issue an unsupported operation, such as previous on a result set that is not scrollable, then the operation throws a SQLException.

CAUTION

In JDBC 1 drivers, the Connection class does not have a method

 Statement createStatement(int type, int concurrency); 

If a program that you compiled for JDBC 2 inadvertently loads a JDBC 1 driver and then calls this nonexistent method, the program will crash. Unfortunately, there is no JDBC 2 mechanism for querying a driver as to whether it is JDBC 2 compliant. In JDBC 3, you can use the getJDBCMajorVersion and getJDBCMinorVersion methods of the DatabaseMetaData class to find the JDBC version number of the driver.

Scrolling is very simple. You use

 if (rs.previous()) . . . 

to scroll backward. The method returns TRue if the cursor is positioned on an actual row; false if it now is positioned before the first row.

You can move the cursor backward or forward by a number of rows with the command

rs.relative(n);

If n is positive, the cursor moves forward. If n is negative, it moves backward. If n is zero, the call has no effect. If you attempt to move the cursor outside the current set of rows, it is set to point either after the last row or before the first row, depending on the sign of n. Then, the method returns false and the cursor does not move. The method returns TRue if the cursor is positioned on an actual row.

Alternatively, you can set the cursor to a particular row number:

rs.absolute(n);

You get the current row number with the call

 int currentRow = rs.getRow(); 

The first row in the result set has number 1. If the return value is 0, the cursor is not currently on a rowit is either before the first row or after the last row.

The convenience methods

 first last beforeFirst afterLast 

move the cursor to the first, to the last, before the first, or after the last position.

Finally, the methods

 isFirst isLast isBeforeFirst isAfterLast 

test whether the cursor is at one of these special positions.

Using a scrollable result set is very simple. The hard work of caching the query data is carried out behind the scenes by the database driver.

Updatable Result Sets

If you want to be able to edit result set data and have the changes automatically reflected in the database, you create an updatable result set. Updatable result sets don't have to be scrollable, but if you present data to a user for editing, you usually want to allow scrolling as well.

To obtain updatable result sets, you create a statement as follows.

 Statement stat = conn.createStatement(    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); 

The result sets returned by a call to executeQuery are then updatable.

NOTE

Not all queries return updatable result sets. If your query is a join that involves multiple tables, the result may not be updatable. If your query involves only a single table or if it joins multiple tables by their primary keys, you should expect the result set to be updatable. Call the getConcurrency method of the ResultSet class to find out for sure.

For example, suppose you want to raise the prices of some books, but you don't have a simple criterion for issuing an UPDATE command. Then, you can iterate through all books and update prices, based on arbitrary conditions.

 String query = "SELECT * FROM Books"; ResultSet rs = stat.executeQuery(query); while (rs.next()) {    if (. . .)    {       double increase = . . .       double price = rs.getDouble("Price");       rs.updateDouble("Price", price + increase);       rs.updateRow();    } } 

There are updateXxx methods for all data types that correspond to SQL types, such as updateDouble, updateString, and so on. As with the getXxx methods, you specify the name or the number of the column. You then specify the new value for the field.

NOTE

If you use the updateXxx method whose first parameter is the column number, be aware that this is the column number in the result set. It may well be different from the column number in the database.

The updateXxx method changes only the row values, not the database. When you are done with the field updates in a row, you must call the updateRow method. That method sends all updates in the current row to the database. If you move the cursor to another row without calling updateRow, all updates are discarded from the row set and they are never communicated to the database. You can also call the cancelRowUpdates method to cancel the updates to the current row.

The preceding example shows how you modify an existing row. If you want to add a new row to the database, you first use the moveToInsertRow method to move the cursor to a special position, called the insert row. You build up a new row in the insert row position by issuing updateXxx instructions. Finally, when you are done, call the insertRow method to deliver the new row to the database. When you are done inserting, call moveToCurrentRow to move the cursor back to the position before the call to moveToInsertRow. Here is an example:

 rs.moveToInsertRow(); rs.updateString("Title", title); rs.updateString("ISBN", isbn); rs.updateString("Publisher_Id", pubid); rs.updateDouble("Price", price); rs.insertRow(); rs.moveToCurrentRow(); 

Note that you cannot influence where the new data is added in the result set or the database.

Finally, you can delete the row under the cursor.

 rs.deleteRow(); 

The deleteRow method immediately removes the row from both the result set and the database.

The updateRow, insertRow, and deleteRow methods of the ResultSet class give you the same power as executing UPDATE, INSERT, and DELETE SQL commands. However, programmers who are accustomed to the Java programming language may find it more natural to manipulate the database contents through result sets than by constructing SQL statements.

CAUTION

If you are not careful, you can write staggeringly inefficient code with updatable result sets. It is much more efficient to execute an UPDATE statement than it is to make a query and iterate through the result, changing data along the way. Updatable result sets make sense for interactive programs in which a user can make arbitrary changes, but for most programmatic changes, a SQL UPDATE is more appropriate.

 java.sql.Connection 1.1 

• Statement createStatement(int type, int concurrency) 1.2

• PreparedStatement prepareStatement(String command, int type, int concurrency) 1.2

  create a statement or prepared statement that yields result sets with the given type and concurrency.

  Parameters:	command	The command to prepare
  	type	One of the constants TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE of the ResultSet interface
  	concurrency	One of the constants CONCUR_READ_ONLY or CONCUR_UPDATABLE of the ResultSet interface

  
  

• SQLWarning getWarnings()

  returns the first of the pending warnings on this connection, or null if no warnings are pending. The warnings are chained togetherkeep calling getNextWarning on the returned SQLWarning object until that method returns null. This call does not consume the warnings. The SQLWarning class extends SQLException. Use the inherited getErrorCode and getSQLState to analyze the warnings.

• void clearWarnings()

  clears all warnings that have been reported on this connection.

 java.sql.ResultSet 1.1 

• int getType() 1.2

  returns the type of this result set, one of TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE.

• int getConcurrency() 1.2

  returns the concurrency setting of this result set, one of CONCUR_READ_ONLY or CONCUR_UPDATABLE.

• boolean previous() 1.2

  moves the cursor to the preceding row. Returns true if the cursor is positioned on a row or false if the cursor is positioned before the first row.

• int getRow() 1.2

  gets the number of the current row. Rows are numbered starting with 1.

• boolean absolute(int r) 1.2

  moves the cursor to row r. Returns true if the cursor is positioned on a row.

• boolean relative(int d) 1.2

  moves the cursor by d rows. If d is negative, the cursor is moved backward. Returns true if the cursor is positioned on a row.

• boolean first() 1.2

• boolean last() 1.2

  move the cursor to the first or last row. Return true if the cursor is positioned on a row.

• void beforeFirst() 1.2

• void afterLast() 1.2

  move the cursor before the first or after the last row.

• boolean isFirst() 1.2

• boolean isLast() 1.2

  test whether the cursor is at the first or last row.

• boolean isBeforeFirst() 1.2

• boolean isAfterLast() 1.2

  test whether the cursor is before the first or after the last row.

• void moveToInsertRow() 1.2

  moves the cursor to the insert row. The insert row is a special row for inserting new data with the updateXxx and insertRow methods.

• void moveToCurrentRow() 1.2

  moves the cursor back from the insert row to the row that it occupied when the moveToInsertRow method was called.

• void insertRow() 1.2

  inserts the contents of the insert row into the database and the result set.

• void deleteRow() 1.2

  deletes the current row from the database and the result set.

• void updateXxx(int column, Xxx data) 1.2

• void updateXxx(String columnName, Xxx data) 1.2

  (Xxx is a type such as int, double, String, Date, etc.) update a field in the current row of the result set.

• void updateRow() 1.2

  sends the current row updates to the database.

• void cancelRowUpdates() 1.2

  cancels the current row updates.

 java.sql.DatabaseMetaData 1.1 

• boolean supportsResultSetType(int type) 1.2

  returns TRue if the database can support result sets of the given type.

  Parameters:

  type

  One of the constants TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE of the ResultSet interface

  
  

• boolean supportsResultSetConcurrency(int type, int concurrency) 1.2

  returns TRue if the database can support result sets of the given combination of type and concurrency.

  Parameters:	type	One of the constants TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE of the ResultSet interface
  	concurrency	One of the constants CONCUR_READ_ONLY or CONCUR_UPDATABLE of the ResultSet interface