使用jdbc链接oracle，传入数组执行批量数据的操作

存储过程，创建包，就不写了，只有包体

create or replace package body test is
  procedure useType as 
    --声明变量
    v_sql varchar2(1000);
    begin 
      v_sql:='GRANT CREATE ANY TYPE TO PHONE';--授权，phone用户可以创建任何类型
      execute immediate v_sql;
      v_sql:='GRANT CREATE ANY TABLE TO PHONE';--授权，phone用户可以创建任何表
      execute immediate v_sql;
      --创建类型，相当于java中的javabean
      v_sql:='CREATE OR REPLACE TYPE ARRAY_OBJ FORCE AS OBJECT(NAME NVARCHAR2(100),AGE NVARCHAR2(100),GENDER NVARCHAR2(100))';
      execute immediate v_sql;
      v_sql:='CREATE OR REPLACE TYPE ARRAY_LIST AS TABLE OF ARRAY_OBJ';
      execute immediate v_sql;
    /*  
    commit;
    exception 
      when others then 
        rollback;
    */
  end useType;
  
  /*****************************************************************/
  PROCEDURE testType(list_in in ARRAY_LIST,list_out out ARRAY_LIST) as 
  nodata  exception;--自定义异常：没有数据则抛出异常
  v_count number;
  v_exist number;
  v_sql   varchar2(1000);
  v_data  ARRAY_OBJ;
  /*
  v_name  varchar2(100);
  v_age   varchar2(100);
  v_gender  varchar2(100);
  */
  begin 
    list_out:=new ARRAY_LIST();--创建一个数组，用来返回数据
    if list_in.count <1 then 
       raise nodata;--如果传入的没有数据则抛出异常
    end if;
    v_count:=1;--初始化变量
    --判断表是否存在
    select count(*) into v_exist from all_all_tables where owner ='PHONE' and TABLE_NAME='TABLE_BEAN';
    
    if v_exist<>1 then --表不存在则，创建表
      v_sql:='create table table_bean (NAME NVARCHAR2(100),AGE NVARCHAR2(100),GENDER NVARCHAR2(100))';
      execute immediate v_sql;
    end if;
    --v_data:=new ARRAY_OBJ();--创建一个对象，用来存放返回值
    list_out.extend(list_in.count);--给数组扩容
    --循环获取传入的数据，并将数据插入数据库
    while v_count< list_in.count+1 loop
          dbms_output.put_line(list_in(v_count).name);
          insert into table_bean (NAME,age,gender)values(list_in(v_count).name,list_in(v_count).age,list_in(v_count).gender);
          --dbms_output.put_line(v_sql);
          --execute immediate v_sql;
          v_data:=new ARRAY_OBJ(list_in(v_count).name,list_in(v_count).age,list_in(v_count).gender);
          list_out(v_count):=v_data;
          v_count:=v_count+1;
    end loop;
  commit;--提交事务
  exception 
   
    when nodata then 
      list_out.extend(1);--给数组扩容
      v_data:=new ARRAY_OBJ(sqlerrm,sqlerrm,sqlerrm);
      insert into table_bean (NAME,age,gender)values('123','123','123');
      list_out(1):=v_data;
       dbms_output.put_line(sqlerrm);
    when others then 
      list_out.extend(1);--给数组扩容
      v_data:=new ARRAY_OBJ(sqlerrm,sqlerrm,sqlerrm);
      list_out(1):=v_data;
      dbms_output.put_line(sqlerrm);
    rollback;--事务回滚
  end testType;
  
  /*****************************************************/
  PROCEDURE proctestType as 
    list_in ARRAY_LIST;
    list_out ARRAY_LIST;
    v_obj ARRAY_OBJ;
    v_count number;
  begin 
    list_in:=new ARRAY_LIST();
    --list_in.extend(1);
    list_out:=new ARRAY_LIST();
    v_count:=1;
    /*
    while v_count<list_in.count loop
      v_obj:=new ARRAY_OBJ('name'||v_count,'age'||v_count,'gender'||v_count);
      --list_in(v_count):=v_obj;
      v_count:=v_count+1;
    end loop;
    v_count:=1;
    while v_count<list_in.count loop
      dbms_output.put_line('-->'||list_in(v_count).name);    
      v_count:=v_count+1;
    end loop;
    */
    test.testType(list_in,list_out);
    commit;
  end proctestType;
end test;

java代码

import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class ArrayTe {
	static Connection conn=null;
	public static Connection getConn() throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl ", "phone", "phone");
		return conn;
	}
	public ARRAY testType(List<Map<String,Object>> listMap,Connection conn) throws Exception {
		ARRAY arr =null;
		ArrayDescriptor desc = ArrayDescriptor.createDescriptor("ARRAY_LIST", conn);
		STRUCT[] structs = new STRUCT[listMap.size()];
		StructDescriptor structdesc = new StructDescriptor("ARRAY_OBJ", conn);
		for(int i=0;i<listMap.size();i++) {
			Object[] result = {listMap.get(i).get("name"),listMap.get(i).get("age"),listMap.get(i).get("gender")};
			structs[i] = new STRUCT(structdesc, conn, result);
		}
		arr=new ARRAY(desc,conn,structs);
		return arr;
	}
	public static void main(String[] args) throws Exception {
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Map<String,Object> map =null;
		for (int i = 0; i < 100; i++) {
			map = new HashMap<String,Object>();
			map.put("name", "nameMap"+i);
			map.put("age", "ageMap"+i);
			map.put("gender", "genderMap"+i);
			list.add(map);
			if(i==0) {
				break;
			}
		}
		System.out.println("list==>"+list.size());
		Connection conn = getConn();
		CallableStatement st = conn.prepareCall("call test.testType(?,?)");
		ArrayTe t =new ArrayTe();
		ARRAY arr = t.testType(list,conn);
		st.setArray(1, arr);
		st.registerOutParameter(2, OracleTypes.ARRAY, "ARRAY_LIST");
		st.execute();
		ARRAY _arr = ((OracleCallableStatement) st).getARRAY(2);
		System.out.println("_arr==>"+_arr.length());
		Datum[] datas = _arr.getOracleArray();//获取对象  
		for(int i=0;i<datas.length;i++){  
		    //获取属性  这里不清楚怎么获取属性的名称，如果有大神知道，还望大神指点
		    Object[] beanAttributes = ((STRUCT) datas[i]).getOracleAttributes();  
		    System.out.println(((STRUCT) datas[i]).getOracleAttributes());
		    //遍历属性  
		    for(int m=0;m<beanAttributes.length;m++){  
		        System.out.print(beanAttributes[m]+"  ");  
		    }  
		    System.out.println("==================");
		}
		conn.close();
	}
}