Passing values from data structures into SQL statements

pureQuery has default rules for using the parameters that you pass to the annotated or inline methods as values for the parameters in SQL statements. pureQuery also has default rules for using the passed parameters when it registers OUT and INOUT parameters. When you use annotated methods, you can override these default rules with an implementation of the ParameterHandler<T> interface.

The ParameterHandler<T> interface has only one method: handleParameters(). When pureQuery calls this method, it passes a PreparedStatement object, which was used to run the SQL statement, along with the parameters that you passed to the annotated method.

Use ParameterHandler<T> objects to set the values of the parameters in the SQL statement and to register any OUT or INOUT parameters. See the Javadoc for your Java™ SDK for information about the java.sql.PreparedStatement interface.

Example one

The purpose of this example is explained in comments in the code.

package com.samplePackage;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;

import com.ibm.pdq.runtime.generator.ParameterHandler;

// This is an example of a custom ParameterHandler that:
// (1) validates a method parameter, and 
// (2) processes a method parameter
// before setting the method parameter as a statement parameter.  This custom
// ParameterHandler also
// (3) Sets one of the statement parameters with a hard-coded value.
//
// This first statement parameter is bonusFactor.  The value set is a number between
// 1 and 2.  The value passed in as a method parameter is between 100% and 200%.
// Therefore, the parameter handler verifies that the passed-in value is between
// 100 and 200, and it divides the value by 100 before setting it as the statement
// parameter bonusFactor.
//
// The second statement parameter is bonusMaxSumForDept.  This is set with a value
// that is hard-coded in the parameter handler -- $20,000.00 .
public class BonusIncreaseParameterHandler implements ParameterHandler
{
  // ?1 is the return value
  // The IN parameter ?2 is set from the passed-in method parameter newBonusPercentageOfOldBonus
  // The IN parameter ?3 is set from a fixed number in the ParameterHandler
  // There are four OUT parameters to the SQL call statement:
  // (1) ?4 => deptsWithoutNewBonuses
  // (2) ?5 => countDeptsViewed
  // (3) ?6 => countDeptsBonusChanged
  // (5) ?7 => errorMsg
  public void handleParameters (PreparedStatement stmt, Object... parameters) throws SQLException
  {
    CallableStatement cstmt = (CallableStatement) stmt;
    
    double newBonusPercentageOfOldBonus = (Double) parameters[1];

    // Verify that the value of the parameter is reasonable
    if (100 > newBonusPercentageOfOldBonus || 200 < newBonusPercentageOfOldBonus) { throw new RuntimeException (
      "The bonusFactorPercentage must be between 100 and 200 inclusive.  The new bonus will be this percentage of the old bonus.  (So, for example, if bonusFactorPercentage=100, then the bonus will not change.)"); }

    // Calculate the bonusFactor to set in cstmt
    double bonusFactor = newBonusPercentageOfOldBonus / 100.0;
    
    // The value for this cstmt parameter is fixed
    double bonusMaxSumForDept = 20000.00;
    
    // Register OUT parameters and set IN parameters
    cstmt.registerOutParameter (1, Types.INTEGER);
    stmt.setDouble (2, bonusFactor);
    stmt.setDouble (3, bonusMaxSumForDept);
    cstmt.registerOutParameter (4, Types.VARCHAR);
    cstmt.registerOutParameter (5, Types.INTEGER);
    cstmt.registerOutParameter (6, Types.INTEGER);
    cstmt.registerOutParameter (7, Types.VARCHAR);
  }

}

Example two

The purpose of this example is explained in comments in the code.

package com.samplePackage;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.ibm.pdq.runtime.generator.ParameterHandler;

// This is an example of a custom ParameterHandler that enables a "legacy" user
// bean to be used un-changed.  See the CustomDepartment class for details about the
// bean implementation and why it is being used un-changed.
public class CustomDepartmentParameterHandler implements ParameterHandler
{

  public void handleParameters (PreparedStatement stmt, Object... parameters) throws SQLException
  {
    CustomDepartment department = (CustomDepartment) parameters[0];
    stmt.setString (1, department.getDepartmentCode ()); 
  }

}

Here is the definition of the CustomDepartment bean.

package com.samplePackage;

