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>
Latest posts by Wayan (see all)
- How do I build simple search page using ZK and Spring Boot? - March 8, 2023
- How do I calculate days between two dates excluding weekends and holidays? - January 12, 2023
- How do I discover the quarter of a given date? - January 11, 2023