Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index