JDBC CachedRowSet Example

CachedRowSets are a type of ResultSet that can operate independently of the database. This means that once the data has been retrieved from the database, it can be cached in memory and disconnected from the database connection. This makes CachedRowSets an ideal choice for applications that require offline data manipulation, where you need to perform operations on the data without being connected to the database. In this post, we will learn about the detail of JDBC CachedRowSet with an example.

The benefits of using CachedRowSets in JDBC

1. Offline data manipulation: As mentioned earlier, CachedRowSets can be use to manipulate data offline. This is especially useful in scenarios where the application needs to perform multiple operations on the same set of data.
2. Improved performance: CachedRowSets can significantly improve application performance. Once the data has been cached in memory, subsequent operations can be performed on the cached data, which reduces the number of round trips to the database. This can result in significant performance improvements, especially when working with large datasets.
3. Better scalability: CachedRowSets can help improve application scalability by reducing the load on the database. By caching data in memory, the application can perform multiple operations on the cached data without requiring a new database connection each time.

Fetch data using CachedRowSet

package com.codersathi.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;

public class CachedRowSetDemo {
	public static void main(String[] args) throws Exception {
	      // Register the JDBC driver
	      Class.forName("com.mysql.cj.jdbc.Driver");

	      // Open a connection
	      Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/codersathi", "root", "");

	      // Create a CachedRowSet object
	      CachedRowSet rowset = RowSetProvider.newFactory().createCachedRowSet();

	      // Set the properties of the CachedRowSet
	      rowset.setCommand("SELECT * FROM employee");

	      // Execute the query and populate the CachedRowSet
	      rowset.execute(conn);

	      // Iterate through the results
	      while (rowset.next()) {
	         int id = rowset.getInt("id");
	         String name = rowset.getString("name");
	         double salary = rowset.getDouble("salary");
	         System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
	      }

	      // Close the connection
	      conn.close();
	   }
}

Output:

ID: 1, Name: Radha, Salary: 50000.0
ID: 2, Name: Krishnan, Salary: 50000.0

Update data using CachedRowSet

package com.codersathi.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;

public class CachedRowSetDemo {
	public static void main(String[] args) throws Exception {
		// Register the JDBC driver
		Class.forName("com.mysql.cj.jdbc.Driver");

		// Open a connection
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/codersathi", "root", "");
		// To update the RowSet we must set autoCommit false. The default value is true.
		// If we don't set it false then it thorows an SQLException saying: Can't call
		// commit when autocommit=true
		conn.setAutoCommit(false);
		// Create a CachedRowSet object
		CachedRowSet rowset = RowSetProvider.newFactory().createCachedRowSet();

		// Set the properties of the CachedRowSet
		rowset.setCommand("SELECT * FROM employee");

		// Execute the query and populate the CachedRowSet
		rowset.execute(conn);

		// Iterate through the results
		while (rowset.next()) {
			int id = rowset.getInt("id");
			if (id == 2) {
				rowset.updateDouble("salary", 60000);
				rowset.updateRow();
			}
		}
		rowset.acceptChanges();
		// Close the connection
		rowset.close();
	}
}

The output of the above code will be:

ID: 1, Name: Radha, Salary: 50000.0
ID: 2, Name: Krishnan, Salary: 60000.0

While doing an update we must set auto commit false to the connection object method. This is because the default behavior of this connection is to commit automatically. Hence, CachedRowSet does not allow us to update the database with the change we made in its object.

The code explains everything. Read the above code carefully to understand.

Thanks