Updating Data from a Result Set
There is another interesting way to update data. You can update items in the result set and then store the updates back into the database. The ResultSet interface contains methods for updating items of various data types. The format of the various update methods is similar to the get methods, in that the update methods take either a numeric column number or a string column name. The second parameter for each update method is the value you want to store in the column. For instance, to change the value of the first_name column, use the following statement:
results.updateString("first_name", "MyName");
When you create your query statement, you must specify a result set type to let the driver know you want to update result set values. The three statement creation methods—createStatement, prepareStatement, and prepareCall—allow you to specify a result set type and a result set concurrency. The result set type can be TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE. The TYPE_FORWARD_ONLY type indicates that you can only scroll forward through the result set, you can't jump back to a previous result set. For the other two types, you can move to any position in the result set. The sensitive/insensitive variation indicates whether or not the result set is sensitive to external changes to a row.
The options for the concurrency are CONCUR_READ_ONLY and CONCUR_UPDATABLE. If you plan to update rows, add new rows, or delete rows using the result set, you must set the concurrency to CONCUR_UPDATABLE.
Listing 3.3 shows a program that reads rows and updates them using the result set.
NOTE
The example in Listing 3.3 uses Oracle instead of Cloudscape to use some of the newer JDBC 2.0 features. You will find that not all servers and/or drivers support all the features of JDBC 2.0.
Listing 3.3 Source Code for UpdateResultSet.java
package usingj2ee.jdbc;
import java.sql.*;
public class UpdateResultSet
{
public static void main(String[ ] args)
{
try
{
// Make sure the DriverManager knows about the driver
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
// Create a connection to the database
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@flamingo:1521:j2eebook",
"j2eeuser", "j2eepass");
// Create a statement for retrieving and updating data
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stmt.executeQuery("select * from Person");
// Execute the query
ResultSet results = stmt.getResultSet();
while (results.next())
{
// Get the name values
String firstName = results.getString("first_name");
String lastName = results.getString("last_name");
// Change the name values
results.updateString("first_name", firstName.toUpperCase());
results.updateString("last_name", lastName.toUpperCase());
// Update the row
results.updateRow();
}
conn.close();
}
catch (Exception exc)
{
exc.printStackTrace();
}
}
}
To delete the current row, call the deleteRow method:
public void deleteRow()
To insert a new row, position the result set to a special row called the insert row by calling moveToInsertRow:
public void moveToInsertRow()
You still use the update methods to modify the contents for the new row, but when you need to save the changes, call insertRow:
public void insertRow()