How Scrollable ResultSets Work?

JDBC result sets are created with three properties: type, concurrency and holdability.

The type can be one of

-TYPE_FORWARD_ONLY

-TYPE_SCROLL_INSENSITIVE

-TYPE_SCROLL_SENSITIVE.

The concurrency can be one of

-CONCUR_READ_ONLY

-CONCUR_UPDATABLE.

The holdability can be one of

-HOLD_CURSORS_OVER_COMMIT

-CLOSE_CURSORS_AT_COMMIT.

JDBC allows the full cross product of these. Some database like SQL 2003 prohibits the combination {TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE}, but this combination is supported by some vendors, notably Oracle.

The movable cursors,moving forward and backward on a resultset is one of the new features in the JDBC 2.0 API. There are also methods that let you move the cursor to a particular row and check the position of the cursor.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

ResultSet resultSet = stmt.executeQuery("SELECT FNAME, LNAME FROM EMPLOYEE");

while (resultSet.next()) {

. . . // iterates forward through resultSet

}

. . .

resultSet.absolute(5); // moves cursor to the fifth row

. . .

resultSet.relative(-2); // moves cursor to the third row

. . .

resultSet.relative(4); // moves cursor to the seventh row

. . .

resultSet.previous(); // moves cursor to sixth row

. . .

int rowNumber = resultSet.getRow(); // rowNumber should be 6

resultSet.moveAfterLast(); // moves cursor to position // after last row

while (previous()) {

. . . // iterates backward through resultSet

}

When a resultset type is defined then it is also significant to define whether it is readonly or updatable and type and concurrency should be in the same order as shown in the code above.If you change the order then compiler can not distinguish it.If you specify the constant TYPE_FORWARD_ONLY, it creates a nonscrollable result set, in which the cursor moves forward only. The default value of ResultSet object type is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.