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!!!

Serialize with care

To think serialization meant just implementing the Marker interface Serializable and just get on with life as usual. As if it would be that easy. What fun would that be right? So today i was breaking my web services to a separate EAR because of the need to load balance and fail over web services. In that i had an exception which was propagated from the EJB layer which was remotely deployed. It worked fine when running together within one app server. But in production after deployement it kept giving an error says org.jaxen.VariableContext class not found. I was speding around 1hr trying to figure out what was wrong by checking my application.xml, checking my libraries within the EAR. But i could not find a reason as to why it was giving this error.

After a few mins of googling about there was a JBOSS-JIRA issue mentioned which said that though JBoss AS throws that error, the real reason is that you have a class which is serialized but it does not have the default contructor because of the fact you override the default contructor with your own constructor. After going through my code i found that in one of my classes i had such a scenraio and also i had used ResourceBundle within that class which i had to make transient as well because it is not serializable.

So after adding the default constructor to my DTO which was passed i was able to resolve the issue.

Cheers

Use Arrays.asList() method with caution

Hey guys,

Been a while since the last post. Work has been hectic and i wasnt really in the mood to blog. My apologies. So getting along with it. I just got to know this interesting detail about java's Arrays.asList() method. If you go through the source code you will find out that it will not give you an implementation of the ArrayList class. Because it has an anonymous inner implmentation of the class ArrayList. And hence for example if you consider the following scenrion;

String[] strArr = new String[]{"test","test1};
List<String> strList = Arrays.asList(strArr);
strList.add("test3");

Here at line no 3 you will get an UnsupportedException thrown because that inner class implmentation does not provide all functionalities provided by the normal ArrayList class.

For more info check out this article.