Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
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 scrollableResultSet
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 theResultSet
API to indicate the type of aResultSet
object:TYPE_FORWARD_ONLY
,TYPE_SCROLL_INSENSITIVE
, andTYPE_SCROLL_SENSITIVE
. The second argument is one of twoResultSet
constants for specifying whether a result set is read-only or updatable:CONCUR_READ_ONLY
andCONCUR_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 typeint
, 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 aResultSet
object, you will automatically get one that isTYPE_FORWARD_ONLY
andCONCUR_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 followingResultSet
constants:TYPE_SCROLL_INSENSITIVE
orTYPE_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 isTYPE_SCROLL_INSENSITIVE
does not reflect changes made while it is still open and one that isTYPE_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 aResultSet
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 methodnext
. 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 methodprevious
, which moves the cursor backward (one row toward the beginning of the result set). Both methods returnfalse
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 awhile
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 methodnext
to returnfalse
. The following code fragment prints out the values in each row ofsrs
, 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.99As 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 methodafterLast
. Then the methodprevious
moves the cursor from the position after the last row to the last row, and then to the previous row with each iteration through thewhile
loop. The loop ends when the cursor reaches the position before the first row, where the methodprevious
returnsfalse
.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.99As 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 methodsfirst
,last
,beforeFirst
, andafterLast
move the cursor to the row indicated in their names. The methodabsolute
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 callingabsolute(1)
puts the cursor on the first row. If the number is negative, the cursor moves the given number from the end, so callingabsolute(-1)
puts the cursor on the last row. The following line of code moves the cursor to the fourth row ofsrs
: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 methodprevious
moves the cursor backward one row. With the methodrelative
, 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 rowThe method
getRow
lets you check the number of the row where the cursor is positioned. For example, you can usegetRow
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 3Four 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 aboolean
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 methodprevious
in awhile
loop. If the methodisAfterLast
returnsfalse
, the cursor is not after the last row, so the methodafterLast
is invoked. This guarantees that the cursor will be after the last row and that using the methodprevious
in thewhile
loop will cover every row insrs
.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
andmoveToCurrentRow
. 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
Feedback Form |
Copyright 1995-2005 Sun Microsystems, Inc. All rights reserved.