Solution: Oracle collections as IN and OUT parameters vis iBatis

[Nir Silverman Tomer Shlomo-BKND43 <BK...@motorola.com>]

After spending a great deal of time in R&D I came up with the following
solution for sending IN and OUT Oracle UDT collections (e.g. nested
tables or arrays). I'm currently using Oracle 10g. My driver version is
10.2.0.2.0 and I had to place the oracle i18n jar (version 10.1.0.2.0.0)
in the class path to solve the '???' datum conversion to string problem.
I am using AppFuse2 (Spring and iBatis 2.3.0). I had to modify 2.3.0 to
allow access to the
com.ibatis.sqlmap.engine.type.JdbcTypeRegistry.setType method. (Only
required if you want OUT nested table).
 
There is not much use for using a nested table as out parameter in my
code... I would much rather use a REF CURSOR for this purpose. However,
for the sake of example and mapping demonstration, I have one i_array IN
nested table and one o_array OUT nested table as parameters to my
procedure.
 
I will try to demonstrate how this is done. First we will declare two
schema scope (not package) types:
 
DROP TYPE EMP_SALARY_TAB;
DROP TYPE EMP_SALARY_REC;
 
CREATE OR REPLACE TYPE EMP_SALARY_REC AS OBJECT (
   EMP_ID                         NUMBER(5),
   EMP_NAME                       VARCHAR2(255),
   START_DATE                     DATE,
   SALARY                         NUMBER   
);
/
 
CREATE OR REPLACE Type EMP_SALARY_TAB AS TABLE OF EMP_SALARY_REC;
/
 
next we will create a small package with one single test procedure
 
CREATE OR REPLACE PACKAGE EMP_SALARY_PKG IS
   PROCEDURE GET_EMP_SALARIES(i_array IN EMP_SALARY_TAB,o_array OUT
EMP_SALARY_TAB);
End EMP_SALARY_PKG;
/
 
CREATE OR REPLACE PACKAGE BODY EMP_SALARY_PKG AS
 
   PROCEDURE GET_EMP_SALARIES(i_array IN EMP_SALARY_TAB,o_array OUT
EMP_SALARY_TAB)
   IS
      emp_salary_rec   OM.EMP_SALARY_REC;
      num_of_months    NUMBER;
      base_salary_usd  NUMBER := 70000;
      annual_bonus_pct NUMBER := 3.5;
      updated_salary   NUMBER;      
   BEGIN
      o_array := i_array;
      FOR idx IN i_array.first()..i_array.last() LOOP
        emp_salary_rec := i_array(idx);
        num_of_months := 24;
        updated_salary := (((num_of_months / 12) * annual_bonus_pct) /
100) + base_salary_usd;
        emp_salary_rec.SALARY := updated_salary;
        o_array(idx) := emp_salary_rec;
      END LOOP;
   EXCEPTION
      WHEN OTHERS THEN
         -- handle errors here...
         dbms_output.put_line('Error: '||substr(1,255,sqlerrm));

   END GET_EMP_SALARIES;
 
END EMP_SALARY_PKG;
/


Now we are ready to begin writing java. First - out POJO (the model used
to transfer data to and from the DB) This is a nasty looking model
because it implements ora.sql.ORAData and ORADataFactory. I basically
copied most of the implementation from the Oracle JPublish help manual
leave aside the standard bean methods used by my framework (AppFuse):
 
package com.mot.nsa.model.oracle;
 
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import oracle.jdbc.OracleTypes;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.Datum;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.STRUCT;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.mot.nsa.model.BaseObject;
 
