java 调用存储过程structdescriptor,Java调用oracle存储过程，集合入参的正确姿势

这是我工作之前遇到的一个需求，Java调用存储过程，入参是集合。踩了一些坑，决定写个干货，和大家分享下。

项目使用的oracle版本：

com.oracle

ojdbc6

11.2.0.1.0

system

${project.basedir}/lib/com/oracle/ojdbc6/11.2.0.1.0/ojdbc6-11.2.0.1.0.jar

具体的Java代码如下：

import oracle.jdbc.OracleTypes;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import oracle.sql.STRUCT;

import oracle.sql.StructDescriptor;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.CallableStatementCallback;

import org.springframework.jdbc.core.CallableStatementCreator;

import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

注：T_ARRAY_VITAL_SIGNS 对应存储过程自定义的对象数组，必须要大写

private Object execute( final List list) {

return jdbcTemplate.execute(new CallableStatementCreator(){

@Override

public CallableStatement createCallableStatement(Connection con) throws SQLException {

String execuSql = "{call ExecuteInterface.saveBatchVitalSign(?,?,?)}";

con = con.getMetaData().getConnection();

ARRAY array = getOracleArray(con, "T_ARRAY_VITAL_SIGNS", list);

CallableStatement cs = con.prepareCall(execuSql);

cs.setArray(1,array);

cs.registerOutParameter(2, OracleTypes.INTEGER);

cs.registerOutParameter(3, OracleTypes.VARCHAR);

return cs;

}

},new CallableStatementCallback(){

@Override

public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {

cs.execute();

int errorcode = cs.getInt(2);

if (errorcode < 0) {

String errormsg = cs.getString(3);

throw new RuntimeException("添加数据失败：" + errormsg);

}

else if (errorcode == 0) {

return "SUCCESS";

}

return "SUCCESS";

}

});

}

定义传给存储过程方法的对象数组结构参数。(注：T_VITAL_SIGNS 对应存储过程自定义的对象,必须要大写)

private ARRAY getOracleArray(Connection con, String Oraclelist,List list) throws SQLException {

ARRAY result = null;

if(list!=null&& list.size() > 0){

StructDescriptor structdesc = new StructDescriptor("T_VITAL_SIGNS", con);

STRUCT[] structs = new STRUCT[list.size()];

Object[] objects = new Object[0];

for (int i = 0; i < list.size();i++) {

objects = new Object[11];

objects[0] = new String(((HisBatchThreeTest)(list.get(i))).getPatientId());

objects[1] = new String(((HisBatchThreeTest)(list.get(i))).getVisitId());

objects[2] = new String(((HisBatchThreeTest)(list.get(i))).getTimePoint());

objects[3] = new String(((HisBatchThreeTest)(list.get(i))).getClassCode());

objects[4] = new String(((HisBatchThreeTest)(list.get(i))).getVitalCode());

objects[5] = new String(((HisBatchThreeTest)(list.get(i))).getVitalSigns());

objects[6] = new String(((HisBatchThreeTest)(list.get(i))).getVitalSignsValue());

objects[7] = new String(((HisBatchThreeTest)(list.get(i))).getUnits());

objects[8] = new String(((HisBatchThreeTest)(list.get(i))).getNurse());

objects[9] = new String(((HisBatchThreeTest)(list.get(i))).getWardCode());

objects[10] = new String(((HisBatchThreeTest)(list.get(i))).getBedLabel());

structs[i] = new STRUCT(structdesc, con, objects);

}

ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,con);

result = new ARRAY(desc, con, structs);

}

return result;

}

注：接收字段若为VARCHAR2，赋值不了给存储过程，需要定义为NVARCHAR2

oracle对象.png

oracle数组 .png