Insert
The previous section described how to modify a column value using methods in the SDBC API, rather than SQL commands. With the SDBC API, a new row can also be inserted into a table or an existing row deleted programmatically.
Suppose our salesman Bob sold a new product to one of our customers, FTOP Darjeeling tea, and we need to add the new sale to the database. Using the previous example, 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 SALES " +
"VALUES (4, 102, 5, 'FTOP Darjeeling tea', '2002-01-02',150)");
The same thing can be done, without using any SQL commands, by using ResultSet methods in the SDBC API. After a ResultSet object is obtained with the results from the table SALES, build the new row and then insert it into the result set and the table SALES in one step. First, build a new row in the insert row, a special row associated with every ResultSet object. This row is not part of the result set. It can be considered as a separate buffer in which a new row is composed prior to insertion.
The next step is to move the cursor to the insert row by invoking the method moveToInsertRow(). Then set a value for each column in the row that should not be null by calling the appropriate updateXXX() method for each value. Note that these are the same updateXXX() methods used to change a column value in the previous section.
Finally, call insertRow() to insert the row that was populated with values into the result set. This method simultaneously inserts the row into the ResultSet object, as well as the database table from where the result set was selected.
The following code fragment creates a scrollable and modifiable ResultSet object rs that contains all of the rows and columns in the table SALES:
XConnection con = XDriverManager.getConnection("jdbc:mySubprotocol:mySubName");
XStatement stmt = con.createStatement();
XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, stmt);
xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
XResultSet rs = stmt.executeQuery("SELECT * FROM SALES");
XRow row = (XRow)UnoRuntime.queryInterface(XRow.class, rs);
The next code fragment uses the XResultSetUpdate interface of rs to insert the row for FTOP Darjeeling tea, shown in the SQL code example. It moves the cursor to the insert row, sets the six column values, and inserts the new row into rs and SALES:
XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class, rs);
XResultSetUpdate updateRs = (XResultSetUpdate)UnoRuntime.queryInterface(XResultSetUpdate.class, rs);
updateRs.moveToInsertRow();
updateRow.updateInt(1, 4);
updateRow.updateInt(2, 102);
updateRow.updateInt(3, 5);
updateRow.updateString(4, "FTOP Darjeeling tea");
updateRow.updateDate(5, new Date((short)1, (short)2, (short)2002));
updateRow.updateFloat(6, 150);
updateRs.insertRow();
The updateXXX() methods behave differently 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, 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 immediately set, but it is set in the insert row rather than in the result set itself.
In 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.
If a value is not supplied 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, an SQLException is returned when an updateXXX() method is not called to set a value for it. This is also true if a table column is missing in the ResultSet object. In the example above, the query was SELECT * FROM SALES, which produced a result set with all the columns of all the rows. To insert one or more rows, the query does not have to select all rows, but it is advisable to select all columns. Additionally, if the table has many rows, use a WHERE clause to limit the number of rows returned by the SELECT statement.
After the method insertRow() is called, start building another insert row, or move the cursor back to a result set row. Any of the methods can be executed that move the cursor to a specific row, such as first(), last(), beforeFirst(), afterLast(), and absolute(). The methods previous(), relative(), and moveToCurrentRow() can also be used. Note that only moveToCurrentRow() can be invoked as long as the cursor is on the insert row.
When the method moveToInsertRow() is called, the result set records which row the cursor is in, that 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 the current row previously. This also explains why the methods previous() and relative() can be used, because require movement relative to the current row.
| Content on this page is licensed under the Public Documentation License (PDL). |
