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.

JDBC Basics