java 调存储过程传集合参数_Oracle存储过程入参传入List集合的小例子

importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.util.ArrayList;importoracle.sql.ARRAY;importoracle.sql.ArrayDescriptor;importoracle.sql.STRUCT;importoracle.sql.StructDescriptor;public classTestProInList {//测试main

public static voidmain(String[] args) {newTestProInList().testProInList();

}//调用存储过程并返回执行结果

public voidtestProInList() {//定义需要的变量

Connection ct = null;

CallableStatement cs= null;

ResultSet rs= null;try{//加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");//得到连接

ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "sccot", "tiger");//创建CallableStatement接口

cs = ct.prepareCall("{call TEST_IN_LIST(?,?)}");//给in?赋值ArrayList list = new ArrayList() {

{this.add(new Student(1, "ZhangSan", 23));this.add(new Student(2, "LiSi", 22));this.add(new Student(3, "WangWu", 21));

}

};

ARRAY array= getArray(ct, "STUDENT", "STU_LIST", list);//该函数调用的第二三个参数就是上面自定义的两个类型，在此必须大写//设置入参

cs.setArray(1, array);//注册出参

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);//执行

cs.execute();//获取返回结果

String message = cs.getString(2);

System.out.println(message);//打印结果：//{id:1, name:ZhangSan, age23},{id:2, name:LiSi, age22},{id:3,//name:WangWu, age21},

}catch(Exception e) {

e.printStackTrace();

}finally{//关闭资源

try{if (cs != null) {

cs.close();

}if (rs != null) {

rs.close();

}if (ct != null) {

ct.close();

}

}catch(Exception e) {

e.printStackTrace();

}

cs= null;

rs= null;

ct= null;

}

}private ARRAY getArray(Connection con, String OraObjType, String OraArrType, ArrayListstuList)throwsException {

ARRAY list= null;if (stuList != null && stuList.size() > 0) {//Oracle识别的集合对象,匹配java对象集合

STRUCT[] structs = newSTRUCT[stuList.size()];//Oracle识别的对象模板,匹配单个java对象

StructDescriptor structdesc = newStructDescriptor(OraObjType, con);//遍历stuList,将每个Student对象转换为Oracle可识别的模板对象

for (int i = 0; i < stuList.size(); i++) {//java对象

Student student =stuList.get(i);//数组大小应和你定义的数据库对象(STUDENT)的属性的个数

Object[] oneRow = new Object[3];

oneRow[0] = student.getId(); //将Student对象的每个属性按顺序设置到oneRow数组中

oneRow[1] =student.getName();

oneRow[2] =student.getAge();

structs[i]= newSTRUCT(structdesc, con, oneRow);

}//匹配list

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

list= newARRAY(desc, con, structs);

}returnlist;

}

}classStudent {private intid;privateString name;private intage;//构造函数

public Student(int id, String name, intage) {super();this.id =id;this.name =name;this.age =age;

}//getters/setters(略)

}