java 的List<Object> 用在批量添加
网上的代码
- SQL code
--定义一个与list中各元素的数据类型相同的数据库对象 CREATE TYPE AOBJECT AS OBJECT( aaaa NUMBER(8), bbbb NUMBER(8) ) --定义一个list数据库对象 CREATE TYPE ALIST AS VARRAY(100) OF AOBJECT
- Java code
public static long addRecords(ArrayList list) { Connection con = null; CallableStatement stmt = null; int backVal = 0; try { con = pool.getConnection(); if (con != null) { ARRAY aArray = getArray(con, "AOBJECT","ALIST", list);//该函数调用的第二三个参数必须大写 stmt = con.prepareCall("{call produce1(?,?)}"); //调用存储过程 ((OracleCallableStatement) stmt).setARRAY(1, aArray); stmt.registerOutParameter(2, java.sql.Types.INTEGER); stmt.execute(); backVal = stmt.getInt(2); } } catch (Exception e) { .... } finally { ....//释放数据库连接 } return backVal; } private static ARRAY getArray(Connection con, String OracleObj, String Oraclelist, ArrayList objlist) throws Exception { ARRAY list = null; if (objlist != null && objlist.size() > 0) { StructDescriptor structdesc = new StructDescriptor(OracleObj, con); STRUCT[] structs = new STRUCT[objlist.size()]; Object[] result = new Object[0]; for (int i = 0; i < objlist.size(); i++) { result = new Object[2];//数组大小应和你定义的数据库对象(AOBJECT)的属性的个数 result[0] = new Integer(..); //将list中元素的数据传入result数组 result[1] = new Integer(..); // structs[i] = new STRUCT(structdesc, con, result); } ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, con); list = new ARRAY(desc, con, structs); } // if return list; }
我想知道的是存储过程produce1里面如何写?
------解决方案--------------------
将你的list用sql的游标存储。。。通过循环游标来取出对应的记录即可。。。
------解决方案--------------------
EATE TYPE ALIST AS VARRAY(100) OF AOBJECT
create or replace procedure procedure1(p1 in ALIST ,p2 out number) as
begin
for i in 1..p1.count loop
insert into 表 values(p1(i).aaaa,p1(i).bbbb....);
p2 := i ;
end loop;
end