How many types of Result Sets are there in JDBC What are they?

There are two types of result sets namely, forward only and, bidirectional.

Forward only ResultSet: The ResultSet object whose cursor moves only in one direction is known as forward only ResultSet. By default, JDBC result sets are forward-only result sets.

You can move the cursor of the forward only ResultSets using the next() method of the ResultSet interface. It moves the pointer to the next row from the current position. This method returns a boolean value. If there are no rows next to its current position it returns false, else it returns true.

Therefore, using this method in the while loop you can iterate the contents of the ResultSet object.

while(rs.next()){
}

Example

Assume we have a table named dataset with content as shown below:

+--------------+-----------+
| mobile_brand | unit_sale |
+--------------+-----------+
| Iphone       |      3000 |
| Samsung      |      4000 |
| Nokia        |      5000 |
| Vivo         |      1500 |
+--------------+-----------+

The following example retrieves all the records of the Dataset table and prints the results:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RetrievingData {
   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/TestDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Dataset");
      System.out.println("Contents of the table");
      while(rs.next()) {
         System.out.print("Brand: "+rs.getString("Mobile_Brand")+", ");
         System.out.print("Sale: "+rs.getString("Unit_Sale"));
         System.out.println("");
      }
   }
}

Output

Connection established......
Contents of the table
Brand: Iphone, Sale: 3000
Brand: Samsung, Sale: 4000
Brand: Nokia, Sale: 5000
Brand: Vivo, Sale: 1500

Bidirectional ResultSet: A bi-directional ResultSet object is the one whose cursor moves in both forward and backward directions.

The createStatement() method of the Connection interface has a variant which accepts two integer values representing the result set type and the concurrency type.

Statement createStatement(int resultSetType, int resultSetConcurrency)

To create a bi-directional result set you need to pass the type as ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE, along with the concurrency, to this method as:

//Creating a Statement object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Example

Following example demonstrates the creation of bi-directional ResultSet. Here we trying to created a bi-directional ResultSet object which retrieves the data from the table name dataset and, we are trying to print rows of the dataset table from last to first using the previous() method.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BidirectionalResultSet {
   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/TestDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Dataset");
      rs.afterLast();
      System.out.println("Contents of the table");
      while(rs.previous()) {
         System.out.print("Brand: "+rs.getString("Mobile_Brand")+", ");
         System.out.print("Sale: "+rs.getString("Unit_Sale"));
         System.out.println("");
      }
   }
}

Output

Connection established......
Contents of the table
Brand: Vivo, Sale: 1500
Brand: Nokia, Sale: 5000
Brand: Samsung, Sale: 4000
Brand: IPhone, Sale: 3000