Using an updatable result set enables our program to update record in the database from the ResultSet object. The operation on the ResultSet object can be updated, inserted or deleted. With this mechanism, we can update a database without executing a query.

In the example below we have a product table with the id, code, name, and price columns. In the first step after we load the result set, we update the product name of the first record. Then we move to the next record and delete it. At last, we insert a new record to a database.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdatableResultSetDemo {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Create an updatable result set. It means that instead of
            // using a separate sql command to update the data, we can
            // update it directly in the result set object.
            //
            // What makes it updatable is because, when creating the
            // statement, we ask the connection object to create a statement
            // with CONCUR_UPDATABLE. The updatable doesn't need to be
            // TYPE_SCROLL_SENSITIVE, but adding this parameter to the
            // statement enables us to go back and forth to update the data.
            Statement statement = connection.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            String query = "SELECT id, code, name, price FROM product";
            ResultSet rs = statement.executeQuery(query);

            System.out.println("id\tcode\tname\tprice");

            while (rs.next()) {
                System.out.println(rs.getLong("id") + "\t"
                                   + rs.getString("code") + "\t"
                                   + rs.getString("name") + "\t"
                                   + rs.getDouble("price"));
            }

            // Move to the first row and update the result set data. After
            // we update the row value, we call the updateRow() method to
            // update the data in the database.
            rs.first();
            rs.updateString("name", "UML Distilled 3rd Edition");
            rs.updateRow();

            // Move to the next result set row and delete the row in the
            // result set and apply it to the database.
            rs.next();
            rs.deleteRow();

            // Insert a new row in the result set object with the
            // moveToInsertRow() method. Supply the information to be
            // inserted and finally call the insertRow() method to insert
            // record to the database.
            rs.moveToInsertRow();
            rs.updateString("code", "P0000010");
            rs.updateString("name", "Data Structures, Algorithms");
            rs.updateDouble("price", 50.99);
            rs.insertRow();

            rs.beforeFirst();
            System.out.println();
            System.out.println("id\tcode\tname\tprice");

            while (rs.next()) {
                System.out.println(rs.getLong("id") + "\t"
                                   + rs.getString("code") + "\t"
                                   + rs.getString("name") + "\t"
                                   + rs.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The code snippet prints out the following output:

id  code    name    price
1   P0000001    Java 2 Notebook 25.0
2   P0000002    Java Servlet Programming    30.0
3   P0000003    PHP Programming 20.0
4   P0000004    Longman Active Study Dictionary 40.0
5   P0000005    Ruby on Rails   24.0
6   P0000006    Championship Manager    0.0
7   P0000007    Transport Tycoon Deluxe 0.0
8   P0000008    Roller Coaster Tycoon 3 0.0
9   P0000009    Pro Evolution Soccer    0.0

id  code    name    price
1   P0000001    UML Distilled 3rd Edition   25.0
3   P0000003    PHP Programming 20.0
4   P0000004    Longman Active Study Dictionary 40.0
5   P0000005    Ruby on Rails   24.0
6   P0000006    Championship Manager    0.0
7   P0000007    Transport Tycoon Deluxe 0.0
8   P0000008    Roller Coaster Tycoon 3 0.0
9   P0000009    Pro Evolution Soccer    0.0
10  P0000010    Data Structures, Algorithms 50.99

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>