The Java TM Tutorial

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

Trail: JDBC Database Access
Lesson: New Features in the JDBC 2.0 API

## 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.

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