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 ResultSets, 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...
}

---