Scrollable ResultSet

Whenever we create an object of ResultSet by default, it allows us to retrieve in forward direction only and we cannot perform any modifications on ResultSet object. Therefore, by default the ResultSet object is non-scrollable and non-updatable ResultSet.

Scrollable ResultSet:

A scrollable ResultSet is one which allows us to retrieve the data in forward direction as well as backward direction but no updations are allowed. In order to make the non-scrollable ResultSet as scrollable ResultSet as scrollable ResultSet we must use the following createStatement which is present in Connection interface.

Type represents type of scrollability and Mode represents either read only or updatable. The value of Type and value of Mode are present in ResultSet interface as constant data members and they are:

int Type	int Mode
TYPE_FORWARD_ONLY - 1	CONCUR_READ_ONLY - 3
TYPE_SCROLL_INSENSITIVE - 2

For example:

Statement   st=con.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,  ResultSet.CONCUR_READ_ONLY);
ResultSet rs=st.executeQuery ("select * from student");

Whenever we create a ResultSet object, by default, constant-1 as a Type and constant-2 as a Mode will be assigned.

The following methods which are available in ResultSet interface which allows us to retrieve the data either in forward direction or in backward direction or in random retrieval:

public boolean next (); -   1
public void beforeFirst (); -   2
public boolean isFirst (); -   3
public void first (); -   4
public boolean isBeforeFirst (); - 5
public boolean previous (); -   6
public void afterLast (); -   7
public boolean isLast (); -   8
public void last (); -   9
public boolean isAfterLast (); -   10
public void absolute (int); -   11
public void relative (int); -   12

• Method-1 returns true when rs contains next record otherwise false.
• Method-2 is used for making the ResultSet object to point to just before the first record (it is by default).
• Method-3 returns true when rs is pointing to first record otherwise false.
• Method-4 is used to point the ResultSet object to first record.
• Method-5 returns true when rs pointing to before first record otherwise false.
• Method-6 returns true when rs contains previous record otherwise false.
• Method-7 is used for making the ResultSet object to point to just after the last record.
• Method-8 returns true when rs is pointing to last record otherwise false.
• Method-9 is used to point the ResultSet object to last record.
• Method-10 returns true when rs is pointing after last record otherwise false.
• Method-11 is used for moving the ResultSet object to a particular record either in forward direction or in backward direction with respect to first record and last record respectively. If int value is positive, rs move in forward direction to that with respect to first record. If int value is negative, rs move in backward direction to that with respect to last record.
• Method-12 is used for moving rs to that record either in forward direction or in backward direction with respect to current record.

Write a java program which illustrates the concept of scrollable ResultSet?

Answer:

import java.sql.*;

class ScrollResultSet {

    public static void main(String[] args) {
        try {
            Class.forName("Sun.jdbc.odbc.JdbcOdbcDriver");
            System.out.println("DRIVERS LOADED...");
            Connection con = DriverManager.getConnection("jdbc:odbc:oradsn", "scott", "tiger");
            System.out.println("CONNECTION ESTABLISHED...");
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = st.executeQuery("select * from emp");
            System.out.println("RECORDS IN THE TABLE...");
            while (rs.next()) {
                System.out.println(rs.getInt(1) + "	" + rs.getString(2));
            }
            rs.first();
            System.out.println("FIRST RECORD...");
            System.out.println(rs.getInt(1) + "	" + rs.getString(2));
            rs.absolute(3);
            System.out.println("THIRD RECORD...");
            System.out.println(rs.getInt(1) + "	" + rs.getString(2));
            rs.last();
            System.out.println("LAST RECORD...");
            System.out.println(rs.getInt(1) + "	" + rs.getString(2));
            rs.previous();
            rs.relative(-1);
            System.out.println("FIRST RECORD...");
            System.out.println(rs.getInt(1) + "	" + rs.getString(2));
            con.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }// main
};// ScrollResultSet