Differences between ResultSet and RowSet

Differences between ResultSet and RowSet

ResultSet and RowSet are both interfaces in the Java JDBC API used to handle data retrieved from a database. While they share some similarities, they also have distinct differences in terms of functionality and usage.

Key Differences

1. ResultSet:

  • Definition: ResultSet is an interface that provides methods to iterate through and manipulate the result of a query executed on a database.
  • Connection Dependency: ResultSet is connected, meaning it remains connected to the database as long as it is being used. Changes in the database can be reflected in the ResultSet.
  • Functionality: Mainly used for reading data from a database. It does not support disconnected operations.
  • Usage: Suitable for scenarios where a connection to the database is maintained throughout the data processing.

2. RowSet

  • Definition: RowSet is a higher-level interface that extends ResultSet and adds additional capabilities such as support for disconnected operations.
  • Connection Independence: RowSet can operate in both connected and disconnected modes. Disconnected RowSet can retrieve data, operate on it, and then reconnect to the database to synchronize changes.
  • Functionality: Provides additional functionalities like JavaBeans component model support, event notification, and the ability to be used in a disconnected state.
  • Usage: Suitable for scenarios where data needs to be manipulated without a continuous connection to the database. Examples include GUI applications and distributed environments.

ResultSet and RowSet

Example

Let’s illustrate the differences with a Java example where we use both ResultSet and RowSet.

Database Table Creation
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 ResultSet and RowSet

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;

public class ResultSetVsRowSetExample {
    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!");

            // Using ResultSet to retrieve data
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");

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

            // Using JdbcRowSet to retrieve data
            JdbcRowSet jdbcRowSet = RowSetProvider.newFactory().createJdbcRowSet();
            jdbcRowSet.setUrl(jdbcURL);
            jdbcRowSet.setUsername(username);
            jdbcRowSet.setPassword(password);
            jdbcRowSet.setCommand("SELECT * FROM employees");
            jdbcRowSet.execute();

            System.out.println("\nUsing JdbcRowSet:");
            while (jdbcRowSet.next()) {
                int id = jdbcRowSet.getInt("id");
                String name = jdbcRowSet.getString("name");
                double salary = jdbcRowSet.getDouble("salary");
                System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
            }
            jdbcRowSet.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 ResultSet and RowSet

  • Using ResultSet:
    • Statement statement = connection.createStatement(); creates a Statement object.
    • ResultSet resultSet = statement.executeQuery(“SELECT * FROM employees”); executes the SELECT query and returns a ResultSet.
    • The ResultSet is used to iterate through and print the results.

  • Using JdbcRowSet:
    • JdbcRowSet jdbcRowSet = RowSetProvider.newFactory().createJdbcRowSet(); creates a JdbcRowSet object.
    • The JDBC URL, username, and password are set using jdbcRowSet.setUrl(), jdbcRowSet.setUsername(), and jdbcRowSet.setPassword().
    • The SELECT query is set using jdbcRowSet.setCommand() and executed with jdbcRowSet.execute().
    • The JdbcRowSet is used to iterate through and print the results.

Homepage

Readmore