SQL Example 2

This example shows how to use parameter host variables and expands the previous example by adding another method which updates a numeric field on one record of the Employer table.

Figure 1. Java Interface

public interface Employer
{
  public void setEmployerSize(EmployerKey empKey,
                            LongWrapper newSize)
    throws AppException, InformationalException;
public LongWrapper countEmployers() throws AppException; }

The following struct is required to contain the primary key for the employer:

Figure 2. Struct for employer key

public final class EmployerKey
implements Serializable, DeepCloneable {
  /**
   *  REFERENCE_NUMBER -> String
   */
  public String employerNumber = "";
}

The SQL statement for this method is:

Figure 3. SQL Implementation

UPDATE Employer
  SET size = :2.longValue
  WHERE employerNumber = :employerNumber;

Note that since longValue is contained in the second parameter it is necessary to qualify it with 2.. Unqualified parameter references are assumed to reference the first parameter.

The SQL statement below qualifies both parameters and is equivalent to the one above:

Figure 4. SQL Implementation with qualified parameters

UPDATE Employer
  SET size = :2.newSize.longValue
  WHERE employerNumber = :1.employerNumber;