java操作Oracle数据库——ARRAY、TABLE类型批量入库

欢迎技术交流。 QQ：138986722

首先是我的数据库表(PLSQL操作)

create table TEST_TABLE
(
  yid      NUMBER,  
  ytel     VARCHAR2(50), 
  yanumber VARCHAR2(50)  
);

这里记录两种方式：不管哪种方式、一定要记得导入orai18n.jar否则一遇到字符串就乱码、添加不到数据

点击下载orai18n.jar

第一种：
在数据库中建立相应的java对象(Oracle中的类型)和数组.

CREATE OR REPLACE TYPE yOracleObject(类型名称) AS OBJECT(
       yid number,ytel varchar2(50),yanumber varchar2(50)
);

数组：

CREATE OR REPLACE TYPE y_Oracle_LIST(数组名称) AS VARRAY(5000) OF yOracleObject(类型名称);

创建完毕后在Types下面可以看到创建好的类型。

存储过程：

CREATE OR REPLACE PROCEDURE proc_test_new(y_Array IN y_oracle_list,
                                          p_out   OUT NUMBER) AS
  v_yID     number;
  v_yTel    varchar(50);
  v_anumber varchar(50);
  v_type    yoracleobject;
begin
  FOR I IN 1 .. y_Array.COUNT LOOP
    v_type    := y_Array(i);
    v_yID     := v_type.yid;
    v_yTel    := v_type.ytel;
    v_anumber := v_type.yanumber;
    insert into test_table values (v_yID, v_yTel, v_anumber);
  end loop;
  commit;
  p_out := 0;
EXCEPTION
  WHEN OTHERS THEN
    p_out := -1;
    ROLLBACK;
END;

java代码：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import com.chinaboxun.ordermanager.core.util.DbUtil;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
@SuppressWarnings("unchecked")
public class TestProc {

	public static void main(String[] args) {
		ArrayList arrayL = new ArrayList();
		TestBean t = new TestBean();
		t.setYid(1);
		t.setYtel("1236");
		t.setYanumber("骚年");
		arrayL.add(t);
		TestBean t1 = new TestBean();
		t1.setYid(2);
		t1.setYtel("1236");
		t1.setYanumber("骚年");
		arrayL.add(t1);  
		TestBean t2 = new TestBean();  
		t2.setYid(3);
		t2.setYtel("1236");
		t2.setYanumber("骚年");
		arrayL.add(t2);
		TestBean t3 = new TestBean();
		t3.setYid(4);
		t3.setYtel("1236");
		t3.setYanumber("骚年");
		arrayL.add(t3);
		TestBean t4 = new TestBean();
		t4.setYid(5);
		t4.setYtel("1236");
		t4.setYanumber("骚年");
		arrayL.add(t4);
		try {
			/*
			 * 记得判断一下list集合的大小、如果集合大于你在数据设置的数组大小了、那么就要分批次提交
			 * 我的是y_Oracle_LIST AS VARRAY(5000) 
			 * 那么当list集合的值等于5000的时候就入库了、
			 * 然后剩下的数据又从新用一个list来装、在继续判断......
			 * 这里只是简单的演示、就不具体操作判断了
			 */
			int backVal = newTest(arrayL);
			System.out.println(backVal==0?"成功!":"失败!");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 将java中的arrayList转化
	 * @param con 数据库连接对象
	 * @param Oraclelist 数据数组类型名称
	 * @param objlist 要存储的list对象
	 * @return oracle.sql.ARRAY
	 * @throws Exception
	 */
	private static ARRAY getOracleArray(Connection con, String Oraclelist,
			ArrayList objlist) throws Exception {
		ARRAY list = null;
		if (objlist != null && objlist.size() > 0) {
			/**
			 * 必须大写类型名称
			 * 否则会报错：java.sql.SQLException: 无效的名称模式: M_ORDER.yoracleobject
			 */
			StructDescriptor structdesc = new StructDescriptor(
					"YORACLEOBJECT", con);
			STRUCT[] structs = new STRUCT[objlist.size()];
			Object[] result = new Object[0];
			for (int i = 0; i < objlist.size(); i++) {
				result = new Object[3]; 
				TestBean t = (TestBean)(objlist.get(i));
				result[0] = t.getYid();
				result[1] = t.getYtel();  
				result[2] = t.getYanumber(); 
				/*
				 * 一定要记得导入orai18n.jar
				 * 否则一遇到字符串就乱码、添加不到数据
				 */
				structs[i] = new STRUCT(structdesc, con, result);
			}
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
					con);
			list = new ARRAY(desc, con, structs);
		} else {
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
					con);
			STRUCT[] structs = new STRUCT[0];
			list = new ARRAY(desc, con, structs);
		}
		return list;
	} 
	
