com.ibm.pdq.runtime.handlers
Interface CallHandlerWithParameters<CAL>


public interface CallHandlerWithParameters<CAL>

Processes the results of an SQL stored procedure call and returns an object of type CAL that describes the results.

The default pureQuery behavior for annotated and inline methods executing SQL stored procedure calls is to return all of the query results and OUT and INOUT parameters in an instance of StoredProcedureResult. Additionally, the default behavior is to update Maps and pureQuery beans that were passed to the annotated or inline method to represent OUT and INOUT parameters. For an example, see Data.call(String, Object...). When some other behavior is wanted, an implementation of CallHandlerWithParameters<CAL> can be specified.

Specifying a CallHandlerWithParameters<CAL> implementation causes its handleCall(CallableStatement, Object...) method to be used to process the results of the SQL stored procedure call and to create an object of type CAL that describes the results. The created object is returned from the associated annotated or inline method. The handleCall method can perform other actions as well. For example, if the user wants mutable method parameters to be updated with the values of SQL OUT and INOUT parameters, the parameters must be updated in the handleCall method.

The object of type CAL that is returned by CallHandlerWithParameters<CAL> can contain as much information about the results of the stored procedure call as is wanted. The following examples show possible types for CAL:

Attention: pureQuery calls cstmt.execute() before calling handleCall(CallableStatement cstmt, Object... parameters), so cstmt.execute() must not be called in handleCall(CallableStatement cstmt, Object... parameters).

Example of creating a CallHandlerWithParameters<CAL> implementation

The following example demonstrates the basic syntax for creating a simple CallHandlerWithParameters<CAL> implementation for an SQL stored procedure named bonus_increase with this signature:

    bonus_increase (IN p_bonusFactor DECIMAL (3,2), IN p_bonusMaxSumForDept DECIMAL (9,2),
                    OUT p_deptsWithoutNewBonuses VARCHAR(255), OUT p_countDeptsViewed INTEGER,
                    OUT p_countDeptsBonusChanged INTEGER, OUT p_errorMsg VARCHAR(255))

bonus_increase returns a single query result that contains three columns: employee.workdept, employee.empno, and employee.bonus. The stored procedure alters employee bonuses by multiplying them by the value of p_bonusFactor, as long as the sum of all bonuses for a given department does not exceed p_bonusMaxSumForDept. p_deptsWithoutNewBonuses is a list of departments for which bonuses were not updated because the sum of all bonuses exceeded p_bonusMaxSumForDept.

