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.1.0</version>
</dependency>
Founder at Kode Java Org
A programmer, recreational runner and diver, live in the island of Bali, Indonesia. Programming in Java, Spring, Hibernate / JPA. You can support me working on this project, buy me a cup of coffee ☕ every little bit helps, thank you 🙏
Latest posts by Wayan (see all)
- How do I create a string of repeated characters? - September 1, 2023
- How do I convert datetime string with optional part to a date object? - August 28, 2023
- How do I split large excel file into multiple smaller files? - April 15, 2023