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): CREATE OR REPLACE TYPE NUMBER_ARRAY AS TABLE OF NUMBER; CREATE OR REPLACE PROCEDURE retrieve_mtn_eligibility_state ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_array_mtn IN NUMBER_ARRAY, p_recordset OUT SYS_REFCURSOR ) AS BEGIN OPEN p_recordset FOR SELECT mtn_eligibility_state, mtn_eligibility_state_date, mtn, acct_num, cust_id FROM CUST_ACCT_MTN_STATE WHERE cust_id = p_cust_id AND acct_num = p_acct_num AND mtn IN ( SELECT * FROM TABLE(p_array_mtn) ); --TODO: Verify MTN given has CUST_ACCT_MTN_ORDER.bdy_mtn dbms_output.put_line('This is the content of that array'); FOR i IN 1 .. p_array_mtn.count LOOP dbms_output.put_line(i||'=' || p_array_mtn(i)); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Some other kind of error occurred.'); END; / 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 Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection());
*Create my ArrayDescriptor for my Type (these NUMBER_ARRAY is my TYPE I created previously) ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection);
*Create my parameter, an ARRAY (al fin y al cabo) and we're set! parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray());

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"> <sqlMap namespace="SP"> <parameterMap id="objectParameters" class="java.util.Map"> <parameter property="cust_id" jdbcType="INTEGER" javaType="java.lang.String" mode="IN" /> <parameter property="acc_num" jdbcType="INTEGER" javaType="java.lang.String" mode="IN" /> <parameter property="array_mtn" jdbcType="NUMBER_ARRAY" typeName="NUMBER_ARRAY" typeHandler="mx.com.company.common.db.typehandler.NumberArrayTypeHandlerCallBack" mode="IN"/> <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/> </parameterMap> <resultMap id="resultSetMap" class="mx.com.company.MyDomainObject" > <result column="mtn_eligibility_state" property="mtnEligibilityState" /> <result column="mtn_eligibility_state_date" property="mtnEligibilityStateDate" /> </resultMap> <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!!!