Updating Data

Updating Data

This section provides general guidelines to help you to optimize system performance when updating data in databases.

Using updateXXX Methods

Although programmatic updates do not apply to all types of applications, developers should attempt to use programmatic updates and deletes. Using the updateXXX methods of the ResultSet object allows the developer to update data without building a complex SQL statement. Instead, the developer simply supplies the column in the result set that is to be updated and the data that is to be changed. Then, before moving the cursor from the row in the result set, the updateRow method must be called to update the database as well.

In the following code fragment, the value of the Age column of the Resultset object rs is retrieved using the method getInt, and the method updateInt is used to update the column with an int value of 25. The method updateRow is called to update the row in the database that contains the modified value.
int n = rs.getInt("Age");  
// n contains value of Age column in the resultset rs 
. . . 
rs.updateInt("Age", 25);  
rs.updateRow(); 
In addition to making the application more easily maintainable, programmatic updates usually result in improved performance. Because the database server is already positioned on the row for the Select statement in process, performance-expensive operations to locate the row to be changed are not needed. If the row must be located, the server usually has an internal pointer to the row available (for example, ROWID).

Using getBestRowIndentifier()

Use getBestRowIndentifier() to determine the optimal set of columns to use in the Where clause for updating data. Pseudo-columns often provide the fastest access to the data, and these columns can only be determined by using getBestRowIndentifier().

Some applications cannot be designed to take advantage of positional updates and deletes. Some applications might formulate the Where clause by using all searchable result columns by calling getPrimaryKeys(), or by calling getIndexInfo() to find columns that might be part of a unique index. These methods usually work, but might result in fairly complex queries.

Consider the following example:
ResultSet WSrs = WSs.executeQuery  
     ("SELECT first_name, last_name, ssn, address, city, state, zip  
        FROM emp"); 
// fetchdata 
... 
WSs.executeQuery ("UPDATE EMP SET ADDRESS = ? 
     WHERE first_name = ? and last_name = ? and ssn = ?  
     and address = ? and city = ? and state = ?  
     and zip = ?"); 
// fairly complex query 
Applications should call getBestRowIndentifier() to retrieve the optimal set of columns (possibly a pseudo-column) that identifies a specific record. Many databases support special columns that are not explicitly defined by the user in the table definition but are "hidden" columns of every table (for example, ROWID and TID). These pseudo-columns generally provide the fastest access to the data because they typically are pointers to the exact location of the record. Because pseudo-columns are not part of the explicit table definition, they are not returned from getColumns. To determine if pseudo-columns exist, call getBestRowIndentifier().

Consider the previous example again:
... 
ResultSet WSrowid = getBestRowIndentifier()  
   (.... "emp", ...); 
... 
WSs.executeQuery ("UPDATE EMP SET ADDRESS = ? 
     WHERE first_name = ? and last_name = ? and ssn = ?  
     and address = ? and city = ? and state = ?  
     and zip = ?"); 
// fastest access to the data! 
If your data source does not contain special pseudo-columns, then the result set of getBestRowIndentifier() consists of the columns of the most optimal unique index on the specified table (if a unique index exists). Therefore, your application does not need to call getIndexInfo to find the smallest unique index.

Synchronize with Contents