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

## Iterator Class Implementation and Advanced Functionality

This section discusses how iterator classes are implemented and what additional functionality they offer beyond the essential methods discussed in "Using Named Iterators" and "Using Positional Iterators".

### Implementation and Functionality of Iterator Classes

Any named iterator class you declare will be generated by the SQLJ translator to implement the `sqlj.runtime.NamedIterator` interface. Classes implementing the `NamedIterator` interface have functionality that maps iterator columns to database columns by name, as opposed to by position.

Any positional iterator class you declare will be generated by the SQLJ translator to implement the `sqlj.runtime.PositionedIterator` interface. Classes implementing the `PositionedIterator` interface have functionality that maps iterator columns to database columns by position, as opposed to by name.

Both the `NamedIterator` interface and the `PositionedIterator` interface, and therefore all generated SQLJ iterator classes as well, implement or extend the `sqlj.runtime.ResultSetIterator` interface.

The `ResultSetIterator` interface specifies the following methods for all SQLJ iterators (both named and positional):

- `close()`--Closes the iterator.

- `getResultSet()`--Extracts the underlying JDBC result set from the iterator.

- `isClosed()`--Determines if the iterator has been closed.

- `next()`--Moves to the next row of the iterator.

The `PositionedIterator` interface adds the following method specification for positional iterators:

- `endFetch()`--Determines if you have reached the last row of a positional iterator.

As discussed in "Using Named Iterators", use the `next()` method to advance through the rows of a named iterator, and accessor methods to retrieve the data. The SQLJ generation of a named iterator class defines an accessor method for each iterator column, where each method name is identical to the corresponding column name. For example, if you declare a `name` column, then a `name()` method will be generated.

As discussed in "Using Positional Iterators", use a `FETCH INTO` statement together with the `endFetch()` method to advance through the rows of a positional iterator and retrieve the data. A `FETCH INTO` statement implicitly calls the `next()` method; do not explicitly use the `next()` method in a positional iterator. The `FETCH INTO` statement also implicitly calls accessor methods that are named according to iterator column numbers. The SQLJ generation of a positional iterator class defines an accessor method for each iterator column, where each method name corresponds to the column position.

Use the `close()` method to close any iterator once you are done with it.

The `getResultSet()` method is central to SQLJ-JDBC interoperability and is discussed in "SQLJ Iterator and JDBC Result Set Interoperability".

Note: You can use a `ResultSetIterator` object directly as a weakly typed iterator if you are interested only in converting it to a JDBC result set and you do not need named or positional iterator functionality. For information, see "Using and Converting Weakly Typed Iterators (ResultSetIterator)".

### Use of the IMPLEMENTS Clause in Iterator Declarations

There might be situations where it will be useful to implement an interface in your iterator declaration. For general information and syntax, see "Declaration IMPLEMENTS Clause".

You might, for example, have an iterator class where you want to restrict access to one or more columns. As discussed in "Using Named Iterators", a named iterator class generated by SQLJ has an accessor method for each column in the iterator. If you want to restrict access to certain columns, you can create an interface with only a subset of the accessor methods, then expose instances of the interface type to the user instead of exposing instances of the iterator class type.

For example, assume you are creating a named iterator of employee data, with columns `ENAME` (employee name), `EMPNO` (employee number), and `SAL` (salary). Accomplish this as follows:

    #sql iterator EmpIter (String ename, int empno, float sal);

This generates a class `EmpIter` with `ename()`, `empno()`, and `sal()` accessor methods.

Assume, though, that you want to prevent access to the `SAL` column. You can create an interface `EmpIterIntfc` that has `ename()` and `empno()` methods, but no `sal()` method. Then you can use the following iterator declaration instead of the declaration above (presume `EmpIterIntfc` is in package `mypackage`):

    #sql iterator EmpIter implements mypackage.EmpIterIntfc
         (String emame, int empno, float sal);

Then if you code your application so that users can access data only through `EmpIterIntfc` instances, they will not have access to the `SAL` column.

### Subclassing Iterator Classes

