CachedRowSet Example

by

In this post, we will be looking at the example code of the CachedRowSet available in JDBC.

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

Related Posts