Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)

Part Number A83724-01

Library

Solution Area

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

Positioning and Processing in Scrollable Result Sets

Scrollable result sets (result set type TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE) allow you to iterate through, them either forward or backward, and to position the result set to any desired row.

This section discusses positioning within a scrollable result set and how to process a scrollable result set backward, instead of forward.

For a complete sample application demonstrating this functionality, see "Positioning in a Result Set--ResultSet2.java".

Positioning in a Scrollable Result Set

In a scrollable result set, you can use several result set methods to move to a desired position and to check the current position.

Methods for Moving to a New Position

The following result set methods are available for moving to a new position in a scrollable result set:

beforeFirst() Method

Positions to before the first row of the result set, or has no effect if there are no rows in the result set.

This is where you would typically start iterating through a result set to process it going forward, and is the default initial position for any kind of result set.

You are outside the result set bounds after a beforeFirst() call. There is no valid current row, and you cannot position relatively from this point.

afterLast() Method

Positions to after the last row of the result set, or has no effect if there are no rows in the result set.

This is where you would typically start iterating through a result set to process it going backward.

You are outside the result set bounds after an afterLast() call. There is no valid current row, and you cannot position relatively from this point.

first() Method

Positions to the first row of the result set, or returns false if there are no rows in the result set.

last() Method

Positions to the last row of the result set, or returns false if there are no rows in the result set.

absolute() Method

Positions to an absolute row from either the beginning or end of the result set. If you input a positive number, it positions from the beginning; if you input a negative number, it positions from the end. This method returns false if there are no rows in the result set.

Attempting to move forward beyond the last row, such as an absolute(11) call if there are 10 rows, will position to after the last row, having the same effect as an afterLast() call.

Attempting to move backward beyond the first row, such as an absolute(-11) call if there are 10 rows, will position to before the first row, having the same effect as a beforeFirst() call.


Note:

Calling absolute(1) is equivalent to calling first(); calling absolute(-1) is equivalent to calling last().  


relative() Method

Moves to a position relative to the current row, either forward if you input a positive number or backward if you input a negative number, or returns false if there are no rows in the result set.

The result set must be at a valid current row for use of the relative() method.

Attempting to move forward beyond the last row will position to after the last row, having the same effect as an afterLast() call.

Attempting to move backward beyond the first row will position to before the first row, having the same effect as a beforeFirst() call.

A relative(0) call is valid but has no effect.


Important:

You cannot position relatively from before the first row (which is the default initial position) or after the last row. Attempting relative positioning from either of these positions would result in a SQL exception.  


Methods for Checking the Current Position

The following result set methods are available for checking the current position in a scrollable result set:

Processing a Scrollable Result Set

In a scrollable result set you can iterate backward instead of forward as you process the result set. The following methods are available:

The previous() method works similarly to the next() method, in that it returns true as long as the new current row is valid, and false as soon as it runs out of rows (has passed the first row).

Backward versus Forward Processing

You can process the entire result set going forward, using the next() method as in JDBC 1.0. This is documented in "Process the Result Set". The default initial position in the result set is before the first row, appropriately, but you can call the beforeFirst() method if you have moved elsewhere since the result set was created.

To process the entire result set going backward, call afterLast(), then use the previous() method. For example (where conn is a connection object):

...
/* NOTE: The specified concurrency type, CONCUR_UPDATABLE, is not relevant to 
this example. */

Statement stmt = conn.createStatement 
         (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");

rs.afterLast();
while (rs.previous())
{
   System.out.println(rs.getString("empno") + " " + rs.getFloat("sal"));
}
...

Unlike relative positioning, you can (and typically do) use next() from before the first row and previous() from after the last row. You do not have to be at a valid current row to use these methods.


Note:

In a non-scrollable result set, you can process only with the next() method. Attempting to use the previous() method will cause a SQL exception.  


Presetting the Fetch Direction

The JDBC 2.0 standard allows the ability to pre-specify the direction, known as the fetch direction, for use in processing a result set. This allows the JDBC driver to optimize its processing. The following result set methods are specified:

With Oracle8i release 8.1.6 and higher, however, the Oracle JDBC drivers support only the forward preset value, which you can specify by inputting the ResultSet.FETCH_FORWARD static constant value.

The values ResultSet.FETCH_REVERSE and ResultSet.FETCH_UNKNOWN are not supported--attempting to specify them causes a SQL warning, and the settings are ignored.



Go to previous page
Go to beginning of chapter
Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Solution Area

Contents

Index