Iterators
In a SQLJ program, the result set returned by a SQL query can be represented as an iterator object that is used to hold the data. An iterator object is an instance of an iterator class, and conceptually is similar to a PL/SQL cursor.
There are five steps you must perform to use an iterator to process rows returned by a SQL query:
- 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 iterators, where both the Java variable type and iterator column name have to be specified.
- Positional iterators, where only Java variable type for column retrieved from the database has to be specified.
Named Iterators: A named iterator declaration specifies both column accessor names and their Java types.
Let's demonstrate all five steps using the example, where you want to retrieve the Ename, Job, and HireDate columns of the Emp table for employees with salaries greater than 1,500.
Declare the iterator class:
#sql iterator EmpIteratorClass(String Ename, String Job, Timestamp HireDate);
The Java String class is used to represent the Ename and Job columns because it is compatible with the Oracle VARCHAR2 database type. The java.sql.Timestamp type is used for the HireDate column (DATE Oracle type) because the java.sql.Date type can hold only year, date, and time information, not hours, minutes, and seconds like java.sql.Timestamp.
Declare an iterator object from the iterator class:
EmpIteratorClass empIterator;
Populate the iterator object using a SQL SELECT statement. The following SQLJ statement populates the empIterator object with the contents of the Ename, Job, and HireDate columns from the Emp table:
int salary = 1500;
#sql empIterator = {
select Ename, Job, HireDate
from Emp
where Sal > :salary
};
We also declared the host variable salary, which is used in the WHERE clause to identify the rows to be retrieved from the Emp table. Keep in mind that the names of the database columns returned by the SQL query have to correspond to the iterator column names defined in Step 1.
Read the rows from the iterator object. Because the iterator object may contain multiple rows, we need to use a loop to access each row in turn—the same way we read rows from the PL/SQL cursor. The named iterator implements a method called next() that allows you to move through rows of the iterator object. In addition, SQLJ provides accessor methods to retrieve values of iterator columns.
The following code prints the name, job, and hire date in a loop:
while (empIterator.next()) {
System.out.println("Name: " + empIterator.Ename());
System.out.println("Job: " + empIterator.Job());
System.out.println("Hire Date:" +
empIterator.HireDate().toString());
}
Close the iterator object:
empIterator.close();
Listing 1 combines Steps 2–5 and shows the method listEmployees() with named iterator and empSalary parameter.
Positional Iterators: In contrast to named iterators, positional iterators specify only the number and types of columns, not their names. Column data can be accessed by position only, through the traditional (to PL/SQL) FETCH...INTO syntax. In addition to using the FETCH statement, you also need to use the positional iterator method endFetch() to detect the termination condition and get out of the loop. This condition must always be checked before fetched data is accessed.
Here are the same five steps now using the example of the positional iterator:
Declare the iterator class:
#sql iterator EmpIteratorClass(String, String, Timestamp);
Declare an iterator object from the iterator class. Also declare all host variables necessary for data fetching from the iterator object:
EmpIteratorClass empIterator;
String name = null;
String job = null;
Timestamp hireDate = null;
Populate the iterator object using a SQL SELECT statement:
int salary = 1500;
#sql empIterator = {
select Ename, Job, HireDate
from Emp
where Sal > :salary
};
Read the rows from the iterator object into host variables:
while (true) {
#sql { FETCH :empIterator INTO :name, :job, :hireDate };
if (empIterator.endFetch()) {
break;
}
System.out.println("Name: " + name);
System.out.println("Job: " + job);
System.out.println("Hire Date:" + hireDate().toString());
}
Close the iterator object:
empIterator.close();
Listing 2 combines Steps 2–5 and shows the method listEmployees() with positional iterator and empSalary parameter.
As you can see, you navigate through the positional iterator object using syntax very similar to the PL/SQL cursor syntax. Both named and positional iterators perform the same basic function: they hold the results of a SQL query that may return multiple rows. Which type of iterator to use is a matter or personal preference; from the performance point of view, they produce identical results.
Executable Statements
Executable SQLJ statements contain a static SQL operation within curly braces. There are two possible types of executable statements, determined by whether SQL returns a value or not.
Here is an example of an embedded SQL statement that does not return a value; it creates a composite index on the Ename and Sal columns in the Emp table:
#sql { create index EMP_ENAME_SAL on Emp(Ename, Sal) };
If an embedded SQL statement returns a value, you need to use a host variable to specify where the result should be placed. In this example you call the PL/SQL function getSalary, which returns the salary value of employee with Empno = 7900. You can use either the VALUES or SET operator to call a function; that is,
int salary;
int empNo = 7900;
#sql salary = { VALUES getSalary(:empNo} };
or
#sql { SET :salary = getSalary(:empNo} };
Host Expressions
Host variables, as you can see in the previous examples, allow a SQLJ program to exchange information between the database and the Java program. They are any Java variables that are declared in the Java program and referenced inside SQLJ statements. Host variables are embedded into the SQLJ statements with a colon prefix and called host expressions. These bind host variables to the SQLJ executable statement, and they may also include Java array elements, object attributes or Java functions. SQLJ takes care of moving data back and forth between the SQL and Java environments.
All standard JDBC types—such as Boolean, byte, short, int, String, byte[], double, float, java.sql.Date, etc.—are valid host expression types in SQLJ. In addition, Oracle's SQLJ translator supports the use of Oracle types such as ROWID, CLOB, BLOB, as well as Object and REF types.
In this article I talked about all main SQLJ types of objects that are necessary to start writing actual SQLJ code: connection contexts, named and positional iterators, executable statements and host expressions. In the next article, I will write a server-side SQLJ program, compile it, deploy it into Oracle JServer, and compare the performance with a PL/SQL counterpart.
Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning. Reach him at milrud@hotmail.com.
Do you have any interesting SQLJ experience you would like to share? Please feel free to contact Boris Milrud directly at 
