JDBC Scrollable ResultSet

Whenever we create an object of ResultSet by default, it allows us to retrieve in the 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.

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 we must use the following createStatement() method which is present in Connection interface.


                        public Statement createStatement(int Type, int Mode);

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

  1. 1). TYPE_FORWARD_ONLY -> 1
  2. 2). TYPE_SCROLL_INSENSITIVE -> 2
  3. 3). CONCUR_READ_ONLY -> 3

We can pass the above constants to ResultSet as below:


                        Statement st=con.createStatement ( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
                        ResultSet rs=st.executeQuery (“select * from empleyee”);

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

ResultSet interface provides us several methods to make an ResultSet as Scrollable ResultSet below is the list of methods available in ResultSet interface.

  1. public boolean next (); It returns true when rs contains next record otherwise false.
  2. public void beforeFirst (); It is used for making the ResultSet object to point to just before the first record (it is by default).
  3. public boolean isFirst (); It returns true when rs is pointing to first record otherwise false.
  4. public void first (); It is used to point the ResultSet object to first record.
  5. public boolean isBeforeFirst (); It returns true when rs pointing to before first record otherwise false.
  6. public boolean previous (); It returns true when rs contains previous record otherwise false.
  7. public void afterLast (); It is used for making the ResultSet object to point to just after the last record.
  8. public boolean isLast (); It returns true when rs is pointing to last record otherwise false.
  9. public void last (); It is used to point the ResultSet object to last record.
  10. public boolean isAfterLast (); It returns true when rs is pointing after last record otherwise false.
  11. public void absolute (int); It 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.
  12. public void relative (int); It is used for moving rs to that record either in forward direction or in backward direction with respect to current record.

JDBC Scrollable ResultSet Example :


                        package com.navabitsolutions.jdbc;

                        import java.sql.Connection;
                        import java.sql.DriverManager;
                        import java.sql.ResultSet;
                        import java.sql.Statement;

                        public class ScrollResultSet {

                            public static void main(String[] args) throws Exception {
                                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                                Connection con = DriverManager.getConnection(
                                        "jdbc:mysql://localhost:3306/navabitsolutions", "root",
                                        "123456");
                                Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
                                ResultSet rs = st.executeQuery("select * from student");
                                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("LAST TO FIRST RECORD...");
                                System.out.println(rs.getInt(1) + " -> " + rs.getString(2));
                                con.close();
                            }

                        }