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, String last_name ); ... // declare a SQLJ iterator CustomerIteratorClass customer_iterator; // populate the SQLJ iterator #sql customer_iterator = { SELECT id, first_name, last_name FROM customers ORDER BY id }; // declare a JDBC result set, and share the rows in the SQLJ iterator // with the result set using a call to getResultSet( ) ResultSet customer_result_set = customer_iterator.getResultSet( );
JDBC statements may now be used to read the rows via the ResultSet object. The following example uses a loop to access and display the id, first_name, and last_name columns using the result set customer_result_set:
while (customer_result_set.next( )) { System.out.println("id = " + customer_result_set.getInt(1)); System.out.println("first name = " + customer_result_set.getString(2)); System.out.println("last name = " + customer_result_set.getString(3)); } // end of while loop
The call to customer_result_set.getInt(1) returns the integer value of the first column in the result set. This is the value of the id column. The calls to customer_result_set.getString(2) and customer_result_set.getString(3) return the values of the first_name and last_name columns, respectively.
Once you are finished processing the result set, you should close the iterator. Closing the iterator also causes the result set to be closed. The following example closes customer_iterator, causing the underlying result set named customer_result_set to be closed as well:
customer_iterator.close( );
11.3.2 Sharing Rows in a Result Set with an Iterator
The rows in a result set may be shared with an iterator using the SQLJ CAST statement, and then read through the iterator using SQLJ statements. The syntax for a SQLJ executable statement containing the CAST statement is:
#sql iterator_name = { CAST :result_set_name };
The syntax elements are as follows:
- iterator_name
-
The name of the SQLJ iterator.
- result_set_name
-
The name of the JDBC result set.
Using the CAST statement, you can share rows from a JDBC result set with either a positional or named iterator.
11.3.2.1 Positional iterators
If you choose to share a result set with a positional iterator, then the iterator must contain the same number of iterator columns as there are columns in the result set. In addition, the iterator column Java types must be compatible with the database columns in the original SELECT statement used to populate the result set. For example, if a result set is used to store the id, first_name, and last_name columns from the customers table, then the positional iterator class must contain the following three Java types:
- int
-
To hold the id column.
- String
-
To hold the first_name column.
- String
-
To hold the last_name column.
The following example declares a positional iterator class containing these three types:
#sql public static iterator CustomerIteratorClass ( int, String, String );
11.3.2.2 Named iterators
If you choose to share a result set with a named iterator, then the iterator column names must be the same as the column names in the original SQL statement used to populate the result set, and the iterator column Java types must be compatible with the database column types. For example, if a result set selects the id, first_name, and last_name columns from the customers table, then the named iterator class must contain the following three iterator columns:
-
int id
-
String first_name
-
String last_name
The declaration in the following statement creates a named iterator class containing these three iterator columns:
#sql public static iterator CustomerIteratorClass ( int id, String first_name, String last_name );
The following example creates and populates a JDBC result set named customer_result_set using the id, first_name, and last_name columns of the customers table:
// create a string containing the SELECT statement String sql_statement = "SELECT id, first_name, last_name FROM customers " + "ORDER BY id"; // create a JDBC statement Statement statement = jdbc_conn.createStatement( ); // create and populate a JDBC result set with the retrieved rows from // the prepared statement ResultSet customer_result_set = statement.executeQuery(sql_statement);
After creating a result set, the next step is to share the rows in the result set with an iterator, which may then be read using SQLJ statements.
11.3.2.3 Sharing and reading the rows
The following example declares an iterator named customer_iterator, and shares the rows from a result set named customer_result_set with this iterator using the SQL CAST statement:
// declare a SQLJ iterator CustomerIteratorClass customer_iterator; // share the rows in the JDBC result with the SQLJ iterator using // the SQLJ CAST statement #sql customer_iterator = { CAST :customer_result_set };
SQLJ statements may then be used to read the rows using the iterator. For example, the following code uses a loop to access customer_iterator and display the id, first_name, and last_name columns:
while (customer_iterator.next( )) { System.out.println("id = " + customer_iterator.id( )); System.out.println("first_name = " + customer_iterator.first_name( )); System.out.println("last_name = " + customer_iterator.last_name( )); } // end of while loop
Once you are finished with the iterator, you should close it. Closing the iterator also closes the result set. The following example closes customer_iterator, also closing the underlying result set customer_result_set:
customer_iterator.close( );
11.3.3 Example Program: JdbcSqljExample2.sqlj
This section contains a complete program, JdbcSqljExample2.sqlj (Example 11-3), that illustrates the sharing of rows between SQLJ iterators and JDBC result sets. The program performs the following steps:
-
Declares and populates a SQLJ iterator, named customer_iterator, that contains the id, first_name, and last_name columns from the customers table.
-
Shares the rows in customer_iterator with a new JDBC result set, named customer_result_set, using the getResultSet( ) method.
-
Displays the rows using customer_result_set, demonstrating that this result set is indeed shared with customer_iterator.
-
Closes customer_iterator.
-
Declares and populates a new JDBC result set, named customer_result_set2, using the same columns from the customers table as were used in Step 1.
-
Declares a new SQLJ iterator named customer_iterator2.
-
Shares the rows in customer_result_set2 with customer_iterator2, using the SQL CAST statement.
-
Displays the rows using customer_iterator2, demonstrating that this iterator is indeed shared with customer_result_set2.
-
Closes customer_iterator2.
Example 11-3. JdbcSqljExample2.sqlj
/* The program JdbcSqljExample2.sqlj illustrates the use of sharing rows between SQLJ iterators and JDBC result sets. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ref.DefaultContext; public class JdbcSqljExample2 { // declare a named iterator class #sql public static iterator CustomerIteratorClass ( int id, String first_name, String last_name ); public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // declare a SQLJ iterator CustomerIteratorClass customer_iterator; // populate the iterator #sql customer_iterator = { SELECT id, first_name, last_name FROM customers ORDER BY id }; // declare a JDBC result set, and share the rows in the SQLJ iterator // with the result set using a call to getResultSet( ) ResultSet customer_result_set = customer_iterator.getResultSet( ); System.out.println("Customer rows using JDBC result set:"); while (customer_result_set.next( )) { System.out.println("id = " + customer_result_set.getInt(1)); System.out.println("first name = " + customer_result_set.getString(2)); System.out.println("last name = " + customer_result_set.getString(3)); } // end of while loop // close the iterator customer_iterator.close( ); // create a JDBC connection Connection jdbc_conn = DefaultContext.getDefaultContext().getConnection( ); // create a string containing the SELECT statement String sql_statement = "SELECT id, first_name, last_name FROM " + "customers ORDER BY id"; // create a JDBC statement Statement statement = jdbc_conn.createStatement( ); // declare and populate a JDBC result set with the retrieved // rows from the prepared statement ResultSet customer_result_set2 = statement.executeQuery(sql_statement); // declare a SQLJ iterator CustomerIteratorClass customer_iterator2; // share the rows in the JDBC result with the SQLJ iterator using // the SQL CAST statement #sql customer_iterator2 = { CAST :customer_result_set2 }; System.out.println("Customer rows using SQLJ iterator:"); while (customer_iterator2.next( )) { System.out.println("id = " + customer_iterator2.id( )); System.out.println("first_name = " + customer_iterator2.first_name( )); System.out.println("last_name = " + customer_iterator2.last_name( )); } // end of while loop // close the iterator customer_iterator2.close( ); jdbc_conn.close( ); Oracle.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) }
The output from the program is as follows:
Customer rows using JDBC result set: id = 1 first name = John last name = Smith id = 2 first name = Cynthia last name = Stevens id = 3 first name = Steve last name = Seymour id = 4 first name = Gail last name = Williams id = 5 first name = Doreen last name = Heyson Customer rows using SQLJ iterator: id = 1 first_name = John last_name = Smith id = 2 first_name = Cynthia last_name = Stevens id = 3 first_name = Steve last_name = Seymour id = 4 first_name = Gail last_name = Williams id = 5 first_name = Doreen last_name = Heyson