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:

• void beforeFirst() throws SQLException

• void afterLast() throws SQLException

• boolean first() throws SQLException

• boolean last() throws SQLException

• boolean absolute(int row) throws SQLException

• boolean relative(int row) throws SQLException

  Note: You cannot position a forward-only result set. Any attempt to position it or to determine the current position will result in a SQL exception.

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:

• boolean isBeforeFirst() throws SQLException

  Returns true if the position is before the first row.

• boolean isAfterLast() throws SQLException

  Returns true if the position is after the last row.

• boolean isFirst() throws SQLException

  Returns true if the position is at the first row.

• boolean isLast() throws SQLException

  Returns true if the position is at the last row.

• int getRow() throws SQLException

  Returns the row number of the current row, or returns 0 if there is no valid current row.

  Note: The boolean methods--isFirst(), isLast(), isAfterFirst(), and isAfterLast()--all return false (and do not throw an exception) if there are no rows in the 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:

• boolean next() throws SQLException

• boolean previous() throws SQLException

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:

• void setFetchDirection(int direction) throws SQLException

• int getFetchDirection() throws SQLException

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.

---