February 23, 2011

How Scrollable ResultSets Work?

JDBC result sets are created with three properties: type, concurrency and holdability.
The type can be one of

The concurrency can be one of

The holdability can be one of

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.


joe said...

Your site appeared to be pretty beneficial. I have already been looking through your website a lot within the last day or two and it has received an area at my favorites. thanks.
Business Loan

joe said...

Thanks for sharing this post with us. It's really an amazing post. Keep posting the good work in future too.


Copyright 2007 All Right Reserved. shine-on design by Nurudin Jauhari. and Published on Free Templates