SQLJ supports the ability to subclass iterator classes. This feature can be very useful in allowing you to add functionality to your queries and query results. See "Subclassing Iterators--SubclassIterDemo.sqlj" for an example of an iterator subclass that treats rows of a query as individual objects and writes them into a Java vector.

The one key requirement of an iterator subclass is that you must supply a public constructor that takes an instance of `sqlj.runtime.RTResultSet` as input. The SQLJ runtime will call this constructor in assigning query results to an instance of your subclass. Beyond that, you provide functionality as you choose.

You can continue to use functionality of the original iterator class (the superclass of your subclass). For example, you can advance through query results by calling the `super.next()` method.

### Scrollable Iterators

Patterned after the JDBC 2.0 specification for scrollable JDBC `ResultSet`s, the ISO standard for SQLJ has adopted support for scrollable iterators.

#### Declaring Scrollable Iterators

To characterize an iterator as scrollable, add the following clause to the iterator declaration.

    implements sqlj.runtime.Scrollable

In this case, the SQLJ translator generates an iterator that implements the `Scrollable` interface. The following declaration declares a named, scrollable iterator.

    #sql public static MyScrIter implements sqlj.runtime.Scrollable
                                 (String ename, int empno);

The code that is generated by the SQLJ translator for the `MyScrIter` class will automatically support all of the methods of the `Scrollable` interface. Next is a discussion of those `Scrollable` methods that you can use with positional as well as named scrollable iterators.

#### The Scrollable Interface

You can provide hints about the fetch direction to scrollable iterators. The following methods are defined on scrollable iterators as well as on execution contexts. Use an `ExecutionContext` to provide the default direction when creating a scrollable iterator.

- `getFetchDirection()`--Retrieves the direction for fetching rows from database tables.

- `setFetchDirection(int)` --Gives the SQLJ runtime a hint as to the direction in which rows are processed. The direction should be one of `sqlj.runtime.ResultSetIterator.FETCH_FORWARD`, `FETCH_REVERSE`, or `FETCH_UNKNOWN`.

  If you do not specify a value for the direction on the `ExecutionContext`, then `FETCH_FORWARD` will be used as a default.

There are also a number of predicates on scrollable iterators. All these methods will return `false` whenever the result set underlying the iterator contains no rows.

- `isBeforeFirst()`--Indicates whether the iterator object is before the first row in the result set.

- `isFirst()`--Indicates whether the iterator object is on the first row of the result set.

- `isLast()`--Indicates whether the iterator object is on the last row of the result set. Note that calling the method `isLast()` may be expensive, because the SQLJ driver may need to fetch ahead one row to determine whether the current row is the last row in the result set.

- `isAfterLast()`--Indicates whether the iterator object is after the last row in the result set.

#### Scrollable Named Iterators

Named iterators use movement methods to navigate through the rows of a result set. Non-scrollable iterators only have the `next()` function for movement. Most movement methods for scrollable iterators work similarly to `next(),` in that they try to position the iterator on an actual row of the result set. They return `true` if the iterator ends up on a valid row and `false` if it does not. Additionally, if you attempt to position the iterator object beyond the first (last) row in the result set, this leaves the iterator object before the first (after the last) row, respectively.

- `previous()`--Moves the iterator object to the previous row in the result set.

- `first()`--Moves the iterator object to the first row in the result set.

- `last()`--Moves the iterator object to the last row in the result set.

- `absolute(int)` -- Moves the iterator object to the given row number in the result set. The first row is row 1, the second is row 2, and so on. If the given row number is negative, the iterator object moves to an absolute row position with respect to the end of the result set. For example, calling `absolute(-1)` positions the iterator object on the last row, `absolute(-2)` indicates the next-to-last row, and so on.

- `relative(int)`--Moves the iterator object a relative number of rows, either positive or negative. Calling `relative(0)` is valid, but does not change the iterator object position.

The methods `beforeFirst()` and `afterLast()` return `void`, because they never place the iterator object on an actual row of the result set.

- `afterLast()`--Moves the iterator object to the end of the result set, just after the last row. This has no effect if the result set contains no rows.

