6.1.3 Cursor Movement Examples

As stated in the previous section, the standard cursor movement for forward only result sets is to use the method next to iterate through each row of a result set once from top to bottom. With scrollable result sets, it is possible to revisit a row or to iterate through the result set multiple times. This is possible because the cursor can be moved before the first row at any time (with the method beforeFirst). The cursor can begin another iteration through the result set with the method next. The following example positions the cursor before the first row and then iterates forward through the contents of the result set. The methods getString and getFloat retrieve the column values for each row until there are no more rows, at which time the method next returns the value false.

rs.beforeFirst();
while (rs.next()) {
System.out.println(rs.getString("EMP_NO") +
" " + rs.getFloat("SALARY");
}

It is also possible to iterate through a result set backwards, as is shown in the next example. The cursor is first moved to the very end of the result set (with the method afterLast), and then the method previous is invoked within a while loop to iterate through the contents of the result set by moving to the previous row with each iteration. The method previous returns false when there are no more rows, so the loop ends after all the rows have been visited.

rs.afterLast();
while (rs.previous()) {
System.out.println(rs.getString("EMP_NO") +
" " + rs.getFloat("SALARY");
}

The interface ResultSet offers still other ways to iterate through the rows of a scrollable result set. Care should be taken, however, to avoid incorrect alternatives such as the one illustrated in the following example:

// incorrect!
while (!rs.isAfterLast()) {
rs.relative(1);
System.out.println(
rs.getString("EMP_NO") + " " + rs.getFloat("SALARY"));
}

This example attempts to iterate forward through a scrollable result set and is incorrect for several reasons. One error is that if ResultSet.isAfterLast is called when the result set is empty, it will return a value of false since there is no last row. The loop body will be executed, which is not what is wanted. An additional problem occurs when the cursor is positioned before the first row of a result set that contains data. In this case, calling rs.relative(1) is erroneous because there is no current row. The method relative moves the cursor the specified number of rows from the current row, and it must be invoked only while the cursor is on the current row.

The following code fragment fixes the problems in the previous example. Here a call to the method ResultSet.first is used to distinguish the case of an empty result set from one that contains data. Because ResultSet.isAfterLast is called only when the result set is non-empty, the loop control works correctly. Since ResultSet.first method (ResultSet interface)>first initially positions the cursor on the first row, the method ResultSet.relative(1) steps through the rows of the result set as expected.

if (rs.first()) {
while (!rs.isAfterLast()) {
System.out.println(
rs.getString("EMP_NO") + " " + rs.getFloat("SALARY"));
rs.relative(1);
}
}