How to update the column of a row in a CachedRowSet object in JDBC?

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.

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.

Updating a Particular column of a row

The updateXXX() methods of the CachedRowSet interface allows you to update column values of a particular row in a RowSet object.

Get the required row and update the desired column using the respective getXXX() methods for example, if you need to insert an integer value at 1st column and, String value at 2nd column you can do so using the updateInt() and updateString() methods as −

rowSet.updateInt(1, integerValue);
rowSet.updateString(2, "stringValue");

After modifying/updating the column values update these changes to the RowSet using the updateRow() method.

To make these changes reflect in the database accept the changes using the acceptChanges() method. To this method you need to pass the connection object using which you have established connection with the database.

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 JDBC program reduces the price of the keyboard by 5000.

import java.sql.Connection;
import java.sql.DriverManager;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
public class UpdatingCatchedRowSet {
   public static void main(String args[]) throws Exception {
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      con.setAutoCommit(false);
      //Creating the RowSet object
      RowSetFactory factory = RowSetProvider.newFactory();
      CachedRowSet rowSet = factory.createCachedRowSet();
      //Setting the query/command
      rowSet.setCommand("select * from ProductSales");
      //Executing the command
      rowSet.execute(con);
      System.out.println("Contents of the table: ");
      while(rowSet.next()) {
         if(rowSet.getString("ProductName").equals("Key-Board")) {
            rowSet.updateInt("Price", rowSet.getInt("Price")-5000 );
            rowSet.updateRow();
         }
      }
      rowSet.acceptChanges();
      rowSet.beforeFirst();
      System.out.println("Contents of the table after updating the row");
      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

Connection established......
Connection established......
Contents of the table:
Contents of the table after updating the row
ID: 1, Product Name: Key-Board, Customer Name: Raja, Dispatch Date: 2019-09-01, Delivery Time: 05:30:00Price: 2000, Location: Hyderabad
ID: 2, Product Name: Earphones, Customer Name: Roja, Dispatch Date: 2019-05-01, Delivery Time: 05:30:00Price: 2000, Location: Vishakhapatnam
ID: 3, Product Name: Mouse, Customer Name: Puja, Dispatch Date: 2019-03-01, Delivery Time: 05:29:59Price: 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:59Price: 6000, Location: Goa