- `beforeFirst()`--Moves the iterator object to the front of the result set, just before the first row. This has no effect if the result set contains no rows.

#### Scrollable Positional Iterators

You are already familiar with the `FETCH` syntax for positional iterators. For example:

    #sql { FETCH :iter INTO :x, :y, :z };

This is actually an abbreviated version of the following syntax.

    #sql { FETCH NEXT FROM :iter INTO :x, :y, :z  };

Now it is easy to see the pattern for moving to the prevoius, first, and, respectively, last row in the result set. (Unfortunately, JDBC 2.0--after which the movement methods were modeled--uses `previous()`, whereas the `FETCH` syntax, which is patterned after SQL, employs `PRIOR`. In case you should forget this inconsistency, the SQLJ translator will also accept `FETCH PREVIOUS`.) Note that in all these cases, `iter.endFetch()` returns true whenever the `FETCH` fails to move to a valid row and retrieve values.

    #sql { FETCH PRIOR FROM :iter INTO :x, :y, :z  };
    #sql { FETCH FIRST FROM :iter INTO :x, :y, :z  };
    #sql { FETCH LAST FROM :iter INTO :x, :y, :z  };

Finally, there is also syntax to pass a numeric value to absolute and relative movements. As before, `iter.endFetch()` returns `true` whenever the `FETCH` fails to move to a valid row and retrieve values.

    #sql { FETCH ABSOLUTE :n FROM :iter INTO :x, :y, :z  };
    #sql { FETCH RELATIVE :n FROM :iter INTO :x, :y, :z  };

Note that you must use a host expression--you cannot simply use a constant for the numeric value. Thus, instead of:

    #sql { FETCH RELATIVE :n FROM :iter INTO :x, :y, :z  };

you must write:

    #sql { FETCH RELATIVE 0 FROM :iter INTO :x, :y, :z };

Incidentally, this command leaves the position of the iterator unchanged and--if the iterator is on a valid row--just populates the variables.

#### From JDBC ResultSets to SQLJ Iterators -- FETCH CURRENT Syntax

The last observation is actually useful if you have an existing JDBC program that you want to rewrite in SQLJ with as little modification as possible.

Your JDBC ResultSet will use only movement methods, such as `next()`, `previous()`, `absolute()`, and so on. You can immediately model this in SQLJ through a named iterator. However, this also implies that all columns of the SQL result set must have a proper name. In practice many (if not all) columns of the result will require introduction of alias names. This is unacceptable if the query text is to remain untouched.

The alternative is to define a positional iterator type for the result set. Now no change is made to the query source. However, this approach forces changes to the control-flow logic of the program. Take the following JDBC code sample:

    ResultSet rs = ... // execute ...query...;
    while (rs.next()) {
       x := rs.getXxx(1); y:=rs.getXxx(2);
       ...process...
    }

This translates along the following lines to SQLJ:

    Iterator ri;
    #sql it = { ...query... };
    while(true) {
       #sql { FETCH :it INTO :x, :y };
       if (it.endFetch()) break;
       ...process...
    }

The transformations to the program logic will become even more onerous when considering arbitrary movements on scrollable iterators. Because positional iterators implement all the movement commands of named iterators, it is possible to exploit this and use `RELATIVE :(0)` to populate variables from the iterator:

    Iterator it;
    #sql it = { ...query... };
    while (it.next()) {
       #sql { FETCH RELATIVE :(0) FROM :it INTO :x, :y };
       ...process...
    }

Now, you can preserve both the original query and the original program logic. Unfortunately, there still is one drawback to this approach: the iterator type `Iterator` must be `Scrollable` -- even if this property is not really needed. To address this, the following syntax extension is furnished by Oracle SQLJ:

    #sql { FETCH CURRENT FROM :iter INTO :x, :y, :z  };

Finally, you can rewrite the JDBC example in SQLJ for scrollable as well as non-scrollable iterators.

    AnyIterator ai;
    #sql ai = { ...query... };
    while (ai.next()) {
       #sql { FETCH CURRENT FROM :ai INTO :x, :y };
       ...process...
    }

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