public class EmpSalary extends BaseObject implements ORAData,
ORADataFactory {
 
    // Class fields
    private Integer empId;
    private String empName;
    private Date startDate;
    private Double salary;
    // JPublish code
    public static final String _SQL_NAME = "OM.EMP_SALARY_REC";
    public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
    protected MutableStruct _struct;
    static int[] _sqlType = { OracleTypes.NUMBER, OracleTypes.CHAR,
OracleTypes.DATE, OracleTypes.NUMBER };
    static ORADataFactory[] _factory = new ORADataFactory[4];
    static final EmpSalary _EmpSalaryFactory = new EmpSalary();
    // logger (if you wish)
    private final Log log = LogFactory.getLog(EmpSalary.class);
    /**
     * 
     */
    private static final long serialVersionUID = -7710368639791237838L;
 
    /* constructor */
    protected EmpSalary(boolean init) {
        if (init) {
            _struct = new MutableStruct(new Object[4], _sqlType,
_factory);
        }
    }
 
    public EmpSalary() {
        this(true);
    }
 
    /* ORAData interface */
    public Datum toDatum(Connection conn) throws SQLException {
        log.info("Calling method toDatum...");
        Datum d = _struct.toDatum(conn, _SQL_NAME);
        return d;
    }
 
    /* ORADataFactory interface */
    public ORAData create(Datum d, int sqlType) throws SQLException {
        return create(null, d, sqlType);
    }
 
    protected ORAData create(EmpSalary o, Datum d, int sqlType) throws
SQLException {
        log.info("Calling method create...");
        if (d == null) {
            return null;
        }
        if (o == null) {
            o = new EmpSalary(false);
        }
        o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
        return o;
    }
 
    public static ORADataFactory getORADataFactory() {
        return _EmpSalaryFactory;
    }
 
    // Getters
    public Integer getEmpId() throws SQLException {
        return (Integer) _struct.getAttribute(0);
    }
 
    public String getEmpName() throws SQLException {
        return (String) _struct.getAttribute(1);
    }
 
    public Date getStartDate() throws SQLException {
        return (Date) _struct.getAttribute(2);
    }
 
    public Double getSalary() throws SQLException {
        return (Double) _struct.getAttribute(3);
    }
 
    // Setters
    public void setEmpId(Integer empId) throws SQLException {
        this.empId = empId;
        _struct.setAttribute(0, this.empId);
    }
 
    public void setEmpName(String empName) throws SQLException {
        this.empName = empName;
        _struct.setAttribute(1, this.empName);
    }
 
    public void setStartDate(Date startDate) throws SQLException {
        this.startDate = startDate;
        _struct.setAttribute(2, this.startDate);
    }
 
    public void setSalary(Double salary) throws SQLException {
        this.salary = salary;
        _struct.setAttribute(3, this.salary);
    }
 
    // Just standard hashCode, equals and toString for POJO's
    @Override
    public int hashCode() {
        return hashCode(this);
    }
 
    @Override
    public boolean equals(Object obj) {
        return equals(this, obj);
    }
 
    @Override
    public String toString() {
        return toString(this);
    }
}

Next we will write the 'EmpSalaryTypeHandlerCallback' which handles this
type:
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import org.apache.commons.dbcp.DelegatingConnection;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry;
import com.my.model.oracle.EmpSalary;
 
public class EmpSalaryTypeHandlerCallback implements TypeHandlerCallback
{
 
    private final Log log =
LogFactory.getLog(EmpSalaryTypeHandlerCallback.class);
    private static final String SCHEMA = "OM";
    private static final String EMP_SALARY_TAB = SCHEMA + "." +
"EMP_SALARY_TAB";
    private static final String EMP_SALARY_REC = SCHEMA + "." +
"EMP_SALARY_REC";
    /**
     * If we need an OUT parameter of type OM.EMP_SALARY_TAB (e.g. our
     * implementation of getResult will be called) we will need to
modify iBatis
     * framework (v2.3.0) to allow access to JdbcTypeRegistry.setType
(currently
     * private -> should be public).
     * 
     * If we only need to sen in a OM.EMP_SALARY_TAB - we do not need to
modify
     * anything and the next static block is not required.
     */
    static {
        JdbcTypeRegistry.setType(EMP_SALARY_REC, OracleTypes.STRUCT);
        JdbcTypeRegistry.setType(EMP_SALARY_TAB, OracleTypes.ARRAY);
    };
 
