The different types of ResultSet

The different types of ResultSet

An In JDBC, ResultSet objects are used to store the result of executing a query. There are different types of ResultSet based on how the data can be navigated and updated. The three main types of ResultSet are:

1. TYPE_FORWARD_ONLY

  • Description: The cursor can move only forward through the rows in the result set. This is the default type.
  • Usage: Suitable for scenarios where you need to iterate through the results once in a forward direction.
    • Example: Processing large data sets sequentially.

2. TYPE_SCROLL_INSENSITIVE

  • Description: The cursor can move forward and backward, and the result set is not sensitive to changes made by others to the underlying data while it is open.
  • Usage: Useful when you need to navigate the result set in both directions, but don’t need to see updates made to the database after the ResultSet is created.
    • Example: Reading data for display in a user interface where navigation is required.

3. TYPE_SCROLL_SENSITIVE

  • Description: The cursor can move forward and backward, and the result set is sensitive to changes made by others to the underlying data while it is open.
  • Usage: Useful when you need to see the changes made by others to the database after the ResultSet is created.
    • Example: Real-time data monitoring applications.

types of ResultSet

Additionally, ResultSet objects can be either read-only or updatable. The concurrency type of a ResultSet determines if it can be updated:

  1. CONCUR_READ_ONLY: The ResultSet cannot be updated. This is the default concurrency type.
  2. CONCUR_UPDATABLE: The ResultSet can be updated.

Example

Let’s illustrate the different types of ResultSet with a Java example where we create and navigate different types of ResultSet.

Database Table Creation
Before running the Java code, create a table in your database.

sql
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DOUBLE
);


Java Program Using Different Types of ResultSet
java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ResultSetTypesExample {
    public static void main(String[] args) {
        // JDBC URL, username, and password of MySQL server
        String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
        String username = "root";
        String password = "password";

        // JDBC variables for opening and managing connection
        Connection connection = null;

        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish a connection
            connection = DriverManager.getConnection(jdbcURL, username, password);
            System.out.println("Connected to the database!");

            // 1. TYPE_FORWARD_ONLY ResultSet
            String selectSQL = "SELECT * FROM employees";
            Statement forwardOnlyStatement = connection.createStatement(
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet forwardOnlyResultSet = forwardOnlyStatement.executeQuery(selectSQL);

            System.out.println("TYPE_FORWARD_ONLY ResultSet:");
            while (forwardOnlyResultSet.next()) {
                int id = forwardOnlyResultSet.getInt("id");
                String name = forwardOnlyResultSet.getString("name");
                double salary = forwardOnlyResultSet.getDouble("salary");
                System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
            }
            forwardOnlyResultSet.close();
            forwardOnlyStatement.close();

Example
// 2. TYPE_SCROLL_INSENSITIVE ResultSet
            Statement scrollInsensitiveStatement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet scrollInsensitiveResultSet = scrollInsensitiveStatement.executeQuery(selectSQL);

            System.out.println("\nTYPE_SCROLL_INSENSITIVE ResultSet:");
            scrollInsensitiveResultSet.afterLast();
            while (scrollInsensitiveResultSet.previous()) {
                int id = scrollInsensitiveResultSet.getInt("id");
                String name = scrollInsensitiveResultSet.getString("name");
                double salary = scrollInsensitiveResultSet.getDouble("salary");
                System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
            }
            scrollInsensitiveResultSet.close();
            scrollInsensitiveStatement.close();

Example

            
            // 3. TYPE_SCROLL_SENSITIVE ResultSet
            Statement scrollSensitiveStatement = connection.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet scrollSensitiveResultSet = scrollSensitiveStatement.executeQuery(selectSQL);

            System.out.println("\nTYPE_SCROLL_SENSITIVE ResultSet:");
            scrollSensitiveResultSet.beforeFirst();
            while (scrollSensitiveResultSet.next()) {
                int id = scrollSensitiveResultSet.getInt("id");
                String name = scrollSensitiveResultSet.getString("name");
                double salary = scrollSensitiveResultSet.getDouble("salary");
                System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
            }

            // Updating a row using TYPE_SCROLL_SENSITIVE ResultSet
            if (scrollSensitiveResultSet.absolute(1)) {
                scrollSensitiveResultSet.updateDouble("salary", 70000.0);
                scrollSensitiveResultSet.updateRow();
                System.out.println("Updated salary for ID 1");
            }
            scrollSensitiveResultSet.close();
            scrollSensitiveStatement.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.out.println("Disconnected from the database.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Explanation of the Example

TYPE_FORWARD_ONLY ResultSet:

  • Statement forwardOnlyStatement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); creates a Statement object with a TYPE_FORWARD_ONLY and CONCUR_READ_ONLY ResultSet.
  • ResultSet forwardOnlyResultSet = forwardOnlyStatement.executeQuery(selectSQL); executes the SELECT query and returns a forward-only ResultSet.

TYPE_SCROLL_INSENSITIVE ResultSet:

  • Statement scrollInsensitiveStatement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); creates a Statement object with a TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY ResultSet.
  • ResultSet scrollInsensitiveResultSet = scrollInsensitiveStatement.executeQuery(selectSQL); executes the SELECT query and returns a scroll-insensitive ResultSet.
  • The result set is navigated backward using scrollInsensitiveResultSet.previous().

TYPE_SCROLL_SENSITIVE ResultSet:

  • Statement scrollSensitiveStatement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); creates a Statement object with a TYPE_SCROLL_SENSITIVE and CONCUR_UPDATABLE ResultSet.
  • ResultSet scrollSensitiveResultSet = scrollSensitiveStatement.executeQuery(selectSQL); executes the SELECT query and returns a scroll-sensitive ResultSet.
  • The result set is navigated forward using scrollSensitiveResultSet.next().
  • The first row is updated using scrollSensitiveResultSet.updateDouble(“salary”, 70000.0); and scrollSensitiveResultSet.updateRow();.

Homepage

Readmore