這是我工作之前遇到的一個需求篮赢，Java調(diào)用存儲過程齿椅，入?yún)⑹羌稀２攘艘恍┛悠羝瑳Q定寫個干貨涣脚，和大家分享下。
項目使用的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 對應(yīng)存儲過程自定義的對象數(shù)組寥茫，必須要大寫
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("添加數(shù)據(jù)失斍彩础：" + errormsg);
}
else if (errorcode == 0) {
return "SUCCESS";
}
return "SUCCESS";
}
});
}
定義傳給存儲過程方法的對象數(shù)組結(jié)構(gòu)參數(shù)。（注：T_VITAL_SIGNS 對應(yīng)存儲過程自定義的對象,必須要大寫）
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數(shù)組 .png
