Working with Databases in Java - Beginning Java Programming: The Object-Oriented Approach - page 323

#sql {select EmployeeID, Name
into :empID,:ename
from EmployeeSchema.Employee
where DNR = :dnr };
SQLJ has two types of cursors: iterators and positional iterators. They are always defined at the start
of the Java class file and not within a class. Remember the Employee table defined earlier:
CREATE TABLE 'EmployeeSchema'.'Employee' (
'EmployeeID' INT NOT NULL,
'Name' VARCHAR(45) NULL,
'Gender' VARCHAR(45) NULL,
'DNR' INT NULL,
PRIMARY KEY ('EmployeeID'),
INDEX 'DNRForeign_idx' ('DNR' ASC),
CONSTRAINT 'DNRForeign'
FOREIGN KEY ('DNR')
REFERENCES 'EmployeeSchema'.'Department' ('DNR')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
Let's now define a named iterator as follows:
#sql iterator EmpNameIter (int EmployeeID, String Name, int DNR);
class SQLJEx1 {
void AccessEmpData() throws SQLException {
EmpNameIter MyNamedIter;
#sql MyNamedItter =
{SELECT Name, EmployeeID, DNR FROM EmployeeSchema.Employee };
}
}
Note that the iterator column names match the table column names. Also observe that the order of
the columns in the SQL statement should not match the order in the iterator statement or table since
data is matched by name and not by position, which is very convenient. You can then access the col-
umns by their name, as follows:
while (MyNamedItter.next()) {
System.out.println(MyNamedItter.EmployeeID +
" " + MyNamedItter.Name + " " + MyNamedItter.DNR));
}
The method next retrieves the next row of the iterator and then returns true unless there are no
more rows to be retrieved, in which case it returns false .
A positional iterator defines only the data type of each column and not its name. An example is as
follows:
#sql iterator EmpPosIter (int, String, in);
class SQLJEx2 {
void AccessEmpData () throws SQLException {
EmpPosIter MyPosIter;
#sql MyPosItter =