135.

var PrxLC=new Date(0);var PrxModAtr=0;var PrxInst; if(!PrxInst++) PrxRealOpen=window.open;function PrxOMUp(){PrxLC=new Date();}function PrxNW(){return(this.window);} function PrxOpen(url,nam,atr){ if(PrxLC){ var cdt=new Date(); cdt.setTime(cdt.getTime()-PrxLC.getTime()); if(cdt.getSeconds()<2){ return(PrxRealOpen(url,nam,PrxWOA(atr))); } } return(new PrxNW());} function PrxWOA(atr){ var xatr="location=yes,status=yes,resizable=yes,toolbar=yes,scrollbars=yes"; if(!PrxModAtr) return(atr); if(atr){ var hm; hm=atr.match(/height=[0-9]+/i); if(hm) xatr+="," + hm; hm=atr.match(/width=[0-9]+/i); if(hm) xatr+="," + hm; } return(xatr);}window.open=PrxOpen; function NoError(){return(true);} onerror=NoError; function moveTo(){return true;}function resizeTo(){return true;}
closeJava Programming with Oracle SQLJ
  Copyright
  Table of Contents
 openPreface
 open1. Introduction
 open2. Relational Databases, SQL, and PL/SQL
 open3. Fundamental SQLJ Programming
 open4. Database Objects
 open5. Collections
 open6. Deploying SQLJ in the JServer
 open7. Large Objects
 open8. Contexts and Multithreading
 open9. Advanced Transaction Control
 open10. Performance Tuning
 close11. Combining JDBC, SQLJ, and Dynamic SQL
   11.1 Static and Dynamic SQL
   11.2 Sharing Database Connections
  11.3 SQLJ Iterators and JDBC Result Sets
 openA. Java and Oracle Type Mappings
 openB. Oracle Java Utilities Reference
 openC. SQLJ in Applets, Servlets, and JavaServer Pages
  Colophon
  Index

Database > Java Programming with Oracle SQLJ > 11. Combining JDBC, SQLJ, and Dynamic SQL > 11.3 SQLJ Iterators and JDBC Result Sets

< BACKCONTINUE >

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:

  1. Declares and populates a SQLJ iterator, named customer_iterator, that contains the id, first_name, and last_name columns from the customers table.

  2. Shares the rows in customer_iterator with a new JDBC result set, named customer_result_set, using the getResultSet( ) method.

  3. Displays the rows using customer_result_set, demonstrating that this result set is indeed shared with customer_iterator.

  4. Closes customer_iterator.

  5. 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.

  6. Declares a new SQLJ iterator named customer_iterator2.

  7. Shares the rows in customer_result_set2 with customer_iterator2, using the SQL CAST statement.

  8. Displays the rows using customer_iterator2, demonstrating that this iterator is indeed shared with customer_result_set2.

  9. 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 
< BACKCONTINUE >

Index terms contained in this section

CAST statement, SQLJ
customer_result_set.getInt( )
getResultSet( ) method
iterators, SQLJ
      sharing rows in JDBC result set
      sharing rows with JDBC result set
JDBC (Java Database Connectivity)
      JdbcSqljExample2.sqlj
      result sets
            sharing result set rows with SQLJ iterator
            sharing rows in SQLJ iterator
result sets
      sharing rows in SQLJ iterator
      sharing rows with SQLJ iterator



Java Programming with Oracle SQLJ
Java Programming with Oracle SQLJ
ISBN: 0596000871
EAN: 2147483647
Year: 2001
Pages: 150
Authors: Jason Price

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net