This is one solution for sending Oracle UDT (user defined types) collections as IN and OUT parameters using iBatis. 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 (oh yeah, we have one - trust me.) I am using AppFuse2 (Spring and iBatis 2.3.0). I also had to modify iBatis 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 or, for the very least use the same collection as IN and OUT). 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 - I could not get this to work on a package level decalration) 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): 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. The following code is far from perfect and in general is not generic. It's also a bit cumbersome and is totaly Oracle oriented. If you polish it - better yet, make it generic - post me a copy... 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 MYSCHEMA.EMP_SALARY_PKG.GET_EMP_SALARIES(?,?)} </procedure> </sqlMap> And finally - your 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"); } } I hope this helps and will save some time for other developers... -- View this message in context: http://www.nabble.com/Oracle-UDT-Collections-as-IN-and-OUT-parameters-with-iBatis---Solution-tp15532303p15532303.html Sent from the iBATIS - User - Java mailing list archive at Nabble.com.