java调用oracle中输入参数是数组类型的函数或者存储过程

这次遇到一个开发需求，提供一个接口，输入参数是数组类型，要通过oracle封装的函数进行调用，返回结果也是一个数组。

记录一下对这种类型的实现方法，备忘。实际名称中用xxx替换了部分值,参考用

oracle端：

FUNCTION    xxx_func(i_a_array   IN cux_b2b_xxx_type, --数组类型参数
                                         i_b        IN VARCHAR2  --其他普通类型参数
                                ) RETURN cux_b2b_xxx_res_type --返回类型

.........

cux_b2b_xxx_type 就是一个数组类型的声明

CREATE OR REPLACE TYPE cux_b2b_xxx_type AS TABLE OF cux_b2b_xxx_rec

cux_b2b_xxx_type 是数组的声明

cux_b2b_xxx_rec是数组具体内容的声明

CREATE OR REPLACE TYPE cux_b2b_xxx_rec AS OBJECT(attribute1 VARCHAR2(240))

as Object 后面就是数组中具体列的说明

cux_b2b_xxx_res_type是返回类型的声明

CREATE OR REPLACE TYPE cux_b2b_xxx_res_type AS TABLE OF cux_b2b_xxx_res_rec

cux_b2b_xxx_res_rec是返回数组中具体列的声明

CREATE OR REPLACE TYPE cux_b2b_xxx_res_rec AS OBJECT
(
  xxx1         VARCHAR2(240), 
  xxx2 VARCHAR2(240), 
  xxx3        NUMBER, 
  xxx4    NUMBER, 
  xxx5          VARCHAR2(4000) 
)

java 部分：

首先，对输入数组的具体类型定义一个类

import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.Datum;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;

public class CuxB2bXxxRecBean implements ORAData {


public static final String _ORACLE_TYPE_NAME = "cux_b2b_xxx_rec".toUpperCase();  //一定是大写,这里的类型是数组中具体列对象的类型


protected MutableStruct _struct;

static int[] _sqlType = { OracleTypes.VARCHAR };//对应输入参数中的具体类型，如果有多列，这里就需要相应的参数个数，位置与列声明的一致，类型也有一致


static ORADataFactory[] _factory = new ORADataFactory[_sqlType.length];


public Datum toDatum(Connection conn) throws SQLException {
_struct.setAttribute(0, this.attribute);//设置参数，与列说明要一致，如果有多个，没有都有设置
return _struct.toDatum(conn, _ORACLE_TYPE_NAME);
}


private CuxB2bXxxRecBean() {
_struct = new MutableStruct(new Object[_sqlType.length], _sqlType, _factory);
}

public CuxB2bInvqtyRecBean(String v){//构造函数，要包含所有列，用于初始化
this();
attribute = v;
}


private String attribute;


public String getAttribute() {
return attribute;
}

}

实际执行,用hibernate封装

public class XxxFunc extends HibernateDaoSupport {

public List<CuxB2bInvqtyResRecBean> queryKuCun(final List<String> items,final String lotNumber) throws Exception{
class Hc implements HibernateCallback<List<CuxB2bXxxResRecBean>> {
public List<CuxB2bXxxResRecBean> doInHibernate(Session session)
throws HibernateException, SQLException {
ConnectionProvider cp = ((SessionFactoryImplementor)session.getSessionFactory()).getConnectionProvider();
Connection conn = cp.getConnection();
CallableStatement cstmt = conn.prepareCall(CallCuxxxxxNewFunSql);
setParam(conn,cstmt,items,orgs,lotNumber,subinventoryCode,locatorId);//设置输入参数
long startPTime = System.currentTimeMillis();
cstmt.execute();
log.debug(" cux_b2b_pkg.cux_xxx_new_fuc 执行耗时  = {}",(System.currentTimeMillis() - startPTime));
ARRAY retArr = (ARRAY)cstmt.getArray(1);//返回结果是数组
cstmt.close();
cp.closeConnection(conn);
return change2CuxB2bXxxResRecBeanList(retArr.getOracleArray());
}
}
return (List<CuxB2bXxxResRecBean>)this.getHibernateTemplate().executeFind(new Hc());
}

参数设置方法

private void setParam(final Connection conn,final CallableStatement cstmt,final List<String> xxx1,final String xxx2) throws SQLException{
C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();//连接池使用了c3p0
OracleConnection connection = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(conn);//转换为oracle的连接
ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("cux_b2b_xxx_type".toUpperCase(), connection);//输入类型设置,这里是数组类型的名称,要大写

cstmt.registerOutParameter(1, OracleTypes.ARRAY,"cux_b2b_xxx_res_type".toUpperCase());//设置输出为数组类型，这里是输出数组类型的名称，不是输出列对应类型的名称，要大写
cstmt.setArray(2, new ARRAY(tabDesc,connection,change2CuxB2bXxxRecBean(xxx1).toArray()));//输入参数设置，进行转换
cstmt.setObject(3, xxx2);

}

参数数组转换为数据库对应的类型数组

private List<CuxB2bXxxRecBean> change2CuxB2bXxxRecBean(List<String> list){
List<CuxB2bXxxRecBean> ret = new ArrayList<CuxB2bXxxRecBean>();
for(String v:list){
ret.add( new CuxB2bXxxRecBean(v));
}
return ret;
}

输出参数转换成对象

private List<CuxB2bXxxResRecBean> change2CuxB2bXxxResRecBeanList(Datum[] dtm) throws SQLException{
List<CuxB2bXxxResRecBean> ret = new ArrayList<CuxB2bXxxResRecBean>(); 
log.debug("dtm.length={}" , dtm.length);
for (int i = 0; i < dtm.length; i++) {
STRUCT st = (STRUCT) dtm[i];
log.debug("st is null = " + (st == null));
if(st != null){
ret.add(change2CuxB2bXxxResRecBean(st.getOracleAttributes()));//转换成对象类
}
}
return ret;
}
CuxB2bXxxResRecBean 是 根据输出对象apps.cux_b2b_xxx_res_rec定义的bean,不在给出具体定义
private CuxB2bXxxResRecBean change2CuxB2bXxxResRecBean(Object[] obj) throws SQLException{
CuxB2bXxxResRecBean v = new CuxB2bXxxResRecBean();
v.setXxx1(getOracle2String(obj[0]));
v.setXxx2(getOracle2String(obj[1]));
v.setXxx3(NullCheck(getOracle2BigDecimal(obj[2])));//数值类型转换成字符串
v.setXxx4(NullCheck(getOracle2BigDecimal(obj[3])));
v.setXxx5(getOracle2String(obj[4]));
return v;
}

private String getOracle2String(Object v) throws SQLException{//oracle类型转换
return (v == null) ? (null) : ((oracle.sql.CHAR)v).getString();
}


private BigDecimal getOracle2BigDecimal(Object v) throws SQLException{
return  (v == null) ? (null) : ((oracle.sql.NUMBER) v).bigDecimalValue();
}

private String NullCheck(Object v){
if(v != null){
return v.toString();
}
return "";
}

另外，对于函数，输入参数是普通参数，返回类型是数组的类型，sql 可以写成 select * from table(xxx(?));

rs = cstmt.executeQuery();使用时，按照ResultSet直接进行处理就可以了