在写存储过程中经常会遇到把java中的arrayList转化为oracle中数组,(arrayList中存的是一些java对象)简单总结一下.
例:arrayList中存在一些people对象
1.首先要在数据库中建立相应的java对象和数组,
如: /* 和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 ;
/
2.将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
3./*把转换后的数组加到存储过程中*/
- 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;
- }
4.存储过程中调用
- 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