Using a named iterator in an SQLJ application

Use a named iterator to refer to each of the columns in a result table by name.

The steps in using a named iterator are:

1. Declare the iterator.
   You declare any result set iterator using an iterator declaration clause. This causes an iterator class to be created that has the same name as the iterator. For a named iterator, the iterator declaration clause specifies the following information:

   • The name of the iterator
   • A list of column names and Java™ data types
   • Information for a Java class declaration, such as whether the iterator is public or static
   • A set of attributes, such as whether the iterator is holdable, or whether its columns can be updated

   When you declare a named iterator for a query, you specify names for each of the iterator columns. Those names must match the names of columns in the result table for the query. An iterator column name and a result table column name that differ only in case are considered to be matching names. The named iterator class that results from the iterator declaration clause contains accessor methods. There is one accessor method for each column of the iterator. Each accessor method name is the same as the corresponding iterator column name. You use the accessor methods to retrieve data from columns of the result table.

   You need to specify Java data types in the iterators that closely match the corresponding column data types. See "Java, JDBC, and SQL data types" for a list of the best mappings between Java data types and table column data types.

   You can declare an iterator in a number of ways. However, because a Java class underlies each iterator, you need to ensure that when you declare an iterator, the underlying class obeys Java rules. For example, iterators that contain a with-clause must be declared as public. Therefore, if an iterator needs to be public, it can be declared only where a public class is allowed. The following list describes some alternative methods of declaring an iterator:

   • As public, in a source file by itself

     This method lets you use the iterator declaration in other code modules, and provides an iterator that works for all SQLJ applications. In addition, there are no concerns about having other top-level classes or public classes in the same source file.

   • As a top-level class in a source file that contains other top-level class definitions

     Java allows only one public, top-level class in a code module. Therefore, if you need to declare the iterator as public, such as when the iterator includes a with-clause, no other classes in the code module can be declared as public.

   • As a nested static class within another class

     Using this alternative lets you combine the iterator declaration with other class declarations in the same source file, declare the iterator and other classes as public, and make the iterator class visible to other code modules or packages. However, when you reference the iterator from outside the nesting class, you must fully-qualify the iterator name with the name of the nesting class.

   • As an inner class within another class

     When you declare an iterator in this way, you can instantiate it only within an instance of the nesting class. However, you can declare the iterator and other classes in the file as public.

     You cannot cast a JDBC ResultSet to an iterator if the iterator is declared as an inner class. This restriction does not apply to an iterator that is declared as a static nested class. See "Use SQLJ and JDBC in the same application" for more information on casting a ResultSet to a iterator.

2. Create an instance of the iterator class.

   You declare an object of the named iterator class to retrieve rows from a result table.

3. Assign the result table of a SELECT to an instance of the iterator.
   To assign the result table of a SELECT to an iterator, you use an SQLJ assignment clause. The format of the assignment clause for a named iterator is:

   #sql context-clause iterator-object={select-statement};

   See "SQLJ assignment-clause" and "SQLJ context-clause" for more information.

4. Retrieve rows.

   Do this by invoking accessor methods in a loop. Accessor methods have the same names as the corresponding columns in the iterator, and have no parameters. An accessor method returns the value from the corresponding column of the current row in the result table. Use the NamedIterator.next() method to move the cursor forward through the result table.

   To test whether you have retrieved all rows, check the value that is returned when you invoke the next method. next returns a boolean with a value of false if there is no next row.

5. Close the iterator.

   Use the NamedIterator.close method to do this.

#sql  iterator ByName(String LastName, Date HireDate);                    1 
                                // Declare named iterator ByName
{
  …
  ByName nameiter;              // Declare object of ByName class         2 
  #sql [ctxt]
 nameiter={SELECT LASTNAME, HIREDATE FROM EMPLOYEE};                      3 
                                // Assign the result table of the SELECT
                                // to iterator object nameiter
  while (nameiter.next())       // Move the iterator through the result   4 
                                // table and test whether all rows retrieved
  {
    System.out.println( nameiter.LastName() + " was hired on " 
      + nameiter.HireDate());   // Use accessor methods LastName and
                                // HireDate to retrieve column values
  }
  nameiter.close();             // Close the iterator                     5 
}