Java Programming with Oracle SQLJ

11.3. SQLJ Iterators and JDBC Result Sets

SQLJ iterators (covered in Chapter 3) are used to process SQL queries that may return multiple rows. A result set is the JDBC equivalent of an iterator, and is used for the same purpose. Once an iterator has been populated using a SQL query, the rows may be shared with a result set and accessed using JDBC statements. This is useful if you need to use a third-party library that contains methods that accept a JDBC result set as a parameter, for example.

The converse is also true: a result set may be populated using a JDBC statement and its rows shared with an iterator; the rows may then be accessed using SQLJ statements. This is useful if you need to use a dynamic SQL SELECT statement, but you wish to use the simpler SQLJ syntax to process the rows returned from the database.

11.3.1. Sharing Rows in an Iterator with a Result Set

The rows in an iterator may be shared with a result set by calling the getResultSet( ) method. The getResultSet( ) method returns a JDBC ResultSet object, through which the rows may be read using JDBC statements. The following example declares a named iterator class and an iterator; then declares and populates the iterator using the id, first_name, and last_name columns of the customers table; and finally calls the getResultSet( ) method to share the rows in the SQLJ iterator with a JDBC result set:

// declare a named iterator class #sql private static iterator CustomerIteratorClass ( int id, String first_name, ...