Menu

What is the JDBC Rowset?

What is the JDBC Rowset?

JDBC RowSet is an interface in the javax.sql package that extends the ResultSet interface. RowSets provide a higher-level and more flexible interface for handling tabular data than the traditional ResultSet. They can be either connected or disconnected from the database. Here are some key features and types of RowSet.

JDBC Rowset

1. Connected RowSet

   JdbcRowSet: A connected RowSet that maintains a constant connection to the database using JDBC. It is a thin wrapper around a ResultSet and is often used for simpler applications where continuous database connectivity is required.

2. Disconnected RowSet:

  • CachedRowSet: A disconnected RowSet that retrieves data from a database, stores it in memory, and disconnects from the database. It allows modifications to the data offline and later synchronizes the changes with the JDBC Rowset.
  • WebRowSet: A specialized CachedRowSet that supports XML serialization and deserialization, allowing easy data transfer between components in a web application.
  • FilteredRowSet: A CachedRowSet that supports filtering of data to include only those rows that meet certain criteria.
  • JoinRowSet: A CachedRowSet that allows joining of data from different RowSets.

Advantages of RowSet

  • Ease of Use: RowSets provide a simpler API and are easier to use compared to ResultSets.
  • Disconnected Operation: Disconnected RowSets like CachedRowSet allow applications to operate on data without maintaining a constant connection to the database, improving performance and scalability.
  • Flexibility: RowSets can be serialized, making it easy to transfer data over a network or between different parts of an application.
  • Event Handling: RowSets support event listeners, allowing applications to respond to changes in the data.

Let’s demonstrate the usage of JdbcRowSet and CachedRowSet with a Java example.

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

Example
sql
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    price DOUBLE
);

-- Insert some sample data
INSERT INTO products (name, price) VALUES ('Laptop', 800.00), ('Tablet', 300.00), ('Smartphone', 600.00);


Java Program Using JdbcRowSet and CachedRowSet

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

public class RowSetExample {
    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";

        // Example using JdbcRowSet
        try (JdbcRowSet jdbcRowSet = RowSetProvider.newFactory().createJdbcRowSet()) {
            jdbcRowSet.setUrl(jdbcURL);
            jdbcRowSet.setUsername(username);
            jdbcRowSet.setPassword(password);
            
            // Execute a query
            jdbcRowSet.setCommand("SELECT * FROM products");
            jdbcRowSet.execute();
            
            // Iterate over the result set
            System.out.println("JdbcRowSet:");
            while (jdbcRowSet.next()) {
                System.out.println("ID: " + jdbcRowSet.getInt("id") +
                                   ", Name: " + jdbcRowSet.getString("name") +
                                   ", Price: " + jdbcRowSet.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // Example using CachedRowSet
        try (CachedRowSet cachedRowSet = RowSetProvider.newFactory().createCachedRowSet()) {
            Connection connection = DriverManager.getConnection(jdbcURL, username, password);
            
            // Execute a query and populate the CachedRowSet
            cachedRowSet.setCommand("SELECT * FROM products");
            cachedRowSet.execute(connection);
            connection.close();
            
            // Modify data while disconnected
            while (cachedRowSet.next()) {
                double price = cachedRowSet.getDouble("price");
                cachedRowSet.updateDouble("price", price * 1.1); // Increase price by 10%
                cachedRowSet.updateRow();
            }
            
            // Reconnect to the database and synchronize changes
            connection = DriverManager.getConnection(jdbcURL, username, password);
            cachedRowSet.acceptChanges(connection);
            connection.close();
            
            // Print modified data
            System.out.println("CachedRowSet:");
            cachedRowSet.beforeFirst(); // Move cursor to the beginning
            while (cachedRowSet.next()) {
                System.out.println("ID: " + cachedRowSet.getInt("id") +
                                   ", Name: " + cachedRowSet.getString("name") +
                                   ", Price: " + cachedRowSet.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation of the JDBC Rowset

  • JdbcRowSet:
    • A JdbcRowSet is created using RowSetProvider.newFactory().createJdbcRowSet().
    • The JDBC URL, username, and password are set using jdbcRowSet.setUrl(jdbcURL), jdbcRowSet.setUsername(username), and jdbcRowSet.setPassword(password).
    • A query is executed using jdbcRowSet.setCommand(“SELECT * FROM products”) and jdbcRowSet.execute().
    • The result set is iterated and printed.

  • CachedRowSet:
    • A CachedRowSet is created using RowSetProvider.newFactory().createCachedRowSet().
    • A query is executed and the CachedRowSet is populated using cachedRowSet.setCommand(“SELECT * FROM products”) and cachedRowSet.execute(connection).
    • The database connection is closed.
    • Data is modified while disconnected, and the changes are synchronized with the database using cachedRowSet.acceptChanges(connection).
    • The modified data is printed.

Homepage

Readmore