这是我工作之前遇到的一个需求，Java调用存储过程，入参是集合。踩了一些坑，决定写个干货，和大家分享下。
项目使用的oracle版本：
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.1.0</version>
<scope>system</scope>
<systemPath>${project.basedir}/lib/com/oracle/ojdbc6/11.2.0.1.0/ojdbc6-11.2.0.1.0.jar</systemPath>
</dependency>
具体的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 <T> Object execute( final List <HisBatchThreeTest> 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<HisBatchThreeTest> 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
