Java Programming with Oracle SQLJ

3.5. Queries That Return Multiple Rows

The SELECT statement is used to retrieve rows from a database source such as a table or a view. There are two cases to consider: a SELECT that returns one row, and a SELECT that returns multiple rows. For the single row case, you can use the SELECT INTO statement described earlier in this chapter. For the case involving multiple rows, you must use a SQLJ iterator.

3.5.1. SQLJ Iterators

An iterator is used to process multiple rows retrieved by a database query. From a conceptual point of view, a SQLJ iterator is similar to a PL/SQL cursor. There are five steps that you must perform to use an iterator to process rows returned by a SELECT statement:

1. Declare the iterator class.

2. Declare an iterator object from the iterator class.

3. Populate the iterator object using a SELECT statement.

4. Read the rows from the iterator object.

5. Close the iterator object.

There are two types of iterator classes:

named

Both the Java variable type and the iterator column name used to store each column retrieved from the database must be specified in the iterator class.

positional

Only the types of the Java variables used to store the columns retrieved from the database are specified in the iterator class. You do not specify column names.

For the most part, you follow the same process whether you are using a named iterator or a positional iterator. The differences lie in Steps 1 and 4. The two types of iterator classes are declared differently, and the method by ...