The Java TM Tutorial

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

Trail: JDBC Database Access
Lesson: JDBC Basics

## Updating Tables

> Suppose that after a successful first week, the proprietor of The Coffee Break wants to update the `SALES` column in the table `COFFEES` by entering the number of pounds sold for each type of coffee. The SQL statement to update one row might look like this:

> >     String updateString = "UPDATE COFFEES " +
> >     	"SET SALES = 75 " +
> >     	"WHERE COF_NAME LIKE 'Colombian'";

> Using the `Statement` object `stmt` , this JDBC code executes the SQL statement contained in `updateString` :

> >     stmt.executeUpdate(updateString);

> The table `COFFEES` will now look like this:

> >     COF_NAME		SUP_ID	PRICE   SALES	TOTAL
> >     --------		------ 	-----	-----   -----
> >     Colombian		101	7.99	75	0
> >     French_Roast		49	8.99	0	0
> >     Espresso		150	9.99	0	0
> >     Colombian_Decaf		101	8.99	0	0
> >     French_Roast_Decaf	49	9.99	0	0

> Note that we have not yet updated the column `TOTAL` , so it still has the value `0` .

> Now let's select the row we updated, retrieve the values in the columns `COF_NAME` and `SALES` , and print out those values:

> >     String query = "SELECT COF_NAME, SALES FROM COFFEES " +
> >     	       "WHERE COF_NAME LIKE 'Colombian'";
> >     ResultSet rs = stmt.executeQuery(query);
> >     while (rs.next()) {
> >     		String s = rs.getString("COF_NAME");
> >     		int n = rs.getInt("SALES");
> >     		System.out.println(n + " pounds of " + s +
> >      						  " sold this week.");
> >     }

> This will print the following:

> >     75 pounds of Colombian sold this week.

> Since the `WHERE` clause limited the selection to only one row, there was just one row in the `ResultSet` `rs` and one line printed as output. Accordingly, it is possible to write the code without a `while` loop:

> >     rs.next();
> >     String s = rs.getString(1);
> >     int n = rs.getInt(2);
> >     System.out.println(n + " pounds of " + s + " sold this week.");

> Even when there is only one row in a result set, you need to use the method `next` to access it. A `ResultSet` object is created with a cursor pointing above the first row. The first call to the `next` method positions the cursor on the first (and in this case, only) row of `rs` . In this code, `next` is called only once, so if there happened to be another row, it would never be accessed.

> Now let's update the `TOTAL` column by adding the weekly amount sold to the existing total, and then let's print out the number of pounds sold to date:

> >     String updateString = "UPDATE COFFEES " +
> >     		      "SET TOTAL = TOTAL + 75 " +
> >     		      "WHERE COF_NAME LIKE 'Colombian'";
> >     stmt.executeUpdate(updateString);
> >     String query = "SELECT COF_NAME, TOTAL FROM COFFEES " +
> >     				   "WHERE COF_NAME LIKE 'Colombian'";
> >     ResultSet rs = stmt.executeQuery(query);
> >     while (rs.next()) {
> >     		String s = rs.getString(1);
> >     		int n = rs.getInt(2);
> >     		System.out.println(n + " pounds of " + s + " sold to date.");
> >     }

> Note that in this example, we used the column index instead of the column name, supplying the index `1` to `getString` (the first column of the result set is `COF_NAME` ), and the index `2` to `getInt` (the second column of the result set is `TOTAL` ). It is important to distinguish between a column's index in the database table as opposed to its index in the result set table. For example, `TOTAL` is the fifth column in the table `COFFEES` but the second column in the result set generated by the query in the example above.

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