java中调用存储过程并传递list集合参数的方法

sql:

CREATE OR REPLACE TYPE test as object(
FID NUMBER,  
NUM nVARCHAR2(30),  
TESKID NUMBER, 
CID NUMBER,
USERCODE nVARCHAR2(100)
)

 

CREATE OR REPLACE TYPE test_list as table of test

 

CREATE OR REPLACE PROCEDURE inserttenumber(
                       v_rec_class_list    IN test_list)is
    v_rec_class test;
    v_count number;
begin
    v_count := v_rec_class_list.count;
    for i in 1..v_count
       loop
          v_rec_class:=v_rec_class_list(i);
          insert into te_number(id,fid,NUM,teskid,STATE,cid,user_code)values(tenumberseq.nextval,v_rec_class.fid,v_rec_class.NUM,v_rec_class.teskid,'-2',v_rec_class.cid,v_rec_class.USERCODE);
          
       end loop;
end inserttenumber;

 

 

java

 

Connection con = null;
  CallableStatement cstmt = null;
  try {
   ApplicationContext  applicationContext=new ClassPathXmlApplicationContext("classpath:spring/applicationContext-resource.xml");
   DataSource datasource=(DataSource)applicationContext.getBean("dataSource");
   List list = new ArrayList();
   for (int i = 0; i < sheets.getRows(); i++) {
    Cell c01 = sheets.getCell(1, i);
    Cell c00 = sheets.getCell(0, i);
    if(!"".equals(c01.getContents())&&!"".equals(c00.getContents())){
     total++;
     Object[] record = new Object[5];
                 record[0] = tn.getFid();
                 record[1] = c01.getContents();
                 record[2] = tn.getTeskId();
                 record[3] = tn.getCid();
                 record[4] = c00.getContents();
                 list.add(record);
    }
    if(i%1000==0){
     con=(Connection) DataSourceUtils.getConnection(datasource);
     System.out.println(con.hashCode()+"======="+list.size());
     tnManager.test1(list,con);
     list.clear();
     System.out.println(con.isClosed());
    }
   }
   System.out.println("============"+list.size());
   con=(Connection) DataSourceUtils.getConnection(datasource);
   tnManager.test1(list,con);
   con.close();
   tt.setCount(total);
   md.addObject("total",total);
   md.addObject("error",tf.getCount()-total);
  } catch (Exception e) {
   System.out.println("=============新增任务，导入号码文件错误！！！");
      e.printStackTrace();
  }
  closeExcel(rwb);

 

===================

public void test1(List list,Connection con) throws Exception{
  StructDescriptor recDesc = StructDescriptor.createDescriptor("TEST", con);
        ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
        System.out.println(new Date());
        for (int i = 0;i<list.size();i++) {
    Object[] record = (Object[])list.get(i);
                STRUCT item = new STRUCT(recDesc, con, record);
                pstruct.add(item);
   }
            ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("TEST_LIST", con);
            ARRAY vArray = new ARRAY(tabDesc, con, pstruct.toArray());
            System.out.println(new Date());
            CallableStatement cstmt = null;
            cstmt = (CallableStatement) con.prepareCall("{call inserttenumber(?)}");
            System.out.println(new Date());
            cstmt.setArray(1, vArray);
            System.out.println(new Date());
            cstmt.execute();
            System.out.println(new Date());
            con.commit();
            cstmt.close();
   con.close();
   System.out.println(new Date()+"==========执行完成"+con.isClosed());
 }