java调用存储过程传number参数_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 pstruct = new ArrayList();

System.out.println(new Date());

for (int i = 0;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());

}