Multi-Row Query Results--SQLJ Iterators

A large number of SQL operations are multi-row queries. Processing multi-row query-results in SQLJ requires a SQLJ iterator, which is a strongly typed version of a JDBC result set and is associated with the underlying database cursor. SQLJ iterators are used first and foremost to take query results from a SELECT statement.

Additionally, Oracle SQLJ offers extensions that allow you to use SQLJ iterators and result sets in the following ways:

• as OUT host variables in executable SQL statements

• as INTO-list targets, such as in a SELECT INTO statement

• as a return type from a stored function call

• as column types in iterator declarations (essentially, nested iterators)

For information about use as stored function returns, see "Using Iterators and Result Sets as Stored Function Returns", after stored procedures and stored functions have been discussed. The other uses listed above are documented later in this section.

For information about advanced iterator topics, see "Iterator Class Implementation and Advanced Functionality". This section discusses how iterator classes are implemented and what advanced functionality is available, such as interoperability with JDBC result sets.

Iterator Concepts

Before using an iterator object, you must declare an iterator class. An iterator declaration specifies a Java class that SQLJ constructs for you, where the class attributes define the types (and optionally the names) of the columns of data in the iterator.

A SQLJ iterator object is an instantiation of such a specifically declared iterator class, with a fixed number of columns of predefined type. This is as opposed to a JDBC result set object, which is an instantiation of the generic java.sql.ResultSet class and can, in principle, contain any number of columns of any type.

When you declare an iterator, you specify either just the datatypes of the selected columns, or both the datatypes and the names of the selected columns:

• Specifying the names and datatypes defines a named iterator class.

• Specifying just the datatypes defines a positional iterator class.

The datatypes (and names, if applicable) that you declare determine how query results will be stored in iterator objects you instantiate from that class. SQL data that are retrieved into an iterator object are converted to the Java types that are specified in the iterator declaration.

When you query to populate a named iterator object, the names and datatypes of the SELECT-fields must match the names and types of the iterator columns (case-insensitive). The order of the SELECT-fields is irrelevant--all that matters is that each SELECT-field name matches an iterator column name. In the simplest case, the database column names directly match the iterator column names. For example, data from an EMP column in a database table can be selected and put into an iterator emp column. Alternatively, you can use an alias to map a database column name to an iterator column name. Furthermore, in a more complicated query, you can perform an operation between two columns and alias the result to match the corresponding iterator column name.

Because SQLJ iterators are strongly typed, they offer the benefit of Java type-checking during the SQLJ semantics-checking phase.

An an example, assume the following table:

CREATE TABLE EMPSAL (
   ID NUMBER(5),
   NAME VARCHAR(30),
   OLDSAL NUMBER(10),
   RAISE NUMBER(10) );

Given this table, you can declare and use a named iterator as follows.

Declaration:

#sql iterator SalNamedIter (int id, String empname, float newsalary);

Executable code:

class MyClass {
   void func() throws SQLException {
      ...
      SalNamedIter niter = null;
      #sql niter = { SELECT name AS empname, id, oldsal + raise 
                    AS newsalary FROM empsal };
      ...
   }
}

The id columns match directly, an alias is used to map the database name column to the iterator empname column, and the newsalary target for the oldsal + raise operation matches the newsalary column of the iterator. The order of items in the SELECT statement does not matter.

When you query to populate a positional iterator object, the data is retrieved according to the order in which you select the columns. Data from the first column selected from the database table is placed into the first column of the iterator, and so on. The datatypes of the table columns must be convertible to the types of the iterator columns, but the names of the database columns are irrelevant, as the iterator columns have no names.

Given the EMPSAL table above, you can declare and use a positional iterator as follows.

Declaration:

#sql iterator SalPosIter (int, String, float);

Executable code:

class MyClass {
   void func() throws SQLException {
      ...
      SalPosIter piter = null;
      #sql piter = { SELECT id, name, oldsal + raise FROM empsal };
      ...
   }
}

Note that the data items are in the same order in the table, iterator, and SELECT statement.

General Steps in Using an Iterator

Five general steps are involved in using either kind of SQLJ iterator:

1. Use a SQLJ declaration to define the iterator class (in other words, to define the iterator type).

