Monday, May 17, 2010

Stored proc with ease with Spring

Our current project was doing stored proc calls natively using SQL which in my opinion was very cumbersome and error prone and unable to extend without ease. While going through the Spring documentation i saw that they provide an abstraction for calling Stored procedures. Hence in this aricle i will explain step by step how i integrated Spring's Stored proc capability.

  • First create a class that extends org.springframework.jdbc.object.StoredProcedure

package com.test;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class StoredProcedureTemplate extends StoredProcedure {

public StoredProcedureTemplate(DataSource ds, String SQL, SqlParameter[] inParams, SqlOutParameter[] outParams,boolean isFucntion) {


private void initializeInParams(SqlParameter[] inParams) {
if (inParams != null && inParams.length > 0) {
for (SqlParameter inParameters : inParams) {

private void initializeOutParams(SqlOutParameter[] outParams) {
if (outParams != null && outParams.length > 0) {
for (SqlParameter outParameters : outParams) {

public Map callStoredProc(HashMap map) {
Map out = null;
out = execute(map);
return out;


Here i have written two private methods to handle in register in and out params used in the stored procedure which is called from within the constructor.

  • Then i create a utility class which uses this stored proc class to call respective stored procedures used within your system I have named it as MasterStoreProcUtil because this will be the central point of access to all stored procedures used within the system.

package com.test.master.core.util;

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.driver.OracleTypes;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;

import com.test.StoredProcedureTemplate;
import com.test.master.core.dto.InOutMsgSearchDTO;
import com.test.master.core.dto.InboundMainGridMapper;
import com.jkcs.dcs.master.core.dto.InboundPartGridMapper;

public class MasterStoredProcUtil {

* Do not change the name of this parameter as the spring file refers by this name
private DataSource dataSource;

* Loaded through spring. See storedproc.spring.xml. Contains all stored procedures which are called through Spring.<br>
* We have loaded through spring so that in a later time if you change the procedure name, you only need to change
* the<br>
* the spring config
private Map<String, String> storedProcedures;

private static final String INBOUND_MSG_PROC = "INBOUND_MSG_DETAILS";

public Map getInboundMessageDetails(InOutMsgSearchDTO inoutMsgSearchDTO) throws JkcsCommonsException {

HashMap inParamMap = new HashMap();
if (inoutMsgSearchDTO.getFlightNumber() != null && !inoutMsgSearchDTO.getFlightNumber().isEmpty()) {
inParamMap.put("pc_flight_number", inoutMsgSearchDTO.getFlightNumber());
} else {
inParamMap.put("pc_flight_number", null);

if (inoutMsgSearchDTO.getFlgDate() != null) {
inParamMap.put("pc_flight_date", inoutMsgSearchDTO.getFlgDate());
} else {
inParamMap.put("pc_flight_date", null);

if (inoutMsgSearchDTO.getMsgType() != null && !inoutMsgSearchDTO.getMsgType().isEmpty()) {
inParamMap.put("pc_msg_type", inoutMsgSearchDTO.getMsgType());
} else {
inParamMap.put("pc_msg_type", null);

if (inoutMsgSearchDTO.getStatus() != null && !inoutMsgSearchDTO.getStatus().isEmpty()) {
inParamMap.put("pc_status", inoutMsgSearchDTO.getStatus());
} else {
inParamMap.put("pc_status", null);

inParamMap.put("pc_pgno_start", inoutMsgSearchDTO.getPgStartNumber());
inParamMap.put("pc_pgno_numrecs", inoutMsgSearchDTO.getPgSize());

SqlParameter[] inParams = { new SqlParameter("pc_flight_number", Types.VARCHAR),
new SqlParameter("pc_flight_date", Types.DATE), new SqlParameter("pc_msg_type", Types.VARCHAR),
new SqlParameter("pc_status", Types.VARCHAR), new SqlParameter("pc_pgno_start", Types.NUMERIC),
new SqlParameter("pc_pgno_numrecs", Types.NUMERIC) };

SqlOutParameter[] outParams = {
new SqlOutParameter("pc_tot_recs", Types.NUMERIC),
new SqlOutParameter("pc_msg_info_cur", OracleTypes.CURSOR, new InboundMainGridMapper()),
new SqlOutParameter("pc_messages_cur", OracleTypes.CURSOR, new InboundPartGridMapper()) };
StoredProcedureTemplate storedProcTemplate = new StoredProcedureTemplate(dataSource, storedProcedures
.get(INBOUND_MSG_PROC), inParams, outParams, false);

return storedProcTemplate.callStoredProc(inParamMap);


In this class i have used the above StoredProcedureTemplate class passing in the required parameters. As you can see its very straight forward to lin the IN params required for the stored procedure. For the out params, here i have deinfed as Curosor because in the stored procs im calling currently the data is returned within a SYS cursor.

The best part about using spring here is that you can wrap the data returned from your ref cursor to your own transfer objects. Hence next i will show you how that mapping class looks like. Farely easy;

package com.test.master.core.dto;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.test.master.core.domain.message.InboundMessages;
import com.test.master.core.domain.message.ProcessedInboundMessage;

public class InboundMainGridMapper implements RowMapper {

public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
List<ProcessedInboundMessage> procIbMsgList = new ArrayList<ProcessedInboundMessage>();
ProcessedInboundMessage ibMsg = new ProcessedInboundMessage();
InboundMessages inboundMsg = new InboundMessages();

return procIbMsgList;


This is the mapper class. You get the result set and iterate through it filling your DTO as required. The names referred here are the column names as specified in you ref cursor. I have returned a list of objects in this instance.

And last to wire it all up the spring configuration needed is as follows;

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns=""

- Makes a JNDI DataSource available as bean reference, assuming a J2EE environment.
- By default, "java:comp/env/" will be prepended if not already specified.
<bean id="myDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<property name="jndiEnvironment">
<prop key="java.naming.factory.initial">org.jnp.interfaces.NamingContextFactory</prop>
<prop key="java.naming.provider.url">localhost:1099</prop>

<!-- Defined the parent bean for datasource so that any other bean requiring it can use the parent tag
and re-use this
<bean id="parentDatasource" abstract="true">
<property name="dataSource" ref="myDataSource"/>

This wires the MasterStoreProcUtil class which had the map of all procs available within the system

<bean id="masterStoredProcUtil" class="com.test.master.core.util.MasterStoredProcUtil"
<property name="storedProcedures">


This is the spring configuration required. I have set the parent attribute in the bean id masterStoredProcUtil to link to the data source bean which i defined as abstract. Which is why i put a note in the class file as to not change the instance variable name of the DataSource instance variable.

I have included all proc names within a map so that even at a latter change we need to change the proc name, we only need to change the spring config minimizing any errors.

Thats it guys. If you have any queries, or possible changes, improvements you see pls do leave a comment which i highly appreciate.



  1. To me it kind of seems like the examples that create type-safe calls through StoredProcedure subclasses are much cleaner: you call a type-safe method from the SP subclass.

    The subclass handles in/out parameter declaration, can contain an inner class for mapping (or use a public one if it makes sense, of course). With only a little bit of work and configuration you end up with things like this:

    storedProcSubclass {
        public void declareParameters()
            declareOutParameter(new SqlOutParameter(OUT_CURSOR, OracleTypes.CURSOR, new GroupMapper()));
            declareInParameter(new SqlParameter(IN_BUSINESS_ID, OracleTypes.NUMBER));

        public Collection selectGroups(final long inBusinessId)
            Map resultMap = execute(new HashMap() {{ put(IN_BUSINESS_ID, new NUMBER(inBusinessId)); }});
            return (Collection) resultMap.get(OUT_CURSOR);

    (plus the mapper). The "client" code just calls selectGroups; the parameters are handled outside of the mainline code.

  2. that makes alot of sense... Thx for the suggesstion Dave..agreed 100% on the fact that parameter handling should be separated from the mainline code...