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.
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
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
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();
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
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
absolute(). The methods
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
relative() can be used, because require movement relative to the current row.
|Content on this page is licensed under the Public Documentation License (PDL).|