2. Declare a variable of the iterator class.

3. Populate the iterator variable with the results from a SQL query, using a SELECT statement.

4. Access the query columns in the iterator (how to accomplish this differs between named iterators and positional iterators, as explained below).

5. When you finish processing the results of the query, close the iterator to release its resources.

Named Iterators vs. Positional Iterators

There are advantages and appropriate situations for each of the two kinds of SQLJ iterators.

Named iterators allow greater flexibility. Because data selection into a named iterator matches SELECT-fields to iterator columns by name, you need not be concerned about the order in your query. This is less prone to error, as it is not possible for data to be placed into the wrong column. If the names don't match, the SQLJ translator will generate an error when it checks your SQL statements against the database.

Positional iterators offer a familiar paradigm and syntax to developers who have experience with other embedded-SQL languages. With named iterators you use a next() method to retrieve data, while with positional iterators you use FETCH INTO syntax similar to that of Pro*C, for example. (Each fetch implicitly advances to the next row of the iterator upon completion.)

Positional iterators do, however, offer less flexibility than named iterators, because you are selecting data into iterator columns by position instead of by name. You must be certain of the order of items in your SELECT statement. You also must select data into all columns of the iterator, and it is possible to have data written into the wrong iterator column if the type of that column happens to match the datatype of the table column being selected.

Access to individual data elements is also less convenient with positional iterators. Named iterators, because they store data by name, are able to have convenient accessor methods for each column (for example, there would be an emp() method to retrieve data from an emp iterator column). With positional iterators, you must fetch data directly into Java host expressions with your FETCH INTO statement and the host expressions must be in the correct order.

Using Named Iterators

When you declare a named iterator class, you declare the name as well as the datatype of each column of the iterator.

When you select data into a named iterator, the SELECT-fields must match the iterator columns in two ways:

• The name of each SELECT-field (either a table column name or an alias) must match an iterator column name (case-insensitive, so emp would match EMP).

• The type of each iterator column must be compatible with the datatype of the corresponding SELECT-field, according to standard JDBC type mappings.

The order in which attributes are declared in your named iterator class declaration is irrelevant. Data is selected into the iterator based on name alone.

A named iterator has a next() method to retrieve data row by row, and an accessor method for each column to retrieve the individual data items. The accessor method name is identical to the column name. (Unlike most accessor method names in Java, accessor method names in named iterator classes do not start with "get".) For example, a named iterator object with a column sal would have a sal() accessor method.

Declaring Named Iterator Classes

Use the following syntax to declare a named iterator class:

#sql <modifiers> iterator classname <implements clause> <with clause> 
     ( type-name-list );

Where modifiers is an optional sequence of legal Java class modifiers, classname is the desired class name for the iterator, and type-name-list is a list of the Java types and names equivalent to (convertible from) the column types and column names in a database table.

The implements clause and with clause are optional, specifying interfaces to implement and variables to define and initialize, respectively. These are discussed in "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause".

For example, you might declare the following named iterator for use with company projects:

#sql public iterator ProjIter (String projname, int id, Date deadline);

This will result in an iterator class with columns of data that are accessible using the following provided accessor methods: projname(), id(), and deadline().

Instantiating and Populating Named Iterators

Declare a named iterator variable and then instantiate and populate it using a SELECT statement. For example, declare an instance of the above ProjIter named iterator:

ProjIter projs;

Now presume you want to populate this iterator with data from a database table defined as follows:

CREATE TABLE PROJECTS (
   ID NUMBER(4),
   NAME VARCHAR(30),
   START_DATE DATE,
   DURATION NUMBER(3) );

There are columns in this table whose names and datatypes match the id and name columns of the iterator, but you must use an alias (and perform an operation as appropriate) to populate the deadline column of the iterator. Here is an example:

#sql projs = { SELECT start_date + duration AS deadline, name, id FROM projects
             WHERE start_date + duration >= sysdate };

This calculates a deadline for each project by adding its duration to its start date, then aliases the results as deadline to match the iterator column deadline. It also uses a WHERE clause so that only future deadlines are processed (deadlines beyond the current system date in the database).

