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:
-
Declare the iterator class.
-
Declare an iterator object from the iterator class.
-
Populate the iterator object using a SELECT statement.
-
Read the rows from the iterator object.
-
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 which you read rows is different as well. In the next section, I'll describe the use of named iterators in detail. Following that, I'll point out those aspects of positional iterators that are different from named iterators.
3.5.2 Named Iterators
In this section, you will see how to use a named iterator to retrieve the id, name, description, and price columns from the products table. As I work through the example, I'll be following the five iterator steps previously outlined.
3.5.2.1 Step 1: Declare the iterator class
When declaring a named iterator class, both the Java variable type and the host variable name used to store each column retrieved from the database must be specified. The syntax for declaring a named iterator class is as follows:
#sql [modifiers] iterator class_name [implements_clause] [with_clause] (java_type column_name [, java_type column_name ...]);
The syntax elements are as follows:
- modifiers
-
The Java class modifiers: public, private, protected, and static.
- class_name
-
Specifies the name that you want to give the iterator class.
- implements_clause
-
Specifies the interfaces that the iterator class implements. This corresponds to the Java implements clause and is discussed later in this chapter.
- with_clause
-
Specifies a list of constants to define and initialize. The with clause is discussed later in this chapter.
- java_type
-
Specifies the Java type of the iterator column represented by column_name. The iterator columns in this syntax are used to store the values retrieved from the database. Each Java type must be compatible with its corresponding database type. For example, if a column uses the VARCHAR2 database type, then the matching iterator column should be a Java String. Appendix A shows which Java types are compatible with which Oracle database types.
- column_name
-
Specifies the name of the iterator column. When an iterator object is populated using a SELECT statement, the values retrieved from a database column are written to the iterator column with the same name as the database column. If you want the iterator column to have a different name from the database column, then you can use the AS clause in the SELECT statement to provide an alias for the database column. The column alias is then used to locate the corresponding iterator column.
 | The addition of the names of the iterator columns to the iterator class definition is what differentiates a named iterator class from a positional iterator class. |
