Oracle存储过程传入List<object>参数

--第一步，创建数据库对象
CREATE OR REPLACE TYPE param_object is object(
  configId nvarchar2(64),
  evaluateScore number(18,2),
  evaluateLevel nvarchar2(64)
); 


--第二步，创建数据库list并且与数据库对象挂关系
create type param_array as table of param_object;


--第三步，创建存储过程将list<object>
create or replace procedure PRO_UPDATE_EVALUATE_SCORE(paramList in param_array,
                                                      result_id out varchar2) is
begin
  for i in 1 .. paramList.count loop
    update SCM_TEST
       set CONFIGID      = paramList(i).configId,
           EVALUATESCORE = paramList(i).evaluateScore where
           EVALUATELEVEL = paramList(i).evaluateLevel;
           result_id := i;
  end loop;
  commit;
end PRO_UPDATE_EVALUATE_SCORE;

package com.atguigu.springdata.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import com.sun.org.apache.xerces.internal.impl.dtd.models.DFAContentModel;

import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class Tesst {
	// 数据库连接
	private static final String connectionURL = "jdbc:oracle:thin:@127.0.0.1:1521/oracle11g";
	private static final String userID = "oracle11g";
	private static final String userPassword = "oracle11g";
	private static final String driver_class = "oracle.jdbc.driver.OracleDriver";

	public void runTest() {
		//组装需要的参数List<String[]> String[] length 要和数据库的自定对象PARAM_OBJECT个数一样
		String chars = "abcdefghijklmnopqrstuvwxyz";
		ArrayList list = new ArrayList<>();
		for(int i=0;i<2000;i++){
			String c = chars.charAt((int)(Math.random() * 26))+"";
			String[] values = { i + 1 + "", i + 1 + "",c.toUpperCase()};
			list.add(values);
		}
		   
		   
		Connection con = null;
		OracleCallableStatement stmt = null;
		try {
			Class.forName(driver_class).newInstance();
			con = DriverManager.getConnection(connectionURL, userID,
					userPassword);
			
			ARRAY aArray = getArray(con, "PARAM_OBJECT","PARAM_ARRAY", list,11);
			
			System.out.println("开始时间："+  new Date());
			stmt = (OracleCallableStatement) con.prepareCall("{call PRO_UPDATE_EVALUATE_SCORE(?,?)}");
			
			stmt.setARRAY(1, aArray);
//			stmt.setArray(1, aArray);
			stmt.registerOutParameter(2, Types.VARCHAR);
			
			stmt.execute();
			String string = stmt.getString(2);
			System.out.println("结束时间："+  new Date());
			System.out.println("输出参数："+string);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 将java数组转换成数据库数组
	 * @param con			原生jdbc链接，链接池的链接不行
	 * @param OracleObj		数据库自定义对象
	 * @param Oraclelist	数据库自定义数组
	 * @param objlist		需要的参数
	 * @param paramNum		自定义对象的个数
	 * @return	
	 * @throws Exception
	 */
	private ARRAY getArray(Connection con, String OracleObj,

	String Oraclelist, ArrayList objlist,int paramNum) throws Exception {

		ARRAY list = null;


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

			StructDescriptor structdesc = StructDescriptor.createDescriptor(OracleObj,con);
			
			STRUCT[] structs = new STRUCT[objlist.size()];

			Object[] result = new Object[0];

			for (int i = 0; i < objlist.size(); i++) {
				Object[] object = (Object[]) objlist.get(i);
				String[] str = (String[]) objlist.get(i);
				
				result = new Object[paramNum];

				// 数组大小应和你定义的数据库对象(AOBJECT)的属性的个数
				for(int j=0;j<paramNum;j++){
					result[j] = str[j];
				}

				// 将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);
		}


		return list;

	}

	public static void main(String[] args) {
		Tesst testListToProcedure = new Tesst();
		testListToProcedure.runTest();
	}

}

注：自定义对象里面的字符必须要使用nvarchar2，不然java代码将字符串的格式封装不进去。链接要使用jdbc原生链接（Connection）才能对自定义对象和数组的转换，使用链接池转换不过来。如果谁使用了连接池成功了麻烦您评论下如何操作的，谢谢！