Updatable ResultSet in JDBC

Introduction

JDBC (Java Database Connectivity) is a popular API (Application Programming Interface) that allows Java programs to interact with databases. JDBC provides a set of classes and methods to access and manipulate data in a database. One of the important features of JDBC is the ability to update the result set. In this blog post, we will discuss the Updatable ResultSet example in JDBC.

What is UpdatableResultSet?

A result set is a set of rows that are returned from a database query. The Updatable ResultSet is a type of result set that allows us to update the data in the result set. In other words, we can modify the data in the result set and update the corresponding data in the database.

Example

Let’s consider a scenario where we have a table called “employees” in a database. This table contains the following columns: id, name, age, and salary. We want to update the salary of an employee based on their id. We can use the Updatable ResultSet to achieve this.

Following is an example code snippet that demonstrates how to use Updatable ResultSet:

import java.sql.*;

public class UpdatableResultSetDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM employees WHERE id = 1");

        if (resultSet.next()) {
            System.out.println("Employee Name: " + resultSet.getString("name"));
            System.out.println("Employee Age: " + resultSet.getInt("age"));
            System.out.println("Employee Salary: " + resultSet.getInt("salary"));

            resultSet.updateInt("salary", 50000);
            resultSet.updateRow();

            System.out.println("Employee Salary Updated Successfully");
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
}

In this code, we first establish a connection to the database using the DriverManager class. We then create a statement object with the ResultSet.TYPE_SCROLL_SENSITIVE and ResultSet.CONCUR_UPDATABLE parameters. These parameters indicate that the result set is scrollable and updatable.

Next, we execute a SELECT query to retrieve the data for the employee with id 1. We use the next() method to move the cursor to the first row in the result set. We then print out the employee’s name, age, and salary using the getString() and getInt() methods.

After that, we update the employee’s salary to 50000 using the updateInt() method. We then call the updateRow() method to update the corresponding row in the database.

Finally, we close the result set, statement, and connection objects.

Conclusion

In this blog post, we discussed the Updatable ResultSet in JDBC and how it allows us to update data in the result set and correspondingly update the data in the database. We also provided an example code snippet that demonstrates how to use Updatable ResultSet in a Java program. By using Updatable ResultSet, we can easily update database records and improve the efficiency of our database interactions.