	/**
	 * 入库
	 * @param peoleList 要存储的list对象
	 * @return
	 * @throws SQLException
	 */
	public static int newTest(ArrayList peoleList) throws SQLException{
		Connection con = null;
		CallableStatement stmt = null;
		int backVal = -1; 
		try {
			DbUtil d = new DbUtil();
			con = d.getCon();
			if (con != null) {
				stmt = con.prepareCall("{call proc_test_new(?,?)}");
				ARRAY adArray = getOracleArray(con, "Y_ORACLE_LIST",peoleList);
				((OracleCallableStatement) stmt).setARRAY(1, adArray);
				stmt.registerOutParameter(2, java.sql.Types.INTEGER);
				stmt.execute(); 
				backVal = stmt.getInt(2);
			}
		} catch (Exception e) {
			e.printStackTrace(); 
		} finally {
			if(stmt!=null){
				stmt.close();
			}
			if(con!=null){
				con.close();
			}
		}
		return backVal;
	}
}

第二种：不用判断list集合大小、不用考虑数组长度的做法就是用table。
使用的类型：你要新增的数据有多少字段就添加相应个数的类型

create or replace type i_table is table of number;
create or replace type t_table is table of varchar2(30);
create or replace type a_table is table of varchar2(30);

存储过程:

create or replace procedure pro_forall_insert(v_1 i_table,
                                              v_2 t_table,
                                              v_3 a_table) as
begin

  forall i in 1 .. v_1.count
    insert into test_table values (v_1(i), v_2(i), v_3(i));
END;

java代码：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import com.chinaboxun.ordermanager.core.util.DbUtil;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
@SuppressWarnings("unchecked")
public class TestProc {

	public static void main(String[] args) {
		Connection con = null;
		CallableStatement cstmt = null;
		oracle.sql.ArrayDescriptor a = null;
		oracle.sql.ArrayDescriptor b = null;
		oracle.sql.ArrayDescriptor c = null;
		DbUtil d = new DbUtil();
		con = d.getCon();
		
		if (1 == 1 )
		{
			Object[] s1 = new Object[10000];
			Object[] s2 = new Object[10000];
			Object[] s3 = new Object[10000];

			for (int i = 0; i < 10000; i++) {
				s1[i] = new Integer(i);
				s2[i] = "ttt"+i;
				s3[i] = "aaa"+i;
			}
			try {
				a = oracle.sql.ArrayDescriptor.createDescriptor("I_TABLE", con);
				b = oracle.sql.ArrayDescriptor.createDescriptor("T_TABLE", con);
				c = oracle.sql.ArrayDescriptor.createDescriptor("A_TABLE", con);
				ARRAY a_test = new ARRAY(a, con, s1);
				ARRAY b_test = new ARRAY(b, con, s2);
				ARRAY c_test = new ARRAY(c, con, s3);
				cstmt = con.prepareCall("{ call pro_forall_insert(?,?,?) }");
				cstmt.setObject(1, a_test);
				cstmt.setObject(2, b_test);
				cstmt.setObject(3, c_test);  
				cstmt.execute();
				con.commit();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

	}
}

DbUtil工具类：

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class DbUtil {
	static Properties properties = null;

	public DbUtil() {
		// 读取.properties文件的信息
		properties = new Properties();
		InputStream in = getClass().getResourceAsStream("/com/chinaboxun/ordermanager/config/ordermanager.properties");
		try {
			properties.load(in);  
		} catch (IOException ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace();
		}
	}
	
	/**
	 * <LI>获取连接对象</LI>
	 * 
	 * @return
	 */
	 public Connection getCon() {
	        Connection connection = null;  
	        try {
	        	String url=properties.getProperty("jdbc.url");
	        	String user=properties.getProperty("jdbc.username");
	        	String pwd=properties.getProperty("jdbc.password");
	        	String driver=properties.getProperty("jdbc.driverClassName");
	            Class.forName(driver);	               
	            connection = DriverManager.getConnection(url, user, pwd);
	        } catch (Exception err) {
	        	  System.out.println("错误：ConDB-->getCon()____JDBC连接失败!");
	            err.printStackTrace();
	            return null;
	        }
	        return connection;
	}  
}

ordermanager.properties属性文件：

jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@172.16.0.162:1521:ORCL
jdbc.username=m_order
jdbc.password=morder

最后：一定要记得导入orai18n.jar否则一遇到字符串就乱码、添加不到数据！

点击下载orai18n.jar

----------------编辑：20140110---------------

一些加上jar继续报错如下错误的朋友可以考虑以下解决方案：

ERROR1：Non supported character set: oracle-character-set-852

ERROR2：oracle/i18n/text/converter/CharacterConverterOGS.getInstance(I)Loracle/i18n/text/converter/CharacterConverter;

以上两个错误可以采取一个方案，就是把type的数据类型改成：nVARCHAR2

如果坚持使用该jar的童鞋请将 nls_charset12.jar 加入到 classpath 中。在加上orai18n.jar

----------------编辑：20140110---------------

我被这个可整惨了！

尼玛、老子这牙齿疼死我了！！！