JAVA调用存储过程--传入集合参数

 

在写存储过程中经常会遇到把java中的arrayList转化为Oracle中数组，（arrayList中存的是一些java对象）简单总结一下．

 

例：arrayList中存在一些people对象。

 

１．首先要在数据库中建立相应的JAVA对象和数组

CREATE TYPE peopleOracleObject AS OBJECT   
(  
　　peopleOracleID NUMBER(8) ,   
　　nameOracle varchar(50) ,   
　　ageOracle Number(3)  
)   
/ 数据库中的array  
CREATE TYPE people_Oracle_LIST AS VARRAY(500) OF peopleOracle ;  
/   

 

２．将JAVA中的arrayList转化

private static ARRAY getOracleArray(Connection con, String Oraclelist,ArrayList objlist) throws Exception {  
　　ARRAY list = null;  
　　if (objlist != null && objlist.size() > 0) {  
　　StructDescriptor structdesc = new StructDescriptor("peopleOracleObject", con);  
　　STRUCT[] structs = new STRUCT[objlist.size()];  
　　Object[] result = new Object[0];  
　　for (int i = 0; i < objlist.size(); i++) {  
　　result = new Object[2];   
　　result[0] = new Long(((people)(objlist.get(i))).getPeopleID());  
　　result[1] = new Long(((people)(objlist.get(i))).getPeopleName());  
　　result[2] = new Long(((people)(objlist.get(i))).getPeopleAge());  
　　structs[i] = new STRUCT(structdesc, con, result);  
}  
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,  
　con);  
list = new ARRAY(desc, con, structs);  
} else {  
　ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, con);  
　STRUCT[] structs = new STRUCT[0];  
　list = new ARRAY(desc, con, structs);  
}  
return list;  
} // function  

 

３．把转换后的数组加到存储过程中

public static int updateADInfo(ArrayList peoleList, int ID){  
Connection con = null;  
CallableStatement stmt = null;  
int backVal;   
　try {  
　　con = pool.getConnection();  
　　if (con != null) {  
　　stmt = con.prepareCall("{call updatePeople(?,?,?)}");  
　　ARRAY adArray = getOracleArray(con, "people_Oracle_LIST", peoleList);  
　　((OracleCallableStatement) stmt).setARRAY(1, adArray);  
　　stmt.setInt(2, ID);   
　　stmt.registerOutParameter(3, java.sql.Types.INTEGER);  
　　stmt.execute();   
} else {   
　backVal = 1;  
}  
} catch (Exception e) {　  
　　e.printStackTrace();   
} finally {  
pool.freeDBResource(con, stmt, null);  
}  
　return backVal;  
}  

 

４．存储过程中调用

CREATE OR REPLACE PROCEDURE NAD_SP_CreateWebCostToAd(  
p_peopleArray IN people_Oracle_LIST,  
p_ID IN number,   
p_out OUT VARCHAR2  
)  
AS  
VpeopleID number(8) :=0;  
VpeopleName varchar(50):=0;  
Vage number(3):=0;  
begin  
for i 1...p_peopleArray.count loop  
　　peopleObj :=p_peopleArray(i);  
　　VpeopleID := peopleObj.peopleOracleID;  
　　VpeopleName := peopleObj.nameOracle;  
　　Vage :=peopleObj.ageOracle;  
.................  
　end loop;  
　commit;  
EXCEPTION   
WHEN OTHERS THEN  
p_out:='-1' || SQLERRM;  
ROLLBACK;   
END;  
/  
SHOW ERRORS