New Features in the JDBC 2.0 API

Moving the Cursor in Scrollable Result Sets

One of the new features in the JDBC 2.0 API is the ability to move a result set's cursor backward as well as forward. There are also methods that let you move the cursor to a particular row and check the position of the cursor. Scrollable result sets make it possible to create a GUI (graphical user interface) tool for browsing result sets, which will probably be one of the main uses for this feature. Another use is moving to a row in order to update it.

Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				     ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

This code is similar to what you have used earlier, except that it adds two arguments to the method createStatement . The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY , TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE . The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int , the compiler will not complain if you switch the order.

Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY (as is the case when you are using only the JDBC 1.0 API).

You will get a scrollable ResultSet object if you specify one of the following ResultSet constants: TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE . The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened. At this stage, you do not need to worry about the finer points of a ResultSet object's capabilities, and we will go into a little more detail later. You might keep in mind, though, the fact that no matter what type of result set you specify, you are always limited by what your DBMS and driver actually provide.

Once you have a scrollable ResultSet object, srs in the previous example, you can use it to move the cursor around in the result set. Remember that when you created a new ResultSet object earlier in this tutorial, it had a cursor positioned before the first row. Even when a result set is scrollable, the cursor is initially positioned before the first row. In the JDBC 1.0 API, the only way to move the cursor was to call the method next . This is still the appropriate method to call when you want to access each row once, going from the first row to the last row, but now you have many other ways to move the cursor.

The counterpart to the method next , which moves the cursor forward one row (toward the end of the result set), is the new method previous , which moves the cursor backward (one row toward the beginning of the result set). Both methods return false when the cursor goes beyond the result set (to the position after the last row or before the first row), which makes it possible to use them in a while loop. You have already used the method next in a while loop, but to refresh your memory, here is an example in which the cursor moves to the first row and then to the next row each time it goes through the while loop. The loop ends when the cursor has gone after the last row, causing the method next to return false . The following code fragment prints out the values in each row of srs , with five spaces between the name and price:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
while (srs.next()) {
	String name = srs.getString("COF_NAME");
	float price = srs.getFloat("PRICE");
	System.out.println(name + "     " + price);
}

The printout will look something like this:

Colombian     7.99
French_Roast     8.99
Espresso     9.99
Colombian_Decaf     8.99
French_Roast_Decaf     9.99

As in the following code fragment, you can process all of the rows is srs going backward, but to do this, the cursor must start out being after the last row. You can move the cursor explicitly to the position after the last row with the method afterLast . Then the method previous moves the cursor from the position after the last row to the last row, and then to the previous row with each iteration through the while loop. The loop ends when the cursor reaches the position before the first row, where the method previous returns false .

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 					     ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
srs.afterLast();
while (srs.previous()) {
	String name = srs.getString("COF_NAME");
	float price = srs.getFloat("PRICE");
	System.out.println(name + "     " + price);
}

The printout will look similar to this:

French_Roast_Decaf     9.99
Colombian_Decaf     8.99
Espresso     9.99
French_Roast     8.99
Colombian     7.99

As you can see, the printout for each will have the same values, but the rows are in the opposite order.

You can move the cursor to a particular row in a ResultSet object. The methods first , last , beforeFirst , and afterLast move the cursor to the row indicated in their names. The method absolute will move the cursor to the row number indicated in the argument passed to it. If the number is positive, the cursor moves the given number from the beginning, so calling absolute(1) puts the cursor on the first row. If the number is negative, the cursor moves the given number from the end, so calling absolute(-1) puts the cursor on the last row. The following line of code moves the cursor to the fourth row of srs :

srs.absolute(4);

If srs has 500 rows, the following line of code will move the cursor to row 497:

 
srs.absolute(-4);

Three methods move the cursor to a position relative to its current position. As you have seen, the method next moves the cursor forward one row, and the method previous moves the cursor backward one row. With the method relative , you can specify how many rows to move from the current row and also the direction in which to move. A positive number moves the cursor forward the given number of rows; a negative number moves the cursor backward the given number of rows. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:

srs.absolute(4); // cursor is on the fourth row
. . . 
srs.relative(-3); // cursor is on the first row
. . . 
srs.relative(2); // cursor is on the third row

The method getRow lets you check the number of the row where the cursor is positioned. For example, you can use getRow to verify the current position of the cursor in the previous example as follows:

 
srs.absolute(4); 
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3); 
int rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2); 
int rowNum = srs.getRow(); // rowNum should be 3

Four additional methods let you verify whether the cursor is at a particular position. The position is stated in their names: isFirst , isLast , isBeforeFirst , isAfterLast . These methods all return a boolean and can therefore be used in a conditional statement. For example, the following code fragment tests to see whether the cursor is after the last row before invoking the method previous in a while loop. If the method isAfterLast returns false , the cursor is not after the last row, so the method afterLast is invoked. This guarantees that the cursor will be after the last row and that using the method previous in the while loop will cover every row in srs .

if (srs.isAfterLast() == false) {
	srs.afterLast();	
}
while (srs.previous()) {
	String name = srs.getString("COF_NAME");
	float price = srs.getFloat("PRICE");
	System.out.println(name + "     " + price);
}

In the next section, you will see how to use the two remaining ResultSet methods for moving the cursor, moveToInsertRow and moveToCurrentRow . You will also see examples illustrating why you might want to move the cursor to certain positions.

New Features in the JDBC 2.0 API