Similarly, you must create an alias if you want to use a function call. Suppose you have a database table Emp with a column comm and a function maximum() that takes a comm entry and an integer as input and returns the maximum of the two. (For example, you could input a 0 to avoid negative numbers in your iterator.) Additionally, suppose you declare an iterator class with a corresponding maxComm column.

Now consider the following SELECT syntax:

SELECT maximum(comm, 0) FROM Emp

This is valid SELECT syntax, but you cannot use it to populate a named iterator because "maximum(comm, 0)" is not an iterator column name (and cannot possibly be, because that is not a valid Java identifier). You can, however, work around this problem by using an alias, as follows:

#sql emps = { SELECT maximum(comm, 0) AS maxComm FROM Emp };

Generally, you must use an alias in your query for any SELECT-field whose name is not a legal Java identifier or does not match a column name in your iterator.

Remember that in populating a named iterator, the number of columns you select from the database can never be less than the number of columns in the iterator. The number of columns you select can be greater than the number of columns in the iterator (unmatched columns are ignored), but this will generate a warning unless you have the SQLJ -warn=nostrict option set.

Accessing Named Iterators

Use the next() method of the named iterator object to step through the data that was selected into it. To access each column of each row, use the accessor methods that are generated by SQLJ, typically inside a while loop.

Whenever next() is called:

• If there is another row to retrieve from the iterator, next() retrieves the row and returns true.

• If there are no more rows to retrieve, next() returns false.

The following is an example of how to access the data of a named iterator (repeating the declaration, instantiation, and population used under "Instantiating and Populating Named Iterators").

Presume the following iterator class declaration:

#sql public iterator ProjIter (String projname, int id, Date deadline);

Populate and then access an instance of this iterator class as follows:

// Declare the iterator variable
ProjIter projs = null;

// Instantiate and populate iterator; order of SELECT doesn't matter
#sql projs = { SELECT start_date + duration as deadline, projname, id 
               FROM projects WHERE start_date + duration >= sysdate };

// Process the results
while (projs.next()) {
   System.out.println("Project name is " + projs.projname());
   System.out.println("Project ID is " + projs.id());
   System.out.println("Project deadline is " + projs.deadline());
}  

// Close the iterator
projs.close();
...

Note the convenient use of the projname(), id(), and deadline() accessor methods to retrieve the data. Note also that the order of the SELECT items does not matter, nor does the order in which the accessor methods are used.

Remember, however, that accessor method names are created with the case exactly as in your declaration of the iterator class. The following will generate compilation errors.

Declaration:

#sql iterator Cursor1 (String NAME);

Executable code:

...
Cursor1 c1;
#sql c1 = { SELECT NAME FROM TABLE };
while (c1.next()) {
   System.out.println("The name is " + c1.name());
}
...

The Cursor1 class has a method called NAME(), not name(). You would have to use c1.NAME() in the System.out.println statement.

For a complete sample of using a named iterator, see "Named Iterator--NamedIterDemo.sqlj".

Using Positional Iterators

When you declare a positional iterator class, you only declare the datatype of each column; you do not define column names. The Java types into which the columns of the SQL query results are selected must be compatible with the datatypes of the SQL data. The names of the database columns or SELECT-fields are irrelevant.

Because names are not used, the order in which you declare your positional iterator Java types must exactly match the order in which the data is selected.

To retrieve data from a positional iterator once data has been selected into it, use a FETCH INTO statement followed by an endFetch() method call to determine if you have reached the end of the data (as detailed under "Accessing Positional Iterators").

Declaring Positional Iterator Classes

Use the following syntax to declare a positional iterator class:

#sql <modifiers> iterator classname <implements clause> <with clause> 
     ( position-list );

Where modifiers is an optional sequence of legal Java class modifiers, and the position-list is a list of the Java types compatible with the column types in a database table.

The implements clause and with clause are optional, specifying interfaces to implement and variables to define and initialize, respectively. These are discussed in "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause".

For example, consider a database table defined as follows:

#sql public iterator EmpIter (int, String, float);

This defines Java class EmpIter with unnamed integer, string, and float columns.

Instantiating and Populating Positional Iterators

Instantiating and populating a positional iterator is no different than doing so for a named iterator, except that you must be certain that your SELECT-fields are in the proper order.

