Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
Suppose that after a successful first week, the proprietor of The Coffee Break wants to update theSALES
column in the tableCOFFEES
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
objectstmt
, this JDBC code executes the SQL statement contained inupdateString
: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 0Note that we have not yet updated the column
TOTAL
, so it still has the value0
.Now let's select the row we updated, retrieve the values in the columns
COF_NAME
andSALES
, 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 theResultSet
rs
and one line printed as output. Accordingly, it is possible to write the code without awhile
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. AResultSet
object is created with a cursor pointing above the first row. The first call to thenext
method positions the cursor on the first (and in this case, only) row ofrs
. 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
togetString
(the first column of the result set isCOF_NAME
), and the index2
togetInt
(the second column of the result set isTOTAL
). 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 tableCOFFEES
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
Feedback Form |
Copyright 1995-2005 Sun Microsystems, Inc. All rights reserved.