데이터 구조의 값을 SQL문에 전달

pureQuery에는 어노테이션이 있는 메소드 또는 인라인 메소드에 전달하는 매개변수를 SQL문의 매개변수에 대한 값으로 사용하기 위한 기본 규칙이 있습니다. 또한 pureQuery에는 OUT 및 INOUT 매개변수를 등록할 때 전달된 매개변수를 사용하기 위한 기본 규칙이 있습니다. 어노테이션이 있는 메소드를 사용하는 경우, 이러한 기본 규칙을 ParameterHandler<T> 인터페이스의 구현으로 겹쳐쓸 수 있습니다.

ParameterHandler<T> 인터페이스에는 handleParameters()라는 한 가지 메소드만 있습니다. 이 메소드를 호출할 때 pureQuery는 어노테이션이 있는 메소드에 전달한 매개변수와 함께 PreparedStatement 오브젝트를 전달합니다. 이 오브젝트는 SQL문을 실행하는 데 사용되었습니다.

ParameterHandler<T> 오브젝트를 사용하여 SQL문의 매개변수 값을 설정하고 모든 OUT 또는 INOUT 매개변수를 등록하십시오. java.sql.PreparedStatement 인터페이스에 대한 정보는 사용자의 Java SDK에 대한 Javadoc을 참조하십시오.

예제 1

이 예제의 용도는 코드의 주석에 설명되어 있습니다.

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);
  }

}

예제 2

이 예제의 용도는 코드의 주석에 설명되어 있습니다.

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 ()); 
  }

}

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
}

예제 3

이 예제의 용도는 코드의 주석에 설명되어 있습니다.

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);
  }
}

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;
  }
}

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;
  }

}

피드백