Create Scrollable ResultSet Example JDBC

Create Scrollable ResultSet Example JDBC


Scrollable ResultSet: JDBC 2.0 API ResultSet methods are used where the records are read in the reverse order and also reading the data of a specific record.

Note: It is advised to go through the postings Scrollable ResultSet and ResultSet Types and Concurrency as they are used in this code. Just for a change, thin driver (Type-4) is used in this code.

Example on Scrollable ResultSet:

getRow() method returns the cursor position on which record it is lying.

The next() method advances the cursor to the next record in each iteration and similar previous() method moves the cursor backwards. The other methods that can be used are previous(), beforeFirst() and afterLast() etc. These methods are explained in Scrollable ResultSet.

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

First parameter can be any one the following three:

TYPE_SCROLL_SENSITIVE
TYPE_SCROLL_INSENSITIVE
TYPE_FORWARD_ONLY

Second parameter can be any one the following two:

CONCUR_READ_ONLY
CONCUR_UPDATABLE

The parameters are final static integer variables of ResultSet interface and passed to the createStatement() method of Connection interface. They are explained in ResultSet Types and Concurrency.

ResultSet Cursor Movement or how next() method executes

Let us see how the while loop returns all the records as it is required almost in each JDBC program.

JDBC maintains a cursor over the records. By default the cursor lies before the first record where actually no record exists. The next() method moves the cursor to the first record. Now the cursor is pointing to the first record. I call the first record as the current record (the record which the cursor points). The getXXX() methods read the fields of the current record, that is first record.

Scrollable ResultSet

After getXXX() methods read the current record data, the iteration continuous. Now the next() method moves the cursor over to the 2nd record. If the cursor points a record, the next() method returns a boolean value of true and the execution goes into the loop body. The getXXX() methods read the 2nd record data. Now the current record is 2nd record. That is, getXXX() methods read always the current record data. When the records get exhausted, the cursor comes after the last record where there is no record (other way, there is no current record). Now the next() method returns false and the loop terminates. It is similar to the Enumeration iteration.

ResultSet: absolute(int) vs realative(int)

Both methods take negative values also.
absolute() counts always from first record where as relative() counts from the current record. For example calling res.absolute(2) and immediately res.absolute(4), the cursor is placed on 4th record. Calling res.relative(2) and immediately res.relative(4), the cursor is placed on 6th record.

absolute(-3) places the cursor on the 3rd record but counting starts from the last record. relative(-3) moves the cursor backwards counting from the current record.

View All JDBC Examples

Leave a Reply

Your email address will not be published. Required fields are marked *