Deleting a Row

So far, you have seen how to update a column value and how to insert a new row. Deleting a row is the third way to modify a ResultSet object, and it is the simplest. All you do is move the cursor to the row you want to delete and then call the method deleteRow . For example, if you want to delete the fourth row in the ResultSet uprs , your code will look like this:
 
uprs.absolute(4);
uprs.deleteRow();
The fourth row has been removed from uprs and also from the database.

The only issue about deletions is what the ResultSet object actually does when it deletes a row. With some JDBC drivers, a deleted row is removed and is no longer visible in a result set. Some JDBC drivers use a blank row as a placeholder (a "hole") where the deleted row used to be. If there is a blank row in place of the deleted row, you can use the method absolute with the original row positions to move the cursor because the row numbers in the result set are not changed by the deletion.

In any case, you should remember that JDBC drivers handle deletions differently. For example, if you write an application meant to run with different databases, you should not write code that depends on there being a hole in a result set.

Seeing Changes in Result Sets
If you or anyone else modifies data in a ResultSet object, the change will always be visible if you close it and then reopen it. In other words, if you re-execute the same query, you will produce a new result set, based on the data currently in a table. This result set will naturally reflect changes anyone made earlier.

The question is whether you can see changes you or anyone else made while the ResultSet object is still open. (Generally, you will be most interested in the changes made by others.) The answer depends on the DBMS, the driver, and the type of ResultSet object you have.

With a ResultSet object that is TYPE_SCROLL_SENSITIVE, you can always see updates anyone makes to column values. You can usually see inserts and deletes, but the only way to be sure is to use DatabaseMetaData methods that return this information. (Refer to the second edition of JDBC Database Access with Java for information on getting metadata and for more details regarding the visibility of changes.)

You can to some extent regulate what changes are visible by raising or lowering the transaction isolation level for your connection with the database. For example, the following line of code, where con is an active Connection object, sets the connection's isolation level to TRANSACTION_READ_COMMITTED :
 
con.setTransactionIsolation(TRANSACTION_READ_COMMITTED);
With this isolation level, your ResultSet object will not show any changes before they are committed, but it can show changes that may have other consistency problems. To allow fewer data inconsistencies, you could raise the transaction isolation level to TRANSACTION_REPEATABLE_READ . The problem is that the higher the isolation level, the poorer the performance. And, as is always true of databases and drivers, you are limited to what they actually provide. Many programmers just use their database's default transaction isolation level. If you want more information about transaction isolation levels, you should consult a book on databases or the second edition of JDBC Database Access with Java .

In a ResultSet object that is TYPE_SCROLL_INSENSITIVE , you generally cannot see changes made to it while it is still open. Some programmers use only this type of ResultSet object because they want a consistent view of data and do not want to see changes made by others.

You can use the method refreshRow to get the latest values for a row straight from the database. This method can be very expensive, especially if the DBMS returns multiple rows each time you call refreshRow . Nevertheless, its use can be valuable if it is critical to have the latest data. Even when a result set is sensitive and changes are visible, an application may not always see the very latest changes that have been made to a row if the driver retrieves several rows at a time and caches them. Thus, using the method refreshRow is the only way to be sure that you are seeing the most up-to-date data.

The following code sample illustrates how an application might use the method refreshRow when it is absolutely critical to see the most current values. Note that the result set should be sensitive; if you use the method refreshRow with a ResultSet object that is TYPE_SCROLL_INSENSITIVE , refreshRow does nothing. (The urgency for getting the latest data is a bit improbable for the table COFFEES , but a commodities trader's fortunes could depend on knowing the latest prices in a wildly fluctuating coffee market. Or, for example, you would probably want the airline reservation clerk to check that the seat you are reserving is still available.)
 
Statement stmt = con.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery(SELECT COF_NAME, PRICE FROM COFFEES);
uprs.absolute(4);
Float price1 = uprs.getFloat("PRICE");
// do something. . .
uprs.absolute(4);
uprs.refreshRow();
Float price2 = uprs.getFloat("PRICE");
if (price2 > price1) {
	// do something. . .
}

New Features in the JDBC 2.0 API