The example CallHandlerWithParameters<CAL> implementation is called BonusIncreaseCallHandlerWithParameters. Notice that this class has two constructors: one that takes no arguments and one that takes one argument. BonusIncreaseCallHandlerWithParameters creates and returns a Map in which each key is a department number. The value corresponding to each department number is null if bonuses for that department could not be set because the sum exceeded p_bonusFactor. For departments in which bonuses were set, the value is a Map<String,Double> in which the key is an employee number and the value is the updated value of that employee's bonus. The no-argument constructor causes the returned Map to describe all the departments. The constructor that takes an instance of List as an argument allows the user to restrict the Map to describing only the departments specified in the List.

    public class BonusIncreaseCallHandlerWithParameters implements CallHandlerWithParameters<Map<String, Map<String, Double>>> {
      private final List<String> departmentsToReport;
      public BonusIncreaseCallHandlerWithParameters () {
        // null means report all departments
        departmentsToReport = null;
      }
      public BonusIncreaseCallHandlerWithParameters (List<String> departmentsToReport) {
        this.departmentsToReport = departmentsToReport;
      }

      public Map<String, Map<String, Double>> handleCall (CallableStatement cstmt, Object... parameters) throws SQLException {
        // Update mutable OUT parameters
        BonusIncreaseOutputParameters outputParameters = (BonusIncreaseOutputParameters) parameters[0];
        outputParameters.setReturnValue (cstmt.getInt (1));
        outputParameters.setDeptsWithoutNewBonuses (cstmt.getString (4));
        outputParameters.setCountDeptsViewed (cstmt.getInt (5));
        outputParameters.setCountDeptsBonusChanged (cstmt.getInt (6));
        outputParameters.setErrorMsg (cstmt.getString (7));

        // A string that is a comma-separated list of departments requiring manager approval
        String departmentsForWhichBonusesRequireApproval = outputParameters.getDeptsWithoutNewBonuses ();

        // Has columns: employee.workdept, employee.empno, employee.bonus
        ResultSet employeeBonusesResultSet = cstmt.getResultSet ();

        // Create a map with department numbers as keys. For departments in which bonuses were
        // set, make the value a map of employees and their bonuses. For departments in which
        // bonuses cannot be set without manager approval, make the value null.
        Map<String, Map<String, Double>> employeeBonusPairsByDepartmentMap = new HashMap<String, Map<String, Double>> ();
        if (null != employeeBonusesResultSet) {
          while (employeeBonusesResultSet.next ()) {
            String departmentNumber = employeeBonusesResultSet.getString ("workdept");
            if (null == departmentsToReport || departmentsToReport.contains (departmentNumber)) {
              if (null != departmentsForWhichBonusesRequireApproval
                departmentsForWhichBonusesRequireApproval.contains (departmentNumber))
                // Employee is in a department that requires approval for bonuses
                employeeBonusPairsByDepartmentMap.put (departmentNumber, null);
              else {
                String employeeNumber = employeeBonusesResultSet.getString ("empno");
                Double bonus = employeeBonusesResultSet.getDouble ("bonus");
                // Add the department if it is not there already
                if (!employeeBonusPairsByDepartmentMap.containsKey (departmentNumber)) {
                  Map<String, Double> currentDepartmentMap = new HashMap<String, Double> ();
                  employeeBonusPairsByDepartmentMap.put (departmentNumber, currentDepartmentMap);
                }
                // Add the employee to the map for his or her department
                employeeBonusPairsByDepartmentMap.get (departmentNumber).put (employeeNumber, bonus);
              }
            }
          }
        }
        return employeeBonusPairsByDepartmentMap;
      }
    }

Example of specifying a CallHandlerWithParameters<CAL> implementation for an inline method

The following example demonstrates the basic syntax for specifying the created CallHandlerWithParameters<CAL> implementation for an inline method.

    Connection connection = DriverManager.getConnection (...);
    Data data = DataFactory.getData (connection);
    Map<String, Map<String, Double>> bonusesByDepartment;
    List<String> departmentsToReport = Arrays.asList (new String[] { "A00", "B01", "C01", "D01", "E01" });     BonusIncreaseCallHandlerWithParameters handler = new BonusIncreaseCallHandlerWithParameters (departmentsToReport);     BonusIncreaseOutputParameters bonusIncreaseOutputParameters = new BonusIncreaseOutputParameters ();
    Double bonusFactor = ...;
    Double bonusMaxSumForDept = ...;
    bonusesByDepartment = data.call ("{?1.returnValue = call bonus_increase (?2, ?3, ?1.deptsWithoutNewBonuses, ?1.countDeptsViewed, ?1.countDeptsBonusChanged, ?1.errorMsg)}", handler, bonusIncreaseOutputParameters, bonusFactor, bonusMaxSumForDept);

Examples of specifying a CallHandlerWithParameters<CAL> implementation for annotated methods

The following two examples demonstrate the basic syntax for specifying the created CallHandlerWithParameters<CAL> implementation for an annotated method. The two examples assume that the annotated methods are declared in an interface named SampleInterfaceData. In the first example, the user needs the returned Map to describe all the departments, so the no-argument constructor is needed. Since the handler is instantiated with a no-argument constructor, the handler is specified in the @Handler(callHandlerWithParameters=...) annotation. The annotated method could be declared in an interface like this:

    @Call(sql = "{?1.returnValue = call bonus_increase (?2, ?3, ?1.deptsWithoutNewBonuses, ?1.countDeptsViewed, ?1.countDeptsBonusChanged, ?1.errorMsg)}")
    @Handler(callHandlerWithParameters = BonusIncreaseCallHandlerWithParameters.class)
    public Map<String,Map<String,Double>> callBonusIncrease (BonusIncreaseOutputParameters bonusIncreaseOutputParameters, Double bonusFactor, Double bonusMaxSumForDept);

Then, after the pureQuery Generator was used to generate the implementation class for the interface, the method could be invoked like this:

    Connection connection = DriverManager.getConnection (...);
    SampleInterfaceData sampleInterfaceData = DataFactory.getData (SampleInterfaceData.class, connection);
    BonusIncreaseOutputParameters bonusIncreaseOutputParameters = new BonusIncreaseOutputParameters ();
    Double bonusFactor = ...;
    Double bonusMaxSumForDept = ...;
    Map<String, Map<String, Double>> bonusesByDepartment;
    bonusesByDepartment = sampleInterfaceData.callBonusIncrease (bonusIncreaseOutputParameters, bonusFactor, bonusMaxSumForDept);

In the next example, the user needs the returned Map to contain only departments from a particular division, so the implementation must be instantiated with a constructor that takes an argument. As a result, the handler is specified as a parameter to the annotated method. The annotated method could be declared in an interface like this:

    @Call(sql = "{?1.returnValue = call bonus_increase (?2, ?3, ?1.deptsWithoutNewBonuses, ?1.countDeptsViewed, ?1.countDeptsBonusChanged, ?1.errorMsg)}")
    public Map<String,Map<String,Double>> callBonusIncrease (BonusIncreaseOutputParameters bonusIncreaseOutputParameters, Double bonusFactor, Double bonusMaxSumForDept, BonusIncreaseCallHandlerWithParameters callHandlerWithParameters);

Then, after the pureQuery Generator was used to generate the implementation class for the interface, the method could be invoked with an instance of BonusIncreaseCallHandlerWithParameters like this:

    Connection connection = DriverManager.getConnection (...);
    SampleInterfaceData sampleInterfaceData = DataFactory.getData (SampleInterfaceData.class, connection);
    List<String> departmentsToReport = Arrays.asList (new String[] { "A00", "B01", "C01", "D01", "E01" });
    BonusIncreaseCallHandlerWithParameters handler = new BonusIncreaseCallHandlerWithParameters (departmentsToReport);
    BonusIncreaseOutputParameters bonusIncreaseOutputParameters = new BonusIncreaseOutputParameters ();
    Double bonusFactor = ...;
    Double bonusMaxSumForDept = ...;
    Map<String, Map<String, Double>> bonusesByDepartment;
    bonusesByDepartment = sampleInterfaceData.callBonusIncrease (bonusIncreaseOutputParameters, bonusFactor, bonusMaxSumForDept, handler);

See Also:
Handler.callHandlerWithParameters(), Data.call(String, CallHandlerWithParameters, Object...), StoredProcedureResult

Method Summary

Modifier and Type Method and Description
 CAL handleCall(CallableStatement cstmt, Object... parameters)
          Processes the results of an SQL stored procedure call and returns an object of type CAL that describes the results.

 

Method Detail

handleCall

CAL handleCall(CallableStatement cstmt,
               Object... parameters)
               throws SQLException
Processes the results of an SQL stored procedure call and returns an object of type CAL that describes the results.

Attention: pureQuery calls cstmt.execute() before calling handleCall(CallableStatement cstmt, Object... parameters), so cstmt.execute() must not be called in handleCall (CallableStatement cstmt, Object... parameters).

Parameters:
cstmt - a CallableStatement that represents an SQL stored procedure. When this method is called by pureQuery, cstmt has already been executed.
parameters - the parameters that were passed to the associated annotated annotated or inline method. In handleCall, the portions of parameters that represent OUT and INOUT parameters to the SQL stored procedure call can be updated with the new values of the appropriate parameters from the call. Additionally, handleCall can store some or all of parameters in the object of type CAL to be returned. If the method is going to update a parameter, that parameter must be mutable; for example, it cannot be an instance of String or Integer.
Returns:
an object of type CAL that describes the results of the SQL stored procedure call
Throws:
SQLException