This section describes how to create ResultSet objects with update capability.
ResultSet objects can also be created with update capability so that they can be used to perform the same functionalities
as the "UPDATE", "INSERT" and "DELETE" SQL statements. To create a ResultSet object, you must follow several guidelines:
The ResultSet object must be created with a SELECT statement by the executeQury() method on a Statement object.
The Statement object must be created with ResultSet.CONCUR_UPDATABLE as the concurrency type.
The SELECT statement must have a single underlying table, with no joins and no aggregations (GROUP BY).
The underlying table must have a primary column, which must be selected in result by the SELECT statement.
All columns that require user values (nonnullable and no default values) in the underlying table must be selected in result by the SELECT statement.
The JDBC driver must support updatability.
Of course, the Derby JDBC driver does support updatability. So I wrote the following sample program to update one row of the Profile table:
Here is a sample program that updated one row in the Profile table through a ResultSet object:
/**
* DerbyUpdateResultSet.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class DerbyUpdateResultSet {
public static void main(String [] args) {
Connection con = null;
try {
con = DriverManager.getConnection(
"jdbc:derby://localhost/TestDB");
// Create a Statement for scrollable ResultSet
Statement sta = con.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
// Catch the ResultSet object
ResultSet res = sta.executeQuery(
"SELECT * FROM Profile WHERE ID = 9");
// Check ResultSet's updatability
if (res.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
System.out.println("ResultSet non-updatable.");
} else {
System.out.println("ResultSet updatable.");
}
// Move the cursor to the first row
res.next();
// Get the current first name and last name
String firstName = res.getString("FirstName");
String lastName = res.getString("LastName");
System.out.println("Current name: "+firstName+" "+lastName);
// Set the new first name and last name
res.updateString("FirstName", "Johnny");
res.updateString("LastName", "Caprio");
res.updateRow();
// Can not get the updated first name and last name back
// firstName = res.getString("FirstName");
// lastName = res.getString("LastName");
// System.out.println("Updated name: "+firstName+" "+lastName);
System.out.println("Updated name: Johnny Caprio");
// Close ResultSet and Statement
res.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
Here is the output:
ResultSet updatable.
Current name: 1352 17d9
Updated name: Johnny Caprio
Notice that I commented out 3 lines of code, because getXXX() methods give me exceptions
when called after the updateRow() method. This is not mentioned in the JDBC API document.
Exception: Invalid operation at current cursor position.
