This section describes how to insert rows into target tables through ResultSet objects.
A ResultSet object with update capability can also be used to insert new rows into database tables.
All you need to do are:
Create a statement with concurrency type set to ResultSet.CONCUR_UPDATABLE.
Call executeQuery() method to return a ResultSet object on the target table.
Call moveToInsertRow() method to move the cursor to the insert row.
Call updateXXX() methods to set column values on the insert row. Do not set any value to the primary key column if it is defined as an INDENITTY column.
Call insertRow() method store the insert row into the database table.
Call moveToCurrentRow() method to move the cursor back to the current row.
I wrote the following sampple program to test this feature:
/**
* DerbyResultSetInsertRow.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class DerbyResultSetInsertRow {
public static void main(String [] args) {
Connection con = null;
try {
con = DriverManager.getConnection(
"jdbc:derby://localhost/TestDB");
// Create a Statement for scrollable ResultSet
Statement sta = con.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
// Catch the ResultSet object
ResultSet res = sta.executeQuery(
"SELECT * FROM Profile WHERE 1=2");
// Check ResultSet's updatability
if (res.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
System.out.println("ResultSet non-updatable.");
} else {
System.out.println("ResultSet updatable.");
}
// Move the cursor to the insert row
res.moveToInsertRow();
// Set the new first name and last name
res.updateString("FirstName", "Lucy");
res.updateString("LastName", "Harrington");
res.updateString("Point", "123.456");
res.updateString("BirthDate", "1977-07-07");
res.updateString("ModTime", "2007-01-01 01:01:01.001");
// Store the insert into database
res.insertRow();
// Move the cursor back to the current row
res.moveToCurrentRow();
System.out.println("Row inserted ok.");
// Close ResultSet and Statement
res.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
Also notice that I used "1=2" as the WHERE condition to purposely generate an empty ResultSet object.
The program executed correctly:
