Use getBestRowIdentifier() 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 getBestRowIdentifier().
Some applications cannot be designed to take advantage of positioned updates and deletes. Some applications 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.
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 getBestRowIdentifier() to return 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 getBestRowIdentifier().
Consider the previous example again:
...
ResultSet WSrowid = getBestRowIdentifier()
(... "emp", ...);
...
WSs.executeUpdate("UPDATE EMP SET ADDRESS = ? WHERE ROWID = ?");
// fastest access to the data!
If your data source does not contain special pseudo-columns, the result set of getBestRowIdentifier() 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.
