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.
Table of Contents
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.