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