// This is an example of a "legacy" bean that a theoretical user might not want
// to modify. Changes he would need to make to make this bean compliant:
// (1) Either change the property names, add @Column, or add @ColumnOverride to
// enable the properties to be mapped to the columns.
// (2) Add setters corresponding to the included getters.
//
// Change (1) Would be a simple modification -- but if the user has lots of beans
// like this, or if he has legacy code that he cannot modify, then he could use a
// ParameterHandler instead.
//
// Change (2) could potentially require significant changes to the user's design
// architecture. For example, in this particular case, it may be that the user
// only wants to allow changes to departmentName and departmentCode when both
// are changed together. Therefore, he has a changeDepartment(String,String) method,
// but no setDepartmentName(String) or setDepartmentCode(String) methods.  A custom
// ParameterHandler would allow the user to still use this bean as-is.
public class CustomDepartment
{
  private String departmentName;
  private String departmentCode;
  // Other properties

  public CustomDepartment (String departmentName, String departmentCode)
  {
    this.departmentName = departmentName;
    this.departmentCode = departmentCode;
  }
  
  public void changeDepartment (String departmentName, String departmentCode)
  {
    this.departmentName = departmentName;
    this.departmentCode = departmentCode;
  }

  public String getDepartmentName ()
  {
    return departmentName;
  }

  public String getDepartmentCode ()
  {
    return departmentCode;
  }

  // Other methods
}

Example three

The purpose of this example is explained in comments in the code.

package com.samplePackage;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.ibm.pdq.runtime.generator.ParameterHandler;

// This is an example of a custom parameter handler in which the SQL parameters
// are not set based on values already available in the method parameters.  In
// this case, the project length is calcuated by multiplying a value of a property 
// from the ProjectLevel parameter with a value of a property from the AdefUser
// parameter.
public class ProjectLevelParameterHandler implements ParameterHandler
{
  public void handleParameters (PreparedStatement stmt, Object... parameters) throws SQLException
  {
    System.out.println("CDS in parameter handler");
    ProjectLevel projectLevel = (ProjectLevel) parameters[0];
    AdefUser adefUser = (AdefUser) parameters[1];
    
    int numberOfEmployees = adefUser.getNumberOfEmloyees ();
    double lengthInDays = projectLevel.getMinimumProjectLengthInDaysPerDepartmentMember () * numberOfEmployees;
    String workDepartment = adefUser.getWorkDept ();
    
    System.out.println("CDS trying to set parameters");
    stmt.setDouble (1, numberOfEmployees);
    stmt.setDouble (2, lengthInDays);
    stmt.setString (3, workDepartment);
  }
}

Here is the definition of the AdefUser bean.

package com.samplePackage;

import com.ibm.pdq.annotation.Column;

public class AdefUser
{
  private String workDept;
  private int numberOfEmloyees;
  
  public AdefUser (String workDept, int numberOfEmployees)
  {
    this.workDept = workDept;
    this.numberOfEmloyees = numberOfEmployees;
  }
  
  @Column(name="no_of_employees")
  public int getNumberOfEmloyees ()
  {
    return numberOfEmloyees;
  }
  
  public void setNumberOfEmloyees (int numberOfEmloyees)
  {
    this.numberOfEmloyees = numberOfEmloyees;
  }
  
  public String getWorkDept ()
  {
    return workDept;
  }
  
  public void setWorkDept (String workDept)
  {
    this.workDept = workDept;
  }
}

Here is the definition of the ProjectLevel bean.

package com.samplePackage;


public class ProjectLevel
{
  private int projectLevel;
  private double minimumProjectLengthInDaysPerDepartmentMember;
  
  public ProjectLevel(int projectLevel, double minimumProjectLengthInDaysPerDepartmentMember)
  {
    this.projectLevel = projectLevel;
    this.minimumProjectLengthInDaysPerDepartmentMember = minimumProjectLengthInDaysPerDepartmentMember;
  }

  public double getMinimumProjectLengthInDaysPerDepartmentMember ()
  {
    return minimumProjectLengthInDaysPerDepartmentMember;
  }

  public void setMinimumProjectLengthInDaysPerDepartmentMember (double minimumProjectLengthInDaysPerDepartmentMember)
  {
    this.minimumProjectLengthInDaysPerDepartmentMember = minimumProjectLengthInDaysPerDepartmentMember;
  }

  public int getProjectLevel ()
  {
    return projectLevel;
  }

  public void setProjectLevel (int projectLevel)
  {
    this.projectLevel = projectLevel;
  }

}

Feedback