Download the JDK
Search the Tutorials
Hide the TOC
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics
JDBC Basics
Getting Started
Setting Up a Database
Establishing a Connection
Setting Up Tables
Retrieving Values from Result Sets
Updating Tables
Milestone: The Basics of JDBC
Using Prepared Statements
Using Joins
Using Transactions
Stored Procedures
SQL Statements for Creating a Stored Procedure
Creating Complete JDBC Applications
Running the Sample Applications
Creating an Applet from an Application
Home Page > JDBC(TM) Database Access > JDBC Basics
« Previous • Trail • Next »
Updating Tables

> Next, you learn to update rows in a result set, using methods in the Java programming language rather than having to send an SQL command.

> Updating a row in a `ResultSet` object is a two-phase process. First, the new value for each column being updated is set, and then the change is applied to the row. The row in the underlying data source is not updated until the second phase is completed.

> The `ResultSet` interface contains two update methods for each JDBC™ type, one specifying the column to be updated as an index and one specifying the column name as it appears in the select list. Column names supplied to updater methods are case insensitive. If a select list contains the same column more than once, the first instance of the column will be updated.

> First, you need to create a `ResultSet` object that is updatable. To do this, supply the `ResultSet` constant `CONCUR_UPDATABLE` to the `createStatement` method, as you have seen in previous examples. The `Statement` object it creates produces an updatable `ResultSet` object each time it executes a query. The following code fragment illustrates creating the updatable `ResultSet` object `uprs`. Note that the code also makes `uprs` scrollable. An updatable `ResultSet` object does not necessarily have to be scrollable, but when you are making changes to a result set, you generally want to be able to move around in it. With a scrollable result set, you can move to rows you want to change, and if the type is `TYPE_SCROLL_SENSITIVE`, you can get the new value in a row after you have changed it.

> >     Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
> >     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> >                                          ResultSet.CONCUR_UPDATABLE);
> >     ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

> The `ResultSet` object `uprs` looks something like this:

> >     COF_NAME                PRICE
> >     ------------------      -----
> >     Colombian               7.99
> >     French_Roast            8.99
> >     Espresso                9.99
> >     Colombian_Decaf         8.99
> >     French_Roast_Decaf      9.99

> The method `updateRow` applies all column changes to the current row. The changes are not made to the row until `updateRow` has been called. You can use the `cancelUpdates` method to back out changes made to the row before the `updateRow` method is called.

> >     Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> >     ResultSet.CONCUR_UPDATABLE);
> >     ResultSet srs = stmt.executeQuery("select COF_Name from COFFEES " +
> >     "where price = 7.99");
> >     srs.next();
> >     srs.updateString("COF_NAME", "Foldgers");
> >     srs.updateRow();

> An update is the modification of a column value in the current row. Let's suppose that you want to raise the price of French Roast Decaf coffee to 10.99:

> >     uprs.last();
> >     uprs.updateFloat("PRICE", 10.99);

> Update operations affect column values in the row where the cursor is positioned, so in the first line the `ResultSet` `uprs` calls the method `last` to move its cursor to the last row (the row where the column `COF_NAME` has the value `FRENCH_ROAST_DECAF`). Once the cursor is on the last row, all of the update methods you call will operate on that row until you move the cursor to another row. The second line changes the value in the `PRICE` column to `10.99` by calling the method `updateFloat`. This method is used because the column value we want to update is a `float` in the Java programming language.

> The `ResultSet.` `updateXXX` methods take two parameters: the column to update and the new value to put in that column. As with the `ResultSet.``getXXX` methods, the parameter designating the column may be either the column name or the column number. There is a different `updateXXX` method for updating each datatype (`updateString`, `updateBigDecimal`, `updateInt`, and so on) just as there are different `getXXX` methods for retrieving different datatypes.

> At this point, the price in `uprs` for French Roast Decaf will be 10.99, but the price in the table `COFFEES` in the database will still be 9.99. To make the update take effect in the database and not just the result set, we must call the `ResultSet` method `updateRow`. Here is what the code should look like to update both `uprs` and `COFFEES` :

> >     uprs.last();
> >     uprs.updateFloat("PRICE", 10.99f);
> >     uprs.updateRow();

> If you had moved the cursor to a different row before calling the method `updateRow`, the update would have been lost. If, on the other hand, you realized that the price should really have been 10.79 instead of 10.99, you could have cancelled the update to 10.99 by calling the method `cancelRowUpdates`. You have to invoke `cancelRowUpdates` before invoking the method `updateRow`; once `updateRow` is called, calling the method `cancelRowUpdates` does nothing. Note that `cancelRowUpdates` cancels all of the updates in a row, so if there are many invocations of the `updateXXX` methods on the same row, you cannot cancel just one of them. The following code fragment first cancels updating the price to 10.99 and then updates it to 10.79:

> >     uprs.last();
> >     uprs.updateFloat("PRICE", 10.99);
> >     uprs.cancelRowUpdates();
> >     uprs.updateFloat("PRICE", 10.79);
> >     uprs.updateRow();

> In this example, only one column value was updated, but you can call an appropriate `updateXXX` method for any or all of the column values in a single row. The concept to remember is that updates and related operations apply to the row where the cursor is positioned. Even if there are many calls to `updateXXX` methods, it takes only one call to the method `updateRow` to update the database with all of the changes made in the current row.

> If you want to update the price for `COLOMBIAN_DECAF` as well, you have to move the cursor to the row containing that coffee. Because the row for `COLOMBIAN_DECAF` immediately precedes the row for `FRENCH_ROAST_DECAF`, you can call the method `previous` to position the cursor on the row for `COLOMBIAN_DECAF`. The following code fragment changes the price in that row to 9.79 in both the result set and the underlying table in the database:

> >     uprs.previous();
> >     uprs.updateFloat("PRICE", 9.79);
> >     uprs.updateRow();

> All cursor movements refer to rows in a `ResultSet` object, not rows in the underlying database. If a query selects five rows from a database table, there will be five rows in the result set, with the first row being row 1, the second row being row 2, and so on. Row 1 can also be identified as the first, and, in a result set with five rows, row 5 is the last.

> The ordering of the rows in the result set has nothing at all to do with the order of the rows in the base table. In fact, the order of the rows in a database table is indeterminate. The DBMS keeps track of which rows were selected, and it makes updates to the proper rows, but they may be located anywhere in the table. When a row is inserted, for example, there is no way to know where in the table it has been inserted.

« Previous • Trail • Next »
Previous page: Retrieving Values from Result Sets
Next page: Milestone: The Basics of JDBC