原文地址：http://mail-archives.apache.org/mod_mbox/ibatis-user-java/200802.mbox/%3C8B243E70CE6BFB438E3CDE03E1E4F698925024@zil01exm62.ds.mot.com%3E
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");
}
}