Utilities Blog by JuCa Cruz: Stored Procedure

Friday, September 17, 2010

Pass Collection to Stored Procedure using iBatis

Today I had a really crazy idea: "Why not making my query work with a list of parameters like I do with Hibernate using the operator IN???"
It sounds simple, Isn't it??? Ok, then try to do it with an Stored Procedure in Oracle :P
Then, this is my Stored Proc (I had to create a Type to have support for Arrays in Oracle, because an Array is not a simple-provided data type): As you can see, my dear visitor, I'm expecting some simple parameters and I'm returning a ResultSet, really straight forward, but the problem comes with the Array of NUMBER (that's why I created my

TYPE NUMBER_ARRAY AS TABLE OF NUMBER

)

The solution to this problem is easier than it seems, just create a Handler dude!!!

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

public class NumberArrayTypeHandlerCallBack implements TypeHandlerCallback {
 
 @SuppressWarnings("unchecked")
 public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
  if(parameter == null) {
   setter.setNull(Types.ARRAY);
  }
  else if(parameter instanceof ArrayList) {
   Statement stmt = setter.getPreparedStatement();
   Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection());
   
   ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection);
   parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray());
  }
  setter.setObject(parameter);
 }
 
 public Object getResult(ResultGetter getter) throws SQLException {
  return null;  
 }
 
 public Object valueOf(String arg0) {
  return null;
 }

}

The magic begins when I transform the Connection (because I have to work at the connection level) provided by the Application Server (WebSphere on this case - If this happens to me on another App Server or with another Connection Pool, I'm going to be in trouble, because the issue will be how to retrieve a Native java.sql.Connection, but that will be another story).
Let's analyze this monster:
* Get Native Connection
*Create my ArrayDescriptor for my Type (these NUMBER_ARRAY is my TYPE I created previously)
*Create my parameter, an ARRAY (al fin y al cabo) and we're set!

Finally this is my mapping file for my stored procedure

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<procedure id="retrieve_mtn_eligibility_state" parameterMap="objectParameters" resultMap="resultSetMap" >  
  {call ${env.qualifier}.retrieve_mtn_eligibility_state(?,?,?,?)}
 </procedure>

</sqlMap>

And my code lived happily ever after!!! Abur!!!