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:

import java.sql.*;
public class JDBCMethods
{
  public static void main(String args[]) throws Exception
  {                                      //database connections
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

    ResultSet res = stmt.executeQuery("select * from Employee");
                                         // by default the cursor is before first record
    System.out.println("To Print All The Records");
    while(res.next())                    // prints records top to bottom
    {
      System.out.println(res.getString(1)+" "+res.getString(2)+" "+res.getString(3));
    }
                                         // now the cursor is after the last record
    System.out.println("\nTo print the first record after moving to first position with first(): ");
    res.first();                        // record numbering starts from 1, cursor pointing to the first record
    System.out.println("Cursor position: "+res.getRow()); // prints 1.
    System.out.println(res.getString(1)+" "+res.getString(2)+" "+res.getString(3));

    System.out.println("\nTo print 3rd record data using absolute(3)");
    res.absolute(3);                     // cursor is on 3rd record
    System.out.println("Record pointer position: "+res.getRow());
    System.out.println(res.getString(1)+" "+res.getString(2)+" "+res.getString(3));

    System.out.println("\nTo print the last record after moving to last record using last()");
    res.last();                          // cursor is on the last record
    System.out.println("Record pointer position: "+res.getRow());
    System.out.println(res.getString(1)+" "+res.getString(2)+" "+res.getString(3));

    System.out.println("To print backwards:");
    res.afterLast();                     // cursor is after last record
    while(res.previous())                // cursor moves backwards         
    {
      System.out.println(res.getString(1)+"  " + res.getString(2) +"  " + res.getString(3));
    }                                    // records are printed from bottom to top

    res.close();
    stmt.close();
    con.close();
  }
}

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.

          
while(res.next())
{
 System.out.println(res.getInt(1) + "\t" + res.getString(2) + "\t" + res.getDouble(3));
}

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.

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