Single updates by means of the update() method of the Data interface

You can use the overloaded update() method of the Data interface to update zero or more rows of a database object.

Example 1: inserting a row into a table by passing values in a bean

Suppose that you want to insert a record into the HRDept.Employee table, which is defined like this:

CREATE TABLE HRDept.Employee(
  EMPNO CHAR(6) NOT NULL,
  FIRSTNME VARCHAR(12) NOT NULL,
  MIDINIT CHAR(1),
  LASTNAME VARCHAR(15), 
  WORKDEPT CHAR(2), 
  PHONENO CHAR(4),
  HIREDATE DATE,
  PRIMARY KEY(EMPNO))

The corresponding bean is defined like this:

public Employee {
  @Column(name="EMPNO") 
   public String employeeId;
  @Column(name="FIRSTNME") 
   public String firstName;
  @Column(name="MIDINIT") 
   public String middleInitial;
  public String lastName;
  @Column(name="WORKDEPT") 
   public String departmentId;
  @Column(name="PHONENO") 
   public String extension;
  public Date hireDate;
}
With this version of the update() method
public int update(java.lang.String sql, Object... parameters)
your application logic might look similar to the following code:
Connection con = DriverManager.getConnection(...);
Data db = DataFactory.getData(con);

Employee newCollegeHire = 
   new Employee("000010", "CHRISTINE", "I", "HAAS", "A00", 
      "3978", new java.sql.Date(System.currentTimeMillis()));

int oneCount = db.update(
 "INSERT INTO HRDept.Employee(EMPNO, FIRSTNME," +
   " MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE) " +
 "VALUES(:employeeId, :firstName, :middleInitial," +     
     " :lastName, :departmentId, :extension," +
    " :hireDate )", newCollegeHire );

Example 2: inserting a row by passing values in a bean, and retrieving a generated value into that bean

You want to add a new "PAYROLL PROGRAMMING" project to the table HRDept.PROJ. However, you have information only for the projName, departNo, respEmp, and majProj properties of the DeptProject object that you plan to use for passing values to the INSERT statement in the update() method.

Eventually, you will have the information for the rest of the properties in the DeptProject object. To update the record for the "PAYROLL PROGRAMMING" project with that remaining information, you will need to know the value in the PROJID column, which is a generated identity column that is used a the primary key.

So, when you insert the new record, you need a means of retrieving the value that is generated in the PROJID column and placing that value in the projId property of the DeptProject object.

This is the definition of the HRDept.PROJ table:

CREATE TABLE HRDept.PROJ
  (PROJID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
   PROJNAME VARCHAR(24),
   DEPTNO CHAR(3),
   RESPEMP CHAR(6),
   PRSTAFF DECIMAL(5, 2),
   PRSTDATE DATE,
   PRENDATE DATE,
   MAJPROJ INTEGER,
   PRIMARY KEY(PROJID))

This is the definition of DeptProject, the bean that corresponds to this table:

public class DeptProject {
  @GeneratedKey
  public Integer projId;
  public String projName;
  public String departNo;
  public String respEmp;
  public BigDecimal prStaff;
  public Date prStDate;
  public Date prEnDate;
  public String majProj;
}
By using this version of the update() method
public int update (java.lang.String sql, Object... parameters)
your code to insert a row into the HRDept.PROJ table might look like this:
Connection con = DriverManager.getConnection(...);
Data db = DataFactory.getData(con);
DeptProject newProject = 
  new DeptProject ("PAYROLL PROGRAMMING", "A00",
   "000010", 319);  // 319: GENERAL AD SYSTEMS

int updateCount = db.update( "INSERT INTO " +
  "HRDept.PROJ(PROJNAME, DEPTNO, RESPEMP, MAJPROJ) " +
  "VALUES(:projName, :departNo, :respEmp, :majProj)", 
  newProject);

Because the projId property in the DeptProject object is defined with an @GeneratedKey annotation and the column PROJID is defined as an identity column that always generates an integer, the value in the PROJID column is passed into the projId property before control is returned from the call to the update() method.

When you later get information about the project's staffing level, start date, and end date, you can use the value for projId when updating the project's row in the HRDept.PROJ table.

newProject.prStaff = ...;
newProject.prStDate = ...;
newProject.prEnDate = ...;

oneCount = db.update("UPDATE HRDept.PROJ SET " +   
    "PRSTAFF=:prStaff, PRSTDATE=:prStDate, " +
    "PRENDATE=:prEnDate WHERE PROJID=:projId ", 
    newProject);

Example 3: inserting a row passing values in an Object array, and retrieving a generated value into an Object array or simple class

If you want to pass values without a bean and retrieve generated values into an object other than a bean, you can use the following version of the update() method:

<T> T update(java.lang.String sql, Class<T> returnClass, String[] columnNames,
             Object... parameters)
This method returns one or more generated values, depending on the return type indicated. The value of the return type Class<T> must be either:
  • Object[].class
  • A simple class directly assignable from JDBC, such as Integer.class or String.class

When the return type is Object[].class, the first n elements of the array are the n generated values from the columns in the generatedColNames parameter. The last element in the array is the update count.

When the return type is a simple, directly assignable JDBC class, a single generated value is returned of the given type. The update count is not returned.

For example, a project might be represented by an Object[], not a bean. The HRDept.PROJ table can be updated and the generated key for the new project retrieved as follows:

Object[] myProj = new Object[4];
myProj[0] = ...;
myProj[1] = ...;
myProj[2] = ...; 
myProj[3] = ...;

String[] generatedColNames = new String[] {"PROJID"};

Integer generatedProjID;

String sql = "INSERT INTO HRDept.PROJ(PROJNAME, DEPTNO, "  
             + "RESPEMP, MAJPROJ) values (?, ?, ?, ?)";
generatedProjID = db.update( sql, Integer.class,
                             generatedColNames, myProj);

Feedback