insertRow() - Inserting New Rows through ResultSet Objects

JDBC Tutorials - Herong's Tutorial Examples

∟Derby (Java DB) - ResultSet Objects of Queries

∟insertRow() - Inserting New Rows through ResultSet Objects

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 sample program to test this feature:

/* DerbyResultSetInsertRow.java
 * Copyright (c) HerongYang.com. 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();
    }
  }
}