Execute Stored Procedures

As explained earlier, Stored Procedures provide a different scenario where the result can be provided as an Out parameter or as a Return value from the procedure. The key to approaching this is on how the parameter map is defined and return map is defined.

All out parameters are configured through the parameter map while the return result set if any are mapped through the result map.

Let us take an example of a stored procedure that fetches employee details that takes 1 in parameter (emp id), has 1 out parameter and returns a result set. The first step would be to setup the parameter map.

<parameterMap id="EMP_SP_PARAM_MAP" class="java.util.HashMap">
<parameter property="EmployeeId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="NumRecords" jdbcType="NUMBER" javaType="java.lang.Long" mode="OUT" />
</parameterMap>

The attribute mode indicates the parameter direction. Refer to the iBatis documentation for more details on the usage of this attribute. The next step is to configure the result map.

<resultMap id="EMPLOYEE_SELECT_RESULT_MAP" class="java.util.HashMap">
<result property="EmployeeId" nullValue="" column="EMP_ID" javaType="java.lang.String" jdbcType="NUMBER"/>
<result property="EmployeeName" nullValue="" column="EMP_NAME" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result property="dtBirthDate" nullValue="" column="DOB" javaType="java.util.Date" jdbcType="DATE"/>
<result property="mobileNum" nullValue="" column="MOB_NO" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap>

If you note, this is same as that provided to the normal select query earlier. A Result map can be reused across multiple query models as the result map typically represents a business object and not just a record of a table.

The definition of the query model for the stored procedure invocationis as follows:

<procedure id="EMPLOYEE_EXECUTE_SP_RETURN_AND_OUT_MYAPP" parameterMap="EMP_SP_PARAM_MAP" resultMap="EMPLOYEE_SELECT_RESULT_MAP">
{ call GETEMP_SP(?, ?)}
</procedure>

Now the actual code part. This is similar to that done for any other database request.

try { 
DatabaseRequest dbRequest = new DatabaseRequest(); 
dbRequest.setDataAccessMapKey("EMPLOYEE");
dbRequest.setOperation(DatabaseConstants.EXECUTE_SP_RETURN_AND_OUT)
dbRequest.setOperationExtension("MYAPP"); 
//Create the parameters that needs to be passed to the Stored procedure
HashMap paramData = new HashMap();
paramData.put("EmployeeId", queryEmpId); 
dbRequest.setData(paramData); //Add the parameters to the request 
DatabaseResult dbResult = dbRequest.execute(); //Execute the request. 
List<HashMap> returnList = (List<HashMap>)dbResult.execute.getReturnedList(); 
//The first record is the params that was passed augmented with the values from the out parameters
if (returnList.size() > 0) {
HashMap outParamData = returnList.remove(0);
logger.debug("out data value = " + outParamData.get("NumRecords"));
logger.debug("selected records returned by SP is " + returnList);
} 
} catch (DatabaseException dbException) {
//Handle the exception appropriately.
}

There is a relation to the operation set and the structure of the result list –

Operation	Equivalent Support in DAF
EXECUTE_SP_NO_RETURN_NO_OUT	No Return and out parameter. Behavior of the Returned list is undefined.
EXECUTE_SP_NO_RETURN_ONLY_OUT	Out parameters – Present in the first item in the return list Return data – Not applicable. The return list has only one record corresponding to the out parameters.
EXECUTE_SP_ONLY_RETURN_NO_OUT	Out parameters – Not applicable. The return list has only the return data from the Stored Procedure. Return data – This is same as the Return list.
EXECUTE_SP_RETURN_AND_OUT	Out parameters – Present in the first item in the return list Return data – All items from position 1 onwards in the list is the result set.

The example uses a typecast to List<HashMap> with an implicit assumption that the result object structure is a HashMap. It is advised to avoid using Generics when operating with EXECUTE_SP_RETURN_AND_OUT for cases where the result map is done against a POJO / Bean / VO / BO class rather than Map. In such a scenario, the first record in the list will contain a HashMap while the remaining ones will be objects of the corresponding POJO / Bean / VO / BO.