Inserting and Deleting Rows Programmatically

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 stmt is a Statement object, 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 ResultSet methods in the JDBC 2.0 API. Basically, after you have a ResultSet object with results from the table COFFEES , you can build the new row and then insert it into both the result set and the table COFFEES in one step. You build a new row in what is called the insert row, a special row associated with every ResultSet object. 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 updateXXX method for each value. Note that these are the same updateXXX methods you used in the previous section for changing a column value. Finally, you call the method insertRow to insert the row you have just populated with values into the result set. This one method simultaneously inserts the row into both the ResultSet object and the database table from which the result set was selected.

The following code fragment creates the scrollable and updatable ResultSet object 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 ResultSet object uprs to 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 uprs and 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 updateXXX methods seem to behave differently here from the way they behaved in the update examples. In those examples, the value set with an updateXXX method 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 updateXXX method 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 updateXXX method does not affect the underlying database table. The method updateRow must be called to have updates occur in the database. For insertions, the method insertRow inserts 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 NULL values, then the value assigned to that column is NULL . If a column does not accept null values, however, you will get an SQLException when you do not call an updateXXX method to set a value for it. This is also true if a table column is missing in your ResultSet object. 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 WHERE clause to limit the number of rows returned by your SELECT statement.

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 first , last , beforeFirst , afterLast , and absolute . You can also use the methods previous , relative , and moveToCurrentRow . Note that you can invoke moveToCurrentRow only 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 moveToCurrentRow can 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 previous and relative , which require movement relative to the current row.

New Features in the JDBC 2.0 API