|New Features in the JDBC 2.0 API|
In the previous section you saw how to modify a column value using methods in the JDBC 2.0 API rather than having to use SQL commands. With the JDBC 2.0 API, you can also insert a new row into a table or delete an existing row programmatically.
Let's suppose that our coffee house proprietor is getting a new variety from one of his coffee suppliers, The High Ground, and wants to add the new coffee to his database. Using the JDBC 1.0 API, he would write code that passes an SQL insert statement to the DBMS. The following code fragment, in which
Statementobject, shows this approach:stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Kona', 150, 10.99, 0, 0)");
You can do the same thing without using any SQL commands by using
ResultSetmethods in the JDBC 2.0 API. Basically, after you have a
ResultSetobject with results from the table
COFFEES, you can build the new row and then insert it into both the result set and the table
COFFEESin one step. You build a new row in what is called the insert row, a special row associated with every
ResultSetobject. This row is not actually part of the result set; you can think of it as a separate buffer in which to compose a new row.
Your first step will be to move the cursor to the insert row, which you do by invoking the method
moveToInsertRow. The next step is to set a value for each column in the row. You do this by calling the appropriate
updateXXXmethod for each value. Note that these are the same
updateXXXmethods you used in the previous section for changing a column value. Finally, you call the method
insertRowto insert the row you have just populated with values into the result set. This one method simultaneously inserts the row into both the
ResultSetobject and the database table from which the result set was selected.
The following code fragment creates the scrollable and updatable
uprs, which contains all of the rows and columns in the table
COFFEES:Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName"); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
The next code fragment uses the
uprsto insert the row for Kona coffee, shown in the SQL code example. It moves the cursor to the insert row, sets the five column values, and inserts the new row into
COFFEES:uprs.moveToInsertRow(); uprs.updateString("COF_NAME", "Kona"); uprs.updateInt("SUP_ID", 150); uprs.updateFloat("PRICE", 10.99); uprs.updateInt("SALES", 0); uprs.updateInt("TOTAL", 0); uprs.insertRow();
Because you can use either the column name or the column number to indicate the column to be set, your code for setting the column values could also have looked like this:uprs.updateString(1, "Kona"); uprs.updateInt(2, 150); uprs.updateFloat(3, 10.99); uprs.updateInt(4, 0); uprs.updateInt(5, 0);
You might be wondering why the
updateXXXmethods seem to behave differently here from the way they behaved in the update examples. In those examples, the value set with an
updateXXXmethod immediately replaced the column value in the result set. That was true because the cursor was on a row in the result set. When the cursor is on the insert row, the value set with an
updateXXXmethod is likewise immediately set, but it is set in the insert row rather than in the result set itself. In both updates and insertions, calling an
updateXXXmethod does not affect the underlying database table. The method
updateRowmust be called to have updates occur in the database. For insertions, the method
insertRowinserts the new row into the result set and the database at the same time.
You might also wonder what happens if you insert a row but do not supply a value for every column in the row. If you fail to supply a value for a column that was defined to accept SQL
NULLvalues, then the value assigned to that column is
NULL. If a column does not accept null values, however, you will get an
SQLExceptionwhen you do not call an
updateXXXmethod to set a value for it. This is also true if a table column is missing in your
ResultSetobject. In the example above, the query was
SELECT * FROM COFFEES, which produced a result set with all the columns of all the rows. When you want to insert one or more rows, your query does not have to select all rows, but it is safer to select all columns. Especially if your table has hundreds or thousands of rows, you might want to use a
WHEREclause to limit the number of rows returned by your
After you have called the method
insertRow, you can start building another row to be inserted, or you can move the cursor back to a result set row. You can, for instance, invoke any of the methods that put the cursor on a specific row, such as
absolute. You can also use the methods
moveToCurrentRow. Note that you can invoke
moveToCurrentRowonly when the cursor is on the insert row.
When you call the method
moveToInsertRow, the result set records which row the cursor is sitting on, which is by definition the current row. As a consequence, the method
moveToCurrentRowcan move the cursor from the insert row back to the row that was previously the current row. This also explains why you can use the methods
relative, which require movement relative to the current row.
|New Features in the JDBC 2.0 API|