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) {

    setDataSource(ds);
    setFunction(isFucntion);
    setSql(SQL);
    initializeInParams(inParams);
    initializeOutParams(outParams);
    compile();
}

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

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

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>();
      do{
          ProcessedInboundMessage ibMsg = new ProcessedInboundMessage();
          ibMsg.setId(rs.getLong("msg_info_id"));
          ibMsg.setFlightNo(rs.getString("flight_number"));
          ibMsg.setFlightDate(rs.getDate("flight_date"));
          ibMsg.setPartNo(rs.getString("part_no"));
          ibMsg.setMsgType(rs.getString("msg_type"));
          ibMsg.setNoOfRecs(rs.getLong("tot_num_recs"));
          InboundMessages inboundMsg = new InboundMessages();
          inboundMsg.setId(rs.getLong("msg_id"));
          ibMsg.setMsgId(inboundMsg);
          procIbMsgList.add(ibMsg);
      }while(rs.next());

      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="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:jee="http://www.springframework.org/schema/jee"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
    http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd">
 
 

 
 <!--
      - 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">
   <value>myDS</value>
  </property>
   <property name="jndiEnvironment">
   <props>
    <prop key="java.naming.factory.initial">org.jnp.interfaces.NamingContextFactory</prop>
    <prop key="java.naming.provider.url">localhost:1099</prop>
   </props>
  </property>
 </bean>

<!-- 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"/>
 </bean>
 
<!-- 
 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"
  parent="parentDatasource">
  <property name="storedProcedures">
   <map>
    <entry key="INBOUND_MSG_DETAILS">
     <value>DCS_P_INBOUND_MSG_DETAILS</value>
    </entry>
   </map>
  </property>
 </bean>


</beans>

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.


Cheers!!!