JDBC调用存储过程，传入list参数，插入或更新数据。

注：这里用的事jdbc方式加载驱动获取的connection，如果是jndi等获取datasource方式，会报connection转型错误，因为需要操作oracle自定义数组类型，需要oracle.jdbc.OracleConnection。

首先，需对需传入的list参数做数据类型的封装，在数据库建立自定义类型

CREATE OR REPLACE TYPE imgObject AS OBJECT

(

imgCODE VARCHAR2(14),

  PATH          VARCHAR2(200),

    CONSTRUCTOR FUNCTION  imgObject

 (

imgCODE VARCHAR2 DEFAULT NULL,

PATH          VARCHAR2 DEFAULT NULL

  ) RETURN SELF AS RESULT

);

CREATE OR REPLACE TYPE img_LIST AS VARRAY(2000) OF imgObject;--参数array

第二步提供java数据集合到oracle数组类型的转换方法

 

            private static ARRAY getOracleArray(Connection con, String Oraclelist,

                    Listobjlist) throwsException {

                ARRAY list = null;

                if (objlist != null && objlist.size() > 0) {

                    StructDescriptor structdesc = newStructDescriptor(

                            "imgObject", con);

                    STRUCT[] structs = newSTRUCT[objlist.size()];

                    // 　　Object[] result = new Object[0];

                    int i = 0;

                    for (Titemvenfile img : objlist) {

                        Object[] result = newObject[2];

                        result[0] = img.getimg_code();

                        result[1] = img.getPath();

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

                        i++;

                    }

                    ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,

                            con);

                    list = newARRAY(desc, con, structs);

                } else{

                    ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,

                            con);

                    STRUCT[] structs = newSTRUCT[0];

                    list = newARRAY(desc, con, structs);

                }

                return list;

            }

 

第三步,存储过程的建立

CREATE OR REPLACE PROCEDURE updateImagels(imgArray IN img_LIST,p_out OUT VARCHAR2)

AS

img_CODE VARCHAR2(14);

img_PATH          VARCHAR2(200);

imgObj imgObject;

v_count number;

UPLOAD_DATE date:=sysdate;

i number;

begin

i:=0;

for i in 1..imgArray.count loop

     imgObj:=imgArray(i);

     img_CODE:=imgObj.img_CODE;

     img_PATH:= imgObj.img_PATH;

     DBMS_OUTPUT.put_line(i);

     DBMS_OUTPUT.put_line(img_CODE);

select count(*) into v_count from  img_tb img

            where img.img_code= img_CODE;

 

     --执行插入或更新

     if v_count=0 then

     insert into img_tb(img_code,img_path)values(img_CODE,img_PATH);

     else

     update img_tb t set t.img_path=img_path where t.img_code=img_code

     end if;

 

end loop;

commit;

  p_out:='1';

EXCEPTION

WHEN OTHERS THEN

p_out:='-1' || SQLERRM;

ROLLBACK;

END updateImagels;

 

第四步，java调用

public staticintupdateImgInfo(Connection con,List imageLs) {

                CallableStatement stmt = null;

                boolean backVal=false;

                try {

                    // con = pool.getConnection();

                    if (con != null) {

                        stmt = con.prepareCall("{call updateImagels(?,?)}");

                        ARRAY adArray = getOracleArray(con, "img_LIST",imageLs);

                        ((OracleCallableStatement) stmt).setARRAY(1, adArray);

                        stmt.registerOutParameter(2, java.sql.Types.INTEGER);

                        stmt.execute();

                    }

                } catch(Exception e) {

                    e.printStackTrace();

                } finally{

                    // pool.freeDBResource(con, stmt, null);

                }

                //TODO 返回值的获取

            }