Retrieving Multiple Tuples
in SQLJ Using Iterators
In SQLJ, an iterator is a
type of object associated with a collection (set or multiset) of records in a
query result. The iterator is associated with the tuples and attrib-utes that appear
in a query result. There are two types of iterators:
A named iterator is associated with a query result by listing the
attribute names and types that appear
in the query result. The attribute names must correspond to appropriately declared Java program variables, as
shown in Figure 13.6.
A positional iterator lists
only the attribute types that appear
in the query result.
In both cases, the list should be in
the same order as the attributes that are listed in the SELECT clause of the query. However, looping over a query result is different
for the two types of iterators, as we shall see. First, we show an example of
using a named iterator in Figure
13.8, program segment J2A. Line 9 in Figure 13.8 shows how a named iterator type
Emp is declared. Notice that the
names of the attributes in a named iterator type must match the names of the
attributes in the SQL query result. Line 10 shows how an iterator object e of type Emp is created in the program and then associated with a query (lines 11
and 12).
When the iterator object is associated with a query (lines 11 and 12 in
Figure 13.8), the program fetches the query result from the database and sets
the iterator to a position before the
first row in the result of the query. This becomes the current row for the iterator. Subsequently, next operations are issued on the iterator object; each next
moves the iterator to the
next row
in the result of the query, making it the cur-rent
row. If the row exists, the operation retrieves the attribute values for that
row into the corresponding program variables. If no more rows exist, the next operation returns NULL, and can thus be used to control
the looping. Notice that the named iterator does not need an INTO clause, because the program variables corresponding to the retrieved
attributes are already specified when the iterator type is declared (line 9 in
Figure 13.8).
Figure 13.8
Program segment J2A, a Java
program segment that uses a named iterator to print employee information in a
particular department.
//Program
Segment J2A:
dname
= readEntry("Enter the Department Name: ") ;
try
{
#sql
{ select Dnumber into :dnumber
from
DEPARTMENT where Dname = :dname} ;
}
catch (SQLException se) {
System.out.println("Department
does not exist: " + dname) ;
Return
;
}
System.out.printline("Employee
information for Department: " + dname) ;
#sql
iterator Emp(String ssn, String fname, String minit, String lname, double
salary) ;
Emp
e = null ;
#sql
e = { select ssn, fname, minit, lname, salary
from
EMPLOYEE where Dno = :dnumber} ;
while
(e.next()) {
System.out.printline(e.ssn
+ " " + e.fname + " " + e.minit + " " + e.lname +
" " + e.salary) ;
}
;
e.close()
;
In Figure 13.8, the command (e.next()) in line 13 performs two functions: It gets the next tuple in the query
result and controls the while loop.
Once the program is done with processing the query result, the command e.close() (line 16) closes the iterator.
Next, consider the same example using positional iterators as shown in Figure 13.9 (program segment J2B).
Line 9 in Figure 13.9 shows how a positional
iterator type Emppos is
declared. The main difference between this and the named iterator is that
there are no attribute names (corresponding to
program variable names) in the positional iterator—only attribute types. This
can provide more flexibility, but makes the processing of the query result
slightly more complex. The attribute types must still must be compatible with
the attribute types in the SQL query result and in the same order. Line 10
shows how a positional iterator object
e of type Emppos is created in the program and then associated with a query (lines 11
and 12).
The positional iterator behaves in a manner that is more similar to
embedded SQL (see Section 13.2.2). A FETCH <iterator variable> INTO <program variables>
command is needed to get the next tuple in a query result. The first time fetch is executed, it gets the first tuple (line 13 in Figure 13.9). Line 16
gets the next tuple until no more tuples exist in the query result. To control
the loop, a positional iterator function e.endFetch() is used. This function is set to a value of TRUE when the iterator is initially associated with an SQL query (line 11),
and is set to FALSE
Figure 13.9
Program segment J2B, a Java
program segment that uses a positional iterator to print employee information
in a particular department.
//Program
Segment J2B:
dname
= readEntry("Enter the Department Name: ") ;
try
{
#sql
{ select Dnumber into :dnumber
from
DEPARTMENT where Dname = :dname} ;
}
catch (SQLException se) {
System.out.println("Department
does not exist: " + dname) ;
Return
;
}
System.out.printline("Employee
information for Department: " + dname) ;
#sql
iterator Emppos(String, String, String, String, double) ;
Emppos
e = null ;
#sql
e = { select ssn, fname, minit, lname, salary
from
EMPLOYEE where Dno = :dnumber} ;
#sql
{ fetch :e into :ssn, :fn, :mi, :ln, :sal} ;
while
(!e.endFetch()) {
System.out.printline(ssn
+ " " + fn + " " + mi + " " + ln + " "
+ sal) ;
#sql
{ fetch :e into :ssn, :fn, :mi, :ln, :sal} ;
}
;
e.close()
;
each time a fetch command returns a valid tuple from the query result.
It is set to TRUE
again when a fetch command does not find any more
tuples. Line 14 shows how the looping is controlled by
negation.
Related Topics
Privacy Policy, Terms and Conditions, DMCA Policy and Compliant
Copyright © 2018-2024 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.