Declare a variable of the EmpIter positional iterator class:

EmpIter emps = null;

Now presume that you want to populate this iterator with data from a database table defined as follows:

CREATE TABLE EMPLOYEES (
   EMPNUM NUMBER(5),
   EMPSAL NUMBER(8,2),
   EMPNAME VARCHAR2(30) );

These three datatypes are compatible with the types of the EmpIter positional iterator columns, but be careful about how you select the data because the order is different. The following will work--instantiating and populating the iterator--as the SELECT-fields are in the same order as the iterator columns:

// Populate iterator emps  
#sql emps = { SELECT empnum, empname, empsal FROM employees };

Remember that in populating a positional iterator, the number of columns you select from the database must equal the number of columns in the iterator.

Accessing Positional Iterators

Access the columns defined by a positional iterator using SQL FETCH INTO syntax.

The INTO part of the command specifies Java host variables that receive the results columns. The host variables must be in the same order as the corresponding iterator columns. Use the endFetch() method that is provided with all positional iterator classes to determine when you have reached the end of the data.

The following is an example (repeating the declaration, instantiation, and population used under "Instantiating and Populating Positional Iterators").

Note that the Java host variables in the SELECT statement are in the same order as the columns of the positional iterator, which is mandatory.

First, presume the following iterator class declaration:

#sql public iterator EmpIter (int, String, float);
...

Populate and then access an instance of this iterator class as follows:

// Declare and initialize host variables
int id=0;
String name=null;
float salary=0.0f;

// Declare an iterator instance
EmpIter emps;

#sql emps = { SELECT empnum, empname, empsal FROM employees };

while (true) {
   #sql { FETCH :emps INTO :id, :name, :salary };
   if (emps.endFetch()) break;   // This test must be AFTER fetch,
                                 // but before results are processed.
   System.out.println("Name is " + name);
   System.out.println("Employee number is " + id);
   System.out.println("Salary is " + salary);
   ...
}

emps.close();
...

The id, name, and salary variables are Java host variables whose types must match the types of the iterator columns.

Explicitly using the next() method is not necessary for a positional iterator, because FETCH calls it implicitly to move to the next row.

For a complete sample of using a positional iterator, see "Positional Iterator--PosIterDemo.sqlj".

Using Iterators and Result Sets as Host Variables

SQLJ supports SQLJ iterators and JDBC result sets as host variables, as illustrated in the examples below.

As you will see from the following examples, using iterators and result sets is fundamentally the same, with differences in declarations and in accessor methods to retrieve the data.

Example: Use of Iterator as OUT Host Variable

This example uses a named iterator as an output host variable.

Declaration:

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

Executable code:

...
EmpIter iter;
...
#sql { BEGIN
          OPEN :OUT iter FOR SELECT ename, empno FROM emp;
       END; };

while (iter.next())
{
   String name = iter.ename();
   int empno = iter.empno();
}
iter.close();
...

This example opens iterator iter in a PL/SQL block to receive data from a SELECT statement, selects data from the ename and empno columns of the emp table, then loops through the iterator to retrieve data into local variables.

Example: Use of Result Set as OUT Host Variable

This example uses a JDBC result set as an output host variable.

...
ResultSet rs;
...
#sql { BEGIN
          OPEN :OUT rs FOR SELECT ename, empno FROM emp;
       END; };

while (rs.next())
{
   String name = rs.getString(1);
   int empno = rs.getInt(2);
}
rs.close();
...

This example opens result set rs in a PL/SQL block to receive data from a SELECT statement, selects data from the ename and empno columns of the emp table, then loops through the result set to retrieve data into local variables.

Example: Use of Iterator as OUT Host Variable for SELECT INTO

This example uses a named iterator as an output host variable, taking data through a SELECT INTO statement. (OUT is the default for host variables in an INTO-list. For information about SELECT INTO statements and syntax, see "Single-Row Query Results--SELECT INTO Statements".)

Declaration:

#sql iterator DNameIter (String dname);

Executable code:

...
DNameIter dnameIter;
String ename;
...

#sql { SELECT ename, cursor (SELECT dname FROM dept WHERE deptno = emp.deptno)
       INTO :ename, :dnameIter FROM emp WHERE empno = 7788 };

