Java 17 Recipes

12-10. Creating an Updatable ResultSet
 Problem
An application task has queried the database and obtained results. You have stored 
those results into a ResultSet object, and you want to update some of those values in 
ResultSet and commit them back to the database.
 Solution
Make your ResultSet object updatable, and then update the rows as needed while 
iterating through the results. The following example method demonstrates how to make 
ResultSet updatable and then how to update content within that ResultSet, eventually 
persisting it in the database.
private static void queryAndUpdateDbRecipes(String recipeNumber){
String sql = "SELECT ID, RECIPE_NUMBER, RECIPE_NAME, DESCRIPTION " +
"FROM RECIPES " +
"WHERE RECIPE_NUMBER = ?";
ResultSet rs = null;
try (PreparedStatement pstmt =
conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, 
ResultSet.CONCUR_UPDATABLE);){
pstmt.setString(1, recipeNumber);
rs = pstmt.executeQuery();
while(rs.next()){
String desc = rs.getString(4);
System.out.println("Updating row" + desc);
rs.updateString(4, desc + " -- More to come");
rs.updateRow();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (rs != null){
try {
Chapter 12 Working With Databases

---

465
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
The main method method is:
public class UpdateResultSetExample {
public static Connection conn = null;
public static void main(String[] args) {
boolean successFlag = false;
try {
CreateConnection createConn = new CreateConnection();
conn = createConn.getConnection();
// Perform Initial Query
queryDbRecipes();
// Update Resultset Row
queryAndUpdateDbRecipes("12-1");
// Query to see final results
queryDbRecipes();
} catch (java.sql.SQLException ex) {
System.out.println(ex);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
Chapter 12 Working With Databases

---

466
This method could be called passing a string value containing a recipe number. 
Suppose that the recipe number "12-1" was passed to this method; the following output 
would be the result.
Successfully connected
12-1: Installing MySQL - Downloading and installation of a MySQL Database
12-2: Connecting to a Database - DriverManager and DataSource 
Implementations
12-3: Handling SQL Exceptions - Using SQLException
12-4: Querying a Database and Retrieving Results - Obtaining and using data 
from a DBMS
Updating row:Downloading and installation of a MySQL Database
12-1: Installing MySQL - Downloading and installation of a MySQL
Database -- More to come
12-2: Connecting to a Database - DriverManager and DataSource 
Implementations
12-3: Handling SQL Exceptions - Using SQLException
12-4: Querying a Database and Retrieving Results - Obtaining and using data 
from a DBMS