Tutorialspoint

1 Answer
Rishi Raj

In JDBC there are two scrollable ResultSets namely, scrollable sensitive and, scrollable insensitive.

In the TYPE_SCROLL_INSENSITIVE ResultSet the cursor moves in forward or backward directions. This type of ResultSet is insensitive to the changes that are made in the database i.e. the modifications done in the database are not reflected in the ResultSet.

Which means if we have established a connection with a database using JDBC program and retrieved a ResultSet holding all the records in a table named SampleTable. Meanwhile if we have added some more records to the table (after retrieving getting the ResultSet), these recent changes will not be reflected in the ResultSet object we previously obtained.

In the TYPE_SCROLL_INSENSITIVE ResultSet the cursor moves in forward or backward directions. This type of ResultSet is sensitive to the changes that are made in the database i.e. the modifications done in the database are reflected in the ResultSet.

Which means if we have established a connection with a database using JDBC program and retrieved a ResultSet holding all the records in a table named SampleTable. Meanwhile if we have added some more records to the table (after retrieving the ResultSet), these recent changes will be reflected in the ResultSet object we previously obtained.

Either scrollable sensitive or, scrollable insensitive both result sets are bi-directional i.e. you can move them in both forward and backward directions.

You can create a Statement object that returns a Scrollable result set, using the createStatement() method as −

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

You can traverse through the contents of a ResultSet object in forward direction using the next() method and, you can traverse its contents in reverse direction using forward() method.

Let us create a table with name cricketers_data in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in cricketers_data table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

We have connected to the database Using a JDBC program and Retrieved the records of the above table into a ResultSet object and traversed through it in forward and reverse directions using the next() and previous() methods.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ScrollableResultset {
   public static void main(String args[])throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Query to retrieve the contents of the cricketers_data table
      String query = "select * from cricketers_Data";
      //Executing the query
      ResultSet rs = stmt.executeQuery(query);
      //Contents of the ResultSet in forward direction
      System.out.println("Contents of the ResultSet in forward direction");
      while (rs.next()) {
         System.out.print("id: "+rs.getInt(1)+", ");
         System.out.print("First name: "+rs.getString(2)+", ");
         System.out.print("Last name: "+rs.getString(3)+", ");
         System.out.print("Year of birth: "+rs.getDate(4)+", ");
         System.out.print("Place of birth: "+rs.getString(5)+", ");
         System.out.print("Country: "+rs.getString(6));
         System.out.println(" ");
      }
      System.out.println(" ");
      //Contents of the ResultSet in reverse direction
      System.out.println("Contents of the ResultSet in reverse direction");
      rs.afterLast();
      while (rs.previous()) {
         System.out.print("id: "+rs.getInt(1)+", ");
         System.out.print("First name: "+rs.getString(2)+", ");
         System.out.print("Last name: "+rs.getString(3)+", ");
         System.out.print("Year of birth: "+rs.getDate(4)+", ");
         System.out.print("Place of birth: "+rs.getString(5)+", ");
         System.out.print("Country: "+rs.getString(6));
         System.out.println(" ");
      }
   }
}

Output

Connection established......
Contents of the ResultSet in forward direction
id: 1, First name: Shikhar, Last name: Dhawan, Year of birth: 1981-12-05, Place of birth: Delhi, Country: India
id: 2, First name: Jonathan, Last name: Trott, Year of birth: 1981-04-22, Place of birth: CapeTown, Country: SouthAfrica
id: 3, First name: Kumara, Last name: Sangakkara, Year of birth: 1977-10-27, Place of birth: Matale, Country: Srilanka
id: 4, First name: Virat, Last name: Kohli, Year of birth: 1988-11-05, Place of birth: Mumbai, Country: India
id: 5, First name: Rohit, Last name: Sharma, Year of birth: 1987-04-30, Place of birth: Nagpur, Country: India
id: 6, First name: Ravindra, Last name: Jadeja, Year of birth: 1988-12-06, Place of birth: Nagpur, Country: India
id: 7, First name: James, Last name: Anderson, Year of birth: 1982-06-30, Place of birth: Burnley, Country: England
Contents of the ResultSet in reverse direction
id: 7, First name: James, Last name: Anderson, Year of birth: 1982-06-30, Place of birth: Burnley, Country: England
id: 6, First name: Ravindra, Last name: Jadeja, Year of birth: 1988-12-06, Place of birth: Nagpur, Country: India
id: 5, First name: Rohit, Last name: Sharma, Year of birth: 1987-04-30, Place of birth: Nagpur, Country: India
id: 4, First name: Virat, Last name: Kohli, Year of birth: 1988-11-05, Place of birth: Mumbai, Country: India
id: 3, First name: Kumara, Last name: Sangakkara, Year of birth: 1977-10-27, Place of birth: Matale, Country: Srilanka
id: 2, First name: Jonathan, Last name: Trott, Year of birth: 1981-04-22, Place of birth: CapeTown, Country: SouthAfrica
id: 1, First name: Shikhar, Last name: Dhawan, Year of birth: 1981-12-05, Place of birth: Delhi, Country: India

Advertisements

We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy.