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 Map
s 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.
- For an inline method, an instance of a
CallHandlerWithParameters<CAL>
implementation can be specified by using the methodData.call(String, CallHandlerWithParameters, Object...)
. - For an annotated method, a
CallHandlerWithParameters<CAL>
implementation can be specified in one of two ways. - An implementation that can be instantiated by using a public no-argument constructor can be specified by using
the
@Handler(callHandlerWithParameters=...)
annotation. In this approach, pureQuery creates a single instance of the implementation, and uses that instance every time the annotated method is invoked. - An implementation can be specified as a parameter in an annotated method declaration. An instance of the
implementation is then passed as an argument to the method at runtime. This approach should be used only when
necessary because performance is slightly better when handlers are specified by using the
@Handler(callHandlerWithParameters=...)
annotation. When one or more handlers are provided as parameters to an annotated method, the handlers must be the last parameters to the method.
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
:
-
CAL
can benull
. -
CAL
can resembleStoredProcedureResult
and contain information such as the returned query results and the SQL stored procedure OUT and INOUT parameters. -
CAL
can be a completely different type of object.
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);
Handler.callHandlerWithParameters()
,
Data.call(String, CallHandlerWithParameters, Object...)
,
StoredProcedureResult
Method Summary
Modifier and Type | Method and Description |
---|---|
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
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
. CAL
that describes the results of the SQL stored procedure call
CAL
that describes the results.Attention: pureQuery calls
cstmt.execute()
before callinghandleCall(CallableStatement cstmt, Object... parameters)
, socstmt.execute()
must not be called inhandleCall (CallableStatement cstmt, Object... parameters)
.