Databases Reference
In-Depth Information
Use the guidelines in this section to manage your updates more efficiently.
Positioned
Updates
,
Inserts
, and
Deletes
, which are implemented using the
updateXXX
methods of the
ResultSet
object, are useful for GUI applications
that allow application users to scroll through a result set, updating and deleting
rows as they go. The application simply supplies the column in the result set to be
updated and the data to be changed. Then, before moving the cursor from the
row in the result set, the
updateRow()
method is called to update the database.
For example, in the following code, the value of the Age column of the
ResultSet
object
rs
is fetched using the
getInt(
) method, and the
updateInt(
) method is used to update the column with an int value of 25. The
updateRow()
method is called to update the row in the database with the modi-
fied value.
int n = rs.getInt("Age");
// n contains value of Age column in the resultset rs
...
rs.updateInt("Age", 25);
rs.updateRow();
Positioned updates typically are faster than updates using SQL commands
because the cursor is already positioned on the row for the
Select
statement in
process. If the row must be located, the database usually can use a key (for exam-
ple, a
ROWID
for Oracle) that serves as an internal pointer to the row. In addition,
positioned updates reduce the need to write complex SQL statements to update
data, making the application easier to maintain.
Some applications cannot be designed to take advantage of positioned Updates
and Deletes. These applications typically formulate the
Where
clause by calling
getPrimaryKeys()
to use all searchable result columns or by calling
getIndexInfo()
to find columns that may be part of a unique index. These
methods usually work but can result in fairly complex queries. For example:
ResultSet WSrs = WSs.executeQuery
("SELECT first_name, last_name, ssn, address, city,
state, zip FROM employees");