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.
Table of Contents
Additionally, ResultSet objects can be either read-only or updatable. The concurrency type of a ResultSet determines if it can be updated:
- CONCUR_READ_ONLY: The ResultSet cannot be updated. This is the default concurrency type.
- 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();.