How do I make updates in Updatable ResultSet?

By Wayan in JDBC 0 Comment

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

In the example below we have a products table with the id, code, name, and price. In the first step after we load the result set, 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 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 = "root";
    private static final String PASSWORD = "";

    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 statement
            // with CONCUR_UPDATABLE. The updatable doesn't need to be
            // TYPE_SCROLL_SENSITIVE, but adding this parameter to the
            // statement enable 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 products";
            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 print 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.0.32</version>
</dependency>

Maven Central

Latest posts by Wayan (see all)
Views: 2,784

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.