    @SuppressWarnings("unchecked")
    public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException {
        log.info("calling setParameter...");
        try {
            List<EmpSalary> empSalaries = (List<EmpSalary>) parameter;
            // log.info("Converting list to array...");
            EmpSalary[] recArray = new EmpSalary[empSalaries.size()];
            for (int i = 0; i < recArray.length; i++) {
                recArray[i] = empSalaries.get(i);
            }
            log.info("Converted list to array.");
            setter.getPreparedStatement().getConnection();
            Connection conn =
setter.getPreparedStatement().getConnection();
            if (conn instanceof DelegatingConnection) {
                DelegatingConnection dcon = (DelegatingConnection) conn;
                conn = dcon.getInnermostDelegate();
            }
            conn = (OracleConnection) conn;
            ArrayDescriptor arrayDescriptor =
ArrayDescriptor.createDescriptor(EMP_SALARY_TAB, conn);
            ARRAY array = new ARRAY(arrayDescriptor, conn, recArray);
            setter.setArray(array);
        } catch (SQLException sqle) {
            log.info("SQLException: " + sqle, sqle);
            throw sqle;
        }
    }
 
    public Object getResult(ResultGetter getter) throws SQLException {
        ARRAY array = (oracle.sql.ARRAY) getter.getArray();
        ResultSet rs = array.getResultSet();
        List<EmpSalary> empSalaries = new ArrayList<EmpSalary>();
        while (rs != null && rs.next()) {
            STRUCT struct = (STRUCT) rs.getObject(2);
            Object[] attribs = struct.getAttributes();
            EmpSalary empSalary = new EmpSalary();
            empSalary.setEmpId(((java.math.BigDecimal)
attribs[0]).intValue());
            empSalary.setEmpName((String) attribs[1]);
            empSalary.setStartDate((Date) attribs[2]);
            empSalary.setSalary(((java.math.BigDecimal)
attribs[3]).doubleValue());
            empSalaries.add(empSalary);
        }
        return empSalaries;
    }
 
    /**
     * Nothing here can help us anyway...
     */
    public Object valueOf(String arg0) {
        if (arg0 == null) {
            return new ArrayList<EmpSalary>();
        }
        return arg0;
    }
}
 
Notice: if you want to pass a collection as an OUT parameter - you will
have to modify iBatis 2.3.0 so you can do this:
JdbcTypeRegistry.setType("MYTYPE",OracleTypes.SomeType). Now we can
start iBatis mapping:
 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="DBTestSQL">
    
    <typeAlias alias="empSalaryTypeHandler"
type="com.my.company.dao.ibatis.utils.EmpSalaryTypeHandlerCallback" />
    <typeAlias alias="empSalary" type="com.my.model.oracle.EmpSalary" />
    
    <resultMap id="empSalaryResult" class="empSalary">
       <result property="empId" jdbcType="NUMERIC"
javaType="java.lang.Integer" column="EMP_ID"/>
       <result property="empName" jdbcType="VARCHAR"
javaType="java.lang.String" column="EMP_NAME"/>
       <result property="startDate" jdbcType="TIMESTAMP"
javaType="java.util.Date" column="START_DATE"/>
       <result property="salary" jdbcType="NUMERIC"
javaType="java.lang.Double" column="SALARY"/>
    </resultMap>
        
    <parameterMap id="empSalaryParams" class="java.util.Map">
        <parameter property="iArray"  typeHandler="empSalaryTypeHandler"
mode="IN" />
        <parameter property="oArray" jdbcType="OM.EMP_SALARY_TAB"
typeName="OM.EMP_SALARY_TAB" typeHandler="empSalaryTypeHandler"
mode="OUT" resultMap="empSalaryResult" />
    </parameterMap>
    
    <procedure id="getEmpSalaries" parameterMap="empSalaryParams"
resultMap="empSalaryResult">
        {call OM.EMP_SALARY_PKG.GET_EMP_SALARIES(?,?)}
    </procedure>
    
</sqlMap>

And finally - you dao implementation should look something like this:
 
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.mot.nsa.dao.EmpSalaryDao;
import com.mot.nsa.model.oracle.EmpSalary;
 
public class EmpSalaryDaoiBatis extends GenericDaoiBatis<EmpSalary,
Integer> implements EmpSalaryDao {
 
    public EmpSalaryDaoiBatis() {
        super(EmpSalary.class);
    }
 
    @SuppressWarnings("unchecked")
    public List<EmpSalary> getEmpSalaries(List<EmpSalary> empList) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("oArray", empList);
        params.put("iArray", empList);
        getSqlMapClientTemplate().queryForObject("getEmpSalaries",
params);
        log.info("Params: " + params);
        return (List<EmpSalary>) params.get("oArray");
    }
}