What is a CachedRowSet in JDBC? Explain?

The CachedRowSet is the base implementation of disconnected row sets. It connects to the data source, reads data from it, disconnects with the data source and the processes the retrieved data, reconnects to the data source and writes the modifications.

Creating a CachedRowSet

You can create a Cached RowSet object using the createCachedRowSet() method of the RowSetFactory.

You can create a RowSetFactory object using the newfactory() method of the RowSetProvider method.

Create a CachedRowSet object using the above-mentioned methods as shown below −

//Creating the RowSet object
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet rowSet = factory.createCachedRowSet();

Connecting to the data source

After creating a RowSet object you need to connect it to the required DataSource.

You can connect to a DataSource by setting values to the properties such as username, password, url and, data source name as −

//Setting the URL
String mysqlUrl = "jdbc:mysql://localhost/SampleDB";
rowSet.setUrl(mysqlUrl);
//Setting the user name
rowSet.setUsername("root");
//Setting the password
rowSet.setPassword("password");

Preparing and executing the command statement

A cached row set has a command property to which you can pass the queries. Set the required query to this property using the setCommand() method.

rowSet.setCommand("select * from ProductSales");

You can execute the query that the RowSet object holds using the execute() method.

rowSet.execute();

Example

Assume we have a table named ProductSales in the database with the following content −

+----+-------------+--------------+--------------+--------------+-------+----------------+
| ID | ProductName | CustomerName | DispatchDate | DeliveryTime | Price | Location       |
+----+-------------+--------------+--------------+--------------+-------+----------------+
| 1  | Key-Board   | Raja         | 2019-09-01   | 05:30:00     | 7000  | Hyderabad      |
| 2  | Earphones   | Roja         | 2019-05-01   | 05:30:00     | 2000  | Vishakhapatnam |
| 3  | Mouse       | Puja         | 2019-03-01   | 05:29:59     | 3000  | Vijayawada     |
| 4  | Mobile      | Vanaja       | 2019-03-01   | 04:40:52     | 9000  | Chennai        |
| 5  | Headset     | Jalaja       | 2019-04-06   | 18:38:59     | 6000  | Goa            |
+----+-------------+--------------+--------------+--------------+-------+----------------+

Following example Retrieves the contents of the above-mentioned table into a CachedRowSet object and displays it.

Example

import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Time;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
public class CachedRowSetExample {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Creating the RowSet object
      RowSetFactory factory = RowSetProvider.newFactory();
      CachedRowSet rowSet = factory.createCachedRowSet();
      //Setting the URL
      String mysqlUrl = "jdbc:mysql://localhost/SampleDB";
      rowSet.setUrl(mysqlUrl);
      //Setting the user name
      rowSet.setUsername("root");
      //Setting the password
      rowSet.setPassword("password");
      //Setting the query/command
      rowSet.setCommand("select * from ProductSales");
      rowSet.execute();
      System.out.println("Contents of the row set");
      while(rowSet.next()) {
         System.out.print("ID: "+rowSet.getInt("ID")+", ");
         System.out.print("Product Name: "+rowSet.getString("ProductName")+", ");
         System.out.print("Customer Name: "+rowSet.getString("CustomerName")+", ");
         System.out.print("Dispatch Date: "+rowSet.getDate("DispatchDate")+", ");
         System.out.print("Delivery Time: "+rowSet.getTime("DeliveryTime"));
         System.out.print("Price: "+rowSet.getString("Price")+", ");
         System.out.print("Location: "+rowSet.getString("Location"));
         System.out.println("");
      }
   }
}

Output

Contents of the Rowset
ID: 1, Product Name: Key-Board,
Customer Name: Raja,
Dispatch Date: 2019-09-01,
Delivery Time: 05:30:00
Price: 7000,
Location: Hyderabad

ID: 2, Product Name: Earphones,
Customer Name: Roja,
Dispatch Date: 2019-05-01,
Delivery Time: 05:30:00
Price: 2000,
Location: Vishakhapatnam

ID: 3, Product Name: Mouse,
Customer Name: Puja,
Dispatch Date: 2019-03-01,
Delivery Time: 05:29:59
Price: 3000,
Location: Vijayawada

ID: 4,
Product Name: Mobile,
Customer Name: Vanaja,
Dispatch Date: 2019-03-01,
Delivery Time: 04:40:52Price: 9000,
Location: Chennai

ID: 5, Product Name: Headset,
Customer Name: Jalaja,
Dispatch Date: 2019-04-06,
Delivery Time: 18:38:59
Price: 6000,
Location: Goa