System.out.println(ename);
while (dnameIter.next())
{
   System.out.println(dnameIter.dname());
}
dnameIter.close();
... 

This example uses nested SELECT statements to accomplish the following:

• Selects the name of employee number 7788 from the emp table, selecting it into the output host variable ename.

• Selects data from the dept table into a cursor wherever the department number matches one of employee 7788's department numbers, selecting that cursor into the output host variable dnameIter, which is a named iterator.

• Prints the employee's name.

• Loops through the named iterator, printing all of his or her department numbers.

Using Iterators and Result Sets as Iterator Columns

Oracle SQLJ includes extensions that allow iterator declarations to specify columns of type ResultSet or columns of other iterator types that were declared within the current scope. In other words, iterators and result sets can exist within iterators in Oracle SQLJ. These column types are used to retrieve a column in the form of a cursor. This is useful for nested SELECT statements that return nested table information.

The following examples are functionally identical, but the first example uses named iterators within a named iterator, the second example uses result sets within a named iterator, and the third example uses named iterators within a positional iterator.

Example: Named Iterator Column in a Named Iterator

This example uses a named iterator that has a column whose type is that of a previously defined named iterator (nested iterators).

Declarations:

#sql iterator DNameIter (String dname);
#sql iterator NameDeptIter2 (String ename, DNameIter depts);

Executable code:

...
NameDeptIter2 iter;
...
#sql iter = { SELECT ename, cursor 
             (SELECT dname FROM dept WHERE deptno = emp.deptno) 
              AS depts FROM emp };

while (iter.next())
{
   System.out.println(iter.ename());
   DNameIter deptsIter = iter.depts();
   while (deptsIter.next())
   {
      System.out.println(deptsIter.dname());
   }
   deptsIter.close();
}
iter.close();
...

This example uses a nested iterator (iterators in a column within another iterator) to print all the departments of each employee in the emp table, as follows:

• Selects each ename (employee name) from the emp table.

• Does a nested SELECT into a cursor to get all the departments from the dept table for each employee (each department that the employee belongs to).

• Puts this name and department data into the outer iterator (iter), which has a name column and an iterator column. The cursor with the department information for any given employee goes into the iterator column of that employee's row of the outer iterator.

• Goes through a nested loop that, for each employee, prints their name and then loops through the inner iterator to print their department names. (Each employee can be in multiple departments in this example.)

Example: Result Set Column in a Named Iterator

This example uses a column of type ResultSet in a named iterator. This example is essentially equivalent to the previous example, except it uses result sets inside an iterator instead of nested iterators.

Declaration:

#sql iterator NameDeptIter1 (String ename, ResultSet depts);

Executable code:

...
NameDeptIter1 iter;
...
#sql iter = { SELECT ename, cursor 
             (SELECT dname FROM dept WHERE deptno = emp.deptno) 
              AS depts FROM emp };

while (iter.next())
{
   System.out.println(iter.ename());
   ResultSet deptsRs = iter.depts();
   while (deptsRs.next())
   {
      String deptName = deptsRs.getString(1);
      System.out.println(deptName);
   }
   deptsRs.close();
}
iter.close();
...

Example: Named Iterator Column in a Positional Iterator

This example uses a positional iterator that has a column whose type is that of a previously defined named iterator (nested iterators). This uses the FETCH INTO syntax of positional iterators. This example is functionally equivalent to the previous two.

Note that because the outer iterator is a positional iterator, there does not have to be an alias to match a column name as there was when the outer iterator was a named iterator in the earlier example.

Declarations:

#sql iterator DNameIter (String dname);
#sql iterator NameDeptIter3 (String, DNameIter);

Executable code:

...
NameDeptIter3 iter;
...
#sql iter = { SELECT ename, cursor 
             (SELECT dname FROM dept WHERE deptno = emp.deptno) FROM emp };

while (true)
{
   String ename = null;
   DNameIter deptsIter = null;
   #sql { FETCH :iter INTO :ename, :deptsIter };
   if (iter.endFetch()) break;
   System.out.println(ename);
   while (deptsIter.next())
   {
      System.out.println(deptsIter.dname());
   }
   deptsIter.close();
}
iter.close();
...

---