|
The following SQLJ statement declares a named iterator class called ProductIteratorNamedClass:
#sql private static iterator ProductIteratorNamedClass( int id, double price, String name, String description );
As you can see, this iterator class uses the private static modifiers. This means that the iterator class is available only within the class in which it is declared and that the iterator class is a static inner class. For more information on this, see the book Learning Java (O'Reilly).
The Java int type in ProductIteratorNamedClass is used to represent the id column in the products table because the Java int type is compatible with the database NUMBER type used for the id column and because the id column contains only integers. The Java double type is used to represent the price column because double is compatible with the database NUMBER(10, 2) type used for the price column Java variables of type double can handle the decimal point in currency values. The Java String type is used to represent the name and description columns in the products table because String is compatible with the database VARCHAR2 type.
Because the iterator column names in a named iterator class are used to match the database columns in the SELECT statement, the order in which the iterator columns are declared in the iterator class doesn't have to be the same as the order of the database columns in the SELECT statement. For example, in ProductIteratorNamedClass, the price iterator column occupies the second position; as you will see in Step 3, the price database column occupies the fourth column position in the query's SELECT list. This works because the names are used to match the iterator columns with the database columns retrieved by the SELECT statement. The matching of the iterator column names and database column names is not case-sensitive, although it is considered good programming practice to use the same case.
3.5.2.2 Step 2: Declare an iterator object from the iterator class
Once you've declared an iterator class, you need to declare an iterator object of that class. The following Java statement declares an iterator object named product_iterator from ProductIteratorNamedClass:
ProductIteratorNamedClass product_iterator;
The product_iterator object is now ready to be populated with the results of a SQL SELECT statement.
3.5.2.3 Step 3: Populate the iterator object using a SELECT statement
Once you've declared the iterator object, you are ready to execute a SQL query to populate that object with data from the database. The following SQLJ statement populates product_iterator with the contents of the id, name, description, and price columns for all rows in the products table where the id column value is five or less:
#sql product_iterator = { SELECT id, name, description, price FROM products WHERE id <= 5 }; Notice that the names of the database columns returned by the SELECT statement correspond to the iterator column names defined for the iterator class. This is important. The types of the iterator columns must also be compatible with the database columns. The order in which database columns are listed or iterator columns are defined is immaterial.
3.5.2.4 Step 4: Read the rows from the iterator object
Having executed your query, your next task is to retrieve the data from the iterator. Since the iterator object may contain multiple rows, you need to use a loop to access each row in turn. Named iterator classes implement a method called next( ) (this method is provided by SQLJ; you don't have to write it) that allows you to step through each row returned by the query. In addition, SQLJ provides accessor methods through which the values of each iterator column stored in the iterator row may be read. The accessor methods are used by calling a function with the same name as the iterator column in the iterator class. With respect to the current example, product_iterator.name( ) returns a Java String value that contains the product name, while product_iterator.id( ) returns a Java int value that contains the product ID. The following while loop prints the product ID number, name, description, and price for the rows in product_iterator:
while (product_iterator.next( )) { // print the contents of the iterator row System.out.println("id = " + product_iterator.id( )); System.out.println("name = " + product_iterator.name( )); System.out.println("description = " + product_iterator.description( )); System.out.println("price = " + product_iterator.price( )); } // end of while loop 3.5.2.5 Step 5: Close the iterator object
Once processing of an iterator is complete, the iterator must be closed using the close( ) method. The following statement closes product_iterator:
product_iterator.close( );
The program FundamentalExample2.sqlj, which you'll see later in this chapter, implements the five steps just described to display the id, name, description, and price columns for the rows in the products table, using a named iterator. The next section describes the differences involved in using a positional iterator.
3.5.3 Positional Iterators
A positional iterator is one in which database columns are matched up with iterator columns based on their positions in a list rather than their names. There are two main differences in the way that a positional iterator is processed as compared to a named iterator:
The following sections describe these differences.
3.5.3.1 Declaring a positional iterator class
When declaring a positional iterator class, you specify only the Java types used to store the values retrieved from the database. You do not specify iterator column names to go along with those types. The syntax for a declaring a positional iterator class is:
#sql [modifiers] iterator class_name [implements_clause] [with_clause] (java_type [, java_type ...]);
The syntax elements are as follows:
- modifiers
-
The Java class modifiers: public, private, protected, and static.
- class_name
-
Specifies the name you want to give the iterator class.
- implements_clause
-
Specifies the interfaces that the iterator class implements. This corresponds to the Java implements clause, discussed later in this chapter.
- with_clause
-
Specifies a list of constants to define and initialize. The with clause is discussed later in this chapter.
- java_type
-
Specifies the Java type of the unnamed iterator column. The Java type must be compatible with the corresponding database type. When the iterator object is populated using a SELECT statement, the values retrieved from the database columns are written to the unnamed iterator columns in the same order in which they are specified in the SELECT statement.
The following SQLJ statement declares a positional iterator class named ProductIteratorPosClass:
#sql private static iterator ProductIteratorPosClass ( int, String, String, double );
As in the previous example involving named iterators, the Java int type is used to represent the id column in the products table, the String type is used to represent the name and description columns, and the double type is used to represent the price column. The order of these types must be the same as the order of the corresponding columns in the SELECT statement used to populate the iterator object. The positional iterator shown here will work with the SELECT statement used earlier in the section on named iterators.
3.5.3.2 Reading the rows from a positional iterator
To access the rows in a positional iterator, you must use the FETCH statement, rather than the next( ) method used to access a named iterator. Assume that a positional iterator object named product_iterator is declared from ProductIteratorPosClass, and that product_iterator is populated with the same five rows returned by the SELECT statement used previously with the named iterators. Since product_iterator contains multiple rows, you need to use a loop to access each row in turn. Within that loop, you use the FETCH statement to retrieve each single row from the iterator and store that row's values in host variables. The syntax for the FETCH statement is:
#sql { FETCH :iterator_name INTO :host_expression [, :host_expression ...] }; The syntax elements are as follows:
- iterator_name
-
Specifies the name of a positional iterator object.
- host_expression
-
Specifies a host expression that identifies a host variable in which one of the iterator column values returned by the FETCH statement will be stored. You must specify the same number of host expressions as there are iterator columns in the positional iterator. In addition, the order in which you list the host expressions must correspond to the order in which the iterator columns are defined. The default, and required, mode of any host expression used in the INTO clause is OUT. Any attempt to set the mode to IN or INOUT will result in a translation time error.
In addition to using the FETCH statement, you also use a method contained in the positional iterator called endFetch( ). The endFetch( ) method returns a Boolean true/false value. It returns true after the last row in the iterator object has been read (or before the first row in the iterator is read); otherwise, it returns false. The loop in the following example illustrates the use of the FETCH statement and the endFetch( ) method to read the rows that are contained in product_iterator:
boolean finished = false; // declare the host variables that will be used to store // the columns contained in the iterator object int id = 0; String name = null; String description = null; double price = 0.0; // step 4: read the rows from the iterator object while (!finished) { // each time the FETCH statement is used on the iterator object, // the next row in the iterator object will be read #sql { FETCH :product_iterator INTO :id, :name, :description, :price }; // after the last row has been read, the endFetch( ) method will // return the boolean true value (this indicates that all the rows // in the iterator object have been read) if (product_iterator.endFetch( )) { finished = true; } else { // display the values System.out.println("id = " + id); System.out.println("name = " + name); System.out.println("description = " + description); System.out.println("price = " + price); } } // end of while loop The program FundamentalExample3.sqlj (not printed in this book, but available for download from the web site) uses a positional iterator to display the id, name, description, and price columns for rows in the products table.
3.5.4 Which Type of Iterator to Use?
Both named and positional iterators perform the same basic function: they process the results of a SELECT statement that returns multiple rows. In your own programs, you can simply use the style of iterator you find easiest to understand. I personally prefer named iterators for the following reasons:
-
You don't have worry so much about the order of the columns in the SELECT statement because the iterator column names in a named iterator class are matched with the database column names. In a positional iterator, you must ensure that the ordering of the unnamed iterator columns in the iterator class is the same as for the database columns listed in the SELECT statement.
-
It is simpler to access the values stored in a named iterator because you use the accessor methods that contain the name of the column. For example, to access the value stored in the name column, you use the name( ) accessor method. In a positional iterator, you first have to fetch the values into host variables.
In the end, it comes down to your own personal preference. Use whichever iterator style works best for you.
3.5.5 The implements and with Clauses for Iterator Classes
There are two more parts of an iterator class definition that you should understand: the implements and with clauses. The SQLJ implements clause corresponds to the Java implements clause, and can be used to indicate the interfaces that an iterator class implements. As you will see later, this is used to declare scrollable iterators, which implement the sqlj.runtime.Scrollable interface. The with clause is used to initialize constants to be included in the generated class.
3.5.5.1 The implements clause
The syntax for the implements clause is:
implements interface_class [, interface_class ...]
The syntax element is as follows:
- interface_class
-
Specifies the interface class that the iterator class implements.
The following example declares a positional iterator class that implements an interface class named MyIteratorInterfaceClass:
#sql private static iterator MyIteratorClass implements MyInteratorInterfaceClass ( int, String );
3.5.5.2 The with clause
The syntax for the with clause is:
with ( constant_name=value [, constant_name=value ...] )
The syntax elements are as follows:
- constant_name
-
Specifies the name of a constant.
- value
-
Specifies the value that you want to assign to the named constant.
The following example declares a positional iterator class named MyIteratorClass, and defines a constant named my_constant that is set to 1.
#sql private static iterator MyIteratorClass with (my_constant=1) ( int, String );
The constant may then be accessed from an iterator object. The following example declares an iterator object and displays the value of the constant:
MyIteratorClass my_iterator; System.out.println(my_iterator.my_constant);
3.5.6 Scrollable Iterators
If you are using SQLJ Version 8.1.7 or higher, you can use scrollable iterators. A scrollable iterator gives you the freedom to access the rows stored in the iterator in non-sequential order. A non-scrollable iterator (the ones I have shown up to this point) only allows you to read each row in sequence, one followed by another. With a scrollable iterator, you can move backward to the previous row or jump randomly to another row.
In order for an iterator class to support scrollable functionality, it must implement the sqlj.runtime.Scrollable interface. You can declare scrollable iterator classes that are either named or positional; I discuss these possibilities in the following two sections.
3.5.6.1 Scrollable named iterators
The first of the following two statements declares an iterator class that implements the sqlj.runtime.Scrollable interface, indicating that the iterator is to be scrollable. The iterator is a named iterator because its definition includes iterator column names. The second statement then declares an iterator object based on that class:
#sql private static iterator NamedScrollableIterClass implements sqlj.runtime.Scrollable ( int id, String name ); NamedScrollableIterClass my_scrollable_named_iter;
Assume that my_scrollable_named_iter is then populated with the id and name column values from the rows in the products table. Because this named iterator class is scrollable, you can use the following methods in addition to the usual next( ) method:
- previous( )
-
Accesses the previous row. If you try to use this method when you are at the first row, you will get an error.
- first( )
-
Accesses the first row.
- last( )
-
Accesses the last row.
- absolute(row_number)
-
Accesses the row specified by row_number, which may be any expression that evaluates to a Java int value. The first row in the iterator has a row number of 1. If you try to use this method to access a nonexistent row, you will get an error.
- relative(relative_row_number)
-
Accesses a row relative to the current row. The row to access is specified by relative_row_number, which may be any expression that evaluates to a Java int value. The relative row number can be positive, specifying a row after the current row, or negative, specifying a row before the current row. If you try to use this method to access a nonexistent row, you will get an error.
- afterLast( )
-
Positions the iterator after the last row.
- beforeFirst( )
-
Positions the iterator before the first row.
The following example demonstrates the use of some of these scrollable iterator methods. The last( ) method is used to position the iterator at the last row. The rows are then accessed in reverse order, using the relative( ) method to scroll backward one row at a time:
my_scrollable_named_iter.last( ); while (my_scrollable_named_iter.relative(-1)) { // print the contents of the iterator row System.out.println("id = " + my_scrollable_named_iter.id( )); System.out.println("name = " + my_scrollable_named_iter.name( )); } // end of while loop In this example, I used relative(-1) to move backward one row at a time. The -1 that I passed as an argument causes the iterator to move back one row with respect to the current row. Because my increment was always -1, I could also have used the previous( ) method.
3.5.6.2 Scrollable positional iterators
You can also declare scrollable positional iterators. For example, the following statements declare a positional iterator class that implements the sqlj.runtime.Scrollable interface and declares an iterator object based on that class. Implementing the sqlj.runtime.Scrollable interface indicates that the iterator is to be scrollable:
#sql private static iterator PosScrollableIterClass implements sqlj.runtime.Scrollable ( int, String ); PosScrollableIterClass my_scrollable_pos_iter;
Assume that my_scrollable_pos_iter is then populated with the id and name column values from the rows in the products table. Because this positional iterator class is scrollable, you will be able to use the following forms of the FETCH clause:
- FETCH PREVIOUS or FETCH PRIOR
-
Accesses the previous row.
- >FETCH FIRST
-
Accesses the first row.
- FETCH LAST
-
Accesses the last row.
- FETCH ABSOLUTE :(row_number)
-
Accesses the row specified by row_number. The parameter row_number can be any expression that evaluates to a Java int value.
- FETCH RELATIVE :(relative_row_number)
-
Positions the iterator a specified number of rows forward or backward from the current row. The parameter relative_row_number can be any expression that evaluates to a Java int value.
There are no FETCH clauses corresponding to the beforeFirst( ) or afterLast( ) methods of a scrollable named iterator, as there is nothing to fetch from these positions. You can, however, use these methods with a positional iterator. The following example is similar to the one shown in the previous section except that it uses a positional iterator. The afterLast( ) method, which can be used with positional iterators, is used to position the iterator past the last row. The FETCH PREVIOUS statement is then used within a loop to process all the rows in the iterator.
boolean finished = false; // declare the host variables that will be used to store // the columns contained in the iterator object int id = 0; String name = null; // position the iterator after the last row my_scrollable_pos_iter.afterLast( ); // read the rows from the iterator object while (!finished) { #sql { FETCH PREVIOUS FROM :my_scrollable_pos_iter INTO :id, :name }; if (my_scrollable_pos_iter.isBeforeFirst( )) { finished = true; } else { // display the values System.out.println("id = " + id); System.out.println("name = " + name); } } // end of while loop The isBeforeFirst( ) method that you see in this code returns a Boolean true value if the iterator is positioned before the first row returned by the query. This is one of the other iterator methods described in the following section.
3.5.6.3 Other scrollable iterator methods
In addition to the methods described earlier in Section 3.5.6.1, the sqlj.runtime.Scrollable interface provides the following methods. They can be used with both scrollable named iterators and scrollable positional iterators:
- setFetchDirection(direction)
-
Indicates the primary direction in which rows will be processed in the iterator. The direction parameter is a Java int value and may be set to one of the following values:
- sqlj.runtime.ResultSetIterator.FETCH_FORWARD
-
Indicates that the primary FETCH direction will be forward. This is the default setting.
- sqlj.runtime.ResultSetIterator.FETCH_REVERSE
-
Indicates that the primary FETCH direction will be backward.
- sqlj.runtime.ResultSetIterator.FETCH_UNKNOWN
-
Indicates that you don't know the primary FETCH direction.
Even if you set the fetch direction, you can still access rows in any order: the direction parameter is just a hint to SQLJ. In fact, all the FETCH commands still work as before. Specifying a direction of FETCH_REVERSE does not cause FETCH to go backward; rather, it indicates to SQLJ that you intend to primarily issue FETCH PREVIOUS statements.
- getFetchDirection( )
-
Returns an int that specifies the primary direction in which rows are processed in the iterator.
- isFirst( )
-
Returns a Boolean true value if the iterator is processing the first row.
- isLast( )
-
Returns a Boolean true value if the iterator is processing the last row.
- isBeforeFirst( )
-
Returns a Boolean true value if the iterator is positioned before the first row.
- isAfterLast( )
-
Returns a Boolean true value if the iterator is positioned after the last row.
3.5.7 Scrollable Result Set Iterators
If you are using SQLJ Version 9i or above, you can use scrollable result set iterators . A scrollable result set iterator is similar to a scrollable positional iterator except that you don't have to declare an iterator class before declaring an iterator object. You can declare a scrollable result set iterator directly from a SELECT statement. Because the class declaration is omitted, you cannot specify names for the iterator columns. And as with a scrollable positional iterator, you can access the rows in a scrollable result set iterator using the various FETCH statements.
The following example uses a scrollable result set iterator to access and display the id and name column values from the products table. Notice there is no iterator class definition and that the rows are accessed using a FETCH CURRENT FROM statement:
// declare a scrollable result set iterator sqlj.runtime.ResultSetIterator my_result_set_iter; // populate the iterator with the id and name column values // from the products table #sql my_result_set_iter = { SELECT id, name FROM products }; int id = 0; String name = null; // access the rows in the iterator while (my_result_set_iter.next( )) { #sql { FETCH CURRENT FROM :my_result_set_iter INTO :id, :name }; System.out.println("id = " + id); System.out.println("name = " + name); } // close the iterator my_result_set_iter.close( ); You will notice that my_result_set_iter was not explicitly declared as implementing sqlj.runtime.Scrollable. This is because result set iterators implicitly support scrolling. Since the iterator is scrollable, you can also use the various methods described earlier for accessing the rows in non-sequential order. Scrollable result set iterators can make your SQLJ programs shorter, making them quite a neat feature of SQLJ 9i.
3.5.8 Example Program: FundamentalExample2.sqlj
This section contains a complete program that illustrates the use of named iterators. FundamentalExample2.sqlj (Example 3-2) uses a named iterator to display the id, name, description, and price columns for the rows in the products table where the id column value is less than or equal to 5. The program performs the following steps:
-
Declares a named iterator class for retrieving the id, name, description, and price columns from the products table.
-
Declares an iterator object from the named iterator class.
-
Populates the iterator object using a SELECT statement.
-
Uses a while loop to access each row in the iterator object and displays the values for each variable in the row.
-
Closes the iterator object.
Example 3-2. FundamentalExample2.sqlj
/* The program FundamentalExample2 illustrates the use of a named iterator to display the id, name, description and price columns from the products table. */ // import required packages import java.sql.*; import oracle.sqlj.runtime.Oracle; public class FundamentalExample2 { // step 1: declare the iterator class (remember that the iterator // column names must match the database column names in the SELECT // statement, but the order of the iterator columns doesn't have // to match the order of the columns in the SELECT statement; // in the example below, the price iterator column occupies // position 2 but in the SELECT statement listed later in the program // the price database column is last) #sql private static iterator ProductIteratorNamedClass( int id, double price, String name, String description ); public static void main(String [] args) throws SQLException { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // step 2: declare an iterator object from the iterator class ProductIteratorNamedClass product_iterator; // step 3: populate the iterator object using a SELECT statement #sql product_iterator = { SELECT id, name, description, price FROM products WHERE id <= 5 }; System.out.println("Product details:"); // step 4: read the rows from the iterator object while (product_iterator.next( )) { // print the contents of the iterator row variables System.out.println("id = " + product_iterator.id( )); System.out.println("name = " + product_iterator.name( )); System.out.println("description = " + product_iterator.description( )); System.out.println("price = " + product_iterator.price( )); } // end of while loop // step 5: close the iterator object product_iterator.close( ); Oracle.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) } The output from the program FundamentalExample2.sqlj is as follows:
Product details: id = 1 name = Beyond Understanding description = The frontiers of human knowledge price = 19.95 id = 2 name = Physics description = Fundamental understanding of the natural world price = 30.0 id = 3 name = Star Travelers description = The adventures of futuristic astronauts price = 25.99 id = 4 name = Seventh Sense description = Supernatural tale of the unknown price = 13.95 id = 5 name = Quantum Jump description = The physics of the quantum world price = 49.99
