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.
| Code Block | ||
|---|---|---|
| ||
<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.
| Code Block | ||
|---|---|---|
| ||
<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> |
| Info |
|---|
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:
| Code Block | ||
|---|---|---|
| ||
<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.
| Code Block | ||
|---|---|---|
| ||
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 |
EXECUTE_SP_ONLY_RETURN_NO_OUT | Out parameters – Not applicable. The return list has only the return data from the Stored Procedure. |
EXECUTE_SP_RETURN_AND_OUT | Out parameters – Present in the first item in the return list |
| Info |
|---|
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. |