ResultSet Iterators 

Use

Typically, a query produces a result set with multiple rows. The result set can be processed using an SQLJ result set iterator. A result set iterator is a Java class that represents an SQL cursor with a well-defined number of columns of specified types. A result set iterator class is declared using the following SQLJ declaration:

#sql <modifiers> iterator <java class name> <column list>.

 

Here, <modifiers> is any combination of the Java class modifiers (for example, public, private or static).

The SQLJ translator substitutes this declaration with the declaration of a specific Java class that defines the types of all columns returned by the iterator. An instance of a result set iterator class serves as a handle to a result set in the database. SQLJ supports two types of result set iterators – named iterators and positional iterators. A named iterator enables the columns of a result to be accessed by their name, whereas a positional iterator enables the columns to be accessed by their ordinal position in the result set. It is the syntax of the <column list>  that determines whether an iterator is named or positional.

The declaration of a result set iterator is possible wherever a Java class may be declared, with the exception that it must not be declared locally to a method. We recommend that you keep every declared result set iterator class in a separate file.

In Open SQL/SQLJ, result set iterators can fetch data only in a forward direction.

If an iterator is closed, the underlying result set will be closed on the database. Closing iterators is good programming style.

Features

Named Iterators

A named iterator enables the columns of the result set to be referenced by name. The iterator declaration contains a list of column type and name pairs. The usage of type and name pairs in the iterator declaration determines that it is a named iterator. The syntax for the declaration of a named iterator is as follows:

#sql ( <modifiers> )? iterator <java class name> '(' <java name list> ')' ';'   <java name list> ::= <java datatype> <java id> (, <java datatype> <java id>)*

 

The column list must not contain any duplicate names, using a case-insensitive comparison. The order of the columns in the iterator is not important. The iterator class generated by the SQLJ translator from this declaration will contain accessor methods for each column listed in the column list of the iterator declaration. These accessor methods enable the column values to be retrieved from the result set rows. The generated Java class implements the interface sqlj.runtime.NamedIterator.

#sql public iterator NamedIter (String name, int id);

Declaration of a named iterator class. This SQLJ declaration declares a result set iterator class NamedIter with two columns. One column has the name nameand the type java.lang.String. The other column has the name id and the type int. The usage of named columns in the column list of the iterator declaration determines that NamedIteriterator is a named iterator class.

 

To use an iterator, a reference variable of the iterator class must be declared. An instance of the iterator class is created assigning the result of a query to the reference variable. After the assignment, the result set will point before the first row. The method next() moves the cursor to the next row of the result set. The values of the current result set column can be extracted calling the accessor methods. If all rows of the result set have been processed, the method next() returns false, true  otherwise.

NamedIter namedIter = null;   #sql [ctx] namedIter = { SELECT name, id FROM employee };   while (namedIter.next()) {    System.out.println(namedIter.name() + "  " + namedIter.id()); } namedIter.close();

Named iterator use. An instance variable namedIterof  the class NamedIteris declared. The result of a query is assigned to the variable namedIter. The result set iterator namedIteris processed in a loop until the method next()returns false. On each iteration, the iterator accesses the current row of the result set. The column values are returned by the getter methods name() and id().

 

The column names in the result set of the query are mapped case insensitively to the columns of the iterator. For all columns of the iterator, there must be exactly one column in the result set of the query that has a name which matches the name of the iterator column in a case insensitive way. If this match is ambiguous, an error message will be issued. However, the result set may comprise columns that cannot be mapped to an iterator column. The SQLJ translator checks strictly whether the types of the result set columns are compatible with the types of the corresponding iterator columns.

Note that column names in the result set are mapped to the iterator columns. If a result set column has an alias, it is the alias that will be taken into account. To map aggregates or expressions in the select list to an iterator column, an alias must be used.

#sql iterator CountAgeIter(int cnt, int age); CountAgeIter countAgeIter;   #sql [ctx] countAgeIter =    { SELECT COUNT(*) AS cnt, age FROM employee GROUP BY age };     while (countAgeIter.next()) {    System.out.println(countAgeIter.cnt() + " employees are " +                   countAgeIter.age() + “ years old.”); } countAgeIter.close();

Named iterator and column alias. In this example, the aggregate function COUNT(*) is contained in the select list. The column name of this column is specified by the alias CNT. Using this alias, the column can be mapped to the matching column of a named iterator.  

Positional Iterators

A positional iterator allows the columns of the result set to be referenced by their ordinal position. The iterator declaration contains a list of column-types. Here, it is the usage of types only in the iterator declaration that determines that it is a positional iterator. A positional iterator is declared according to the following syntax:

#sql ( <modifiers> )? iterator <java class name> '(' <java type list> ')' ';'   <java type list> ::= <java datatype> (, <java datatype>)*

 

The generated Java class implements the interface sqlj.runtime.PositionedIterator.

#sql iterator PosIter(String, String);

Declaration of a positional iterator class. This SQLJ declaration declares a positional result set iterator class PosIter. The iterator has two columns with Java type String. It is the usage of anonymous columns in the column list of the iterator declaration that determines that this is a positional iterator.

 

To use a positional iterator, a reference variable of the iterator class has to be declared. The iterator is instantiated by assigning to it the result of a query. A positional iterator requires using the FETCH statement to retrieve the column values of the result set rows into Java host variables. Also, the FETCH statement advances the result set iterator by one row. The endFetch()method indicates whether the last row of the result set has been processed.

Open SQL/SQLJ prohibits to process the result set of a SELECT * query with a positional iterator. The reason is that Open SQL does not guarantee the order of the columns. It may be different at runtime and at design time.

PosIter posIter;   String empl_name; String empl_id;   #sql [ctx] posIter = { SELECT name, id FROM employee }; while (true) {    #sql { FETCH :posIter          INTO :empl_name, :empl_id };    if (posIter.endFetch())       break;    System.out.println(       empl_name + " " + empl_id); } posIter.close();

Positional iterator use. An instance variable posIterof class PosIter is declared. It is instantiated by assigning to it the result of a query.  The iterator posIter is processed in a loop. On every iteration, the column values of the current row are fetched into Java host variables using the SQLJ FETCH command. If the method endFetch() returns true, the last row of the iterator has been processed.

Untyped Iterators

If the result set column types are not known in advance, the sqlj.runtime.ResultSetIterator interface may be used as the target for a query. This iterator is referred to as an untyped iterator. An untyped iterator does not possess accessor methods for the column. It must not be used in a FETCH statement.

sqlj.runtime.ResultSetIterator untypedIter = null; #sql untypedIter = { SELECT ename FROM emp }; ResultSet rs = untypedIter.getResultSet(); while (rs.next()) {    System.out.println("employee name: " + rs.getString(1)); } untypedIter.close(); // close iterator and result set

Untyped iterator. The result of a query is assigned to the untyped iterator untypedIter. The getResultSet()method is used to retrieve the JDBC ResultSet object from the iterator that is processed using JDBC.