The JavaTM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC(TM) Database Access
Lesson: 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.

| Start of Tutorial > Start of Trail > Start of Lesson | Search