The Java TM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC Database Access
Lesson: New Features in the JDBC 2.0 API

## 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. . .
> >     }

| Start of Tutorial > Start of Trail > Start of Lesson | Search