将Java List 传入到Oracle DB的存储过程

http://progress.iteye.com/blog/438707

一 : Oracle代码

create table T_list_test
(
    list_test_id number(18),
    list_test_title varchar(200),
    list_test_price number(18,6)
)

CREATE  TYPE AMaterial AS OBJECT
(
       mid NUMBER(18),
       mname NVARCHAR2(200),   ----------切记,java对象中的String型,在创建oracle对象时,必须使用  NVARCHAR2,否则拿不到值
       mprice NUMBER(18,6)
)

CREATE TYPE mList AS TABLE OF AMaterial; 

CREATE OR REPLACE procedure test_varray(list_ IN mList,omName OUT VARCHAR2)
AS      
BEGIN
       FOR i IN 1..list_.count() LOOP
           dbms_output.put_line('第'||i||'个对象的ID为:__________'||list_(i).mid);
           dbms_output.put_line('第'||i||'个对象的name为:__________'||list_(i).mname);
           dbms_output.put_line('第'||i||'个对象的price为:__________'||list_(i).mprice);
          
           INSERT INTO T_list_test(list_test_id,list_test_title,list_test_price) VALUES (list_(i).mid,list_(i).mname,list_(i).mprice);        
                 
       END LOOP;
       omName := '操作成功!';
END;

一 : java代码

1, Bean

public class MObj {
	private Long mId;
	private String mName;
	private Double price;

	public void setMId(Long mId) {
		this.mId = mId;
	}

	public void setMName(String mName) {
		this.mName = mName;
	}

	public void setPrice(Double price) {
		this.price = price;
	}

	public Long getMId() {
		return (this.mId);
	}

	public String getMName() {
		return (this.mName);
	}

	public Double getPrice() {
		return (this.price);
	}
}

2, java测试代码

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.engine.spi.SessionImplementor;


import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

 
public class TestProcedureList {

	private static ARRAY getArray(Connection con, String OracleObj,
			String Oraclelist, List listData) throws Exception {
		ARRAY array = null;

		if (listData != null && listData.size() > 0) {
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
					con);

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

			for (int i = 0; i < listData.size(); i++) {
				/*
				 * result = new Object[3]; // 数组大小应和你定义的数据库对象(AOBJECT)的属性的个数
				 * result[0] = ((MObj) listData.get(i)).getMId(); //
				 * 将list中元素的数据传入result数组 result[1] = ((MObj)
				 * listData.get(i)).getMName(); result[2] = ((MObj)
				 * listData.get(i)).getPrice();
				 */
				Object[] result = { ((MObj) listData.get(i)).getMId(),
						((MObj) listData.get(i)).getMName(),
						((MObj) listData.get(i)).getPrice() };

				structs[i] = new STRUCT(structdesc, con, result);
			}

			array = new ARRAY(desc, con, structs);
		}
		return array;
	}

	/**
	 * @return
	 */
	private List makeData() {
		List list = new ArrayList<MObj>();

		MObj m1 = new MObj();
		m1.setMId(Long.valueOf(110000000000000061l));
		m1.setMName("订单物资一");
		m1.setPrice(1888.80);

		MObj m2 = new MObj();
		m2.setMId(Long.valueOf(110000000000000052l));
		m2.setMName("订单物资二");
		m2.setPrice(6666.80);

		list.add(m1);
		list.add(m2);

		return list;
	}

	private Connection getConn() throws Exception {

		Class c = Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "xxxx", "xxx");

		return conn;
	}

	public static void main(String[] args) {
		TestProcedureList procedureList = new TestProcedureList();
		List listData = procedureList.makeData();
		Connection conn = null;

		try {
			conn = procedureList.getConn();

			if (conn != null && !conn.isClosed()) {
				ARRAY array_ = procedureList.getArray(conn, "AMATERIAL",
						"MLIST", listData);
				CallableStatement stmt = conn
						.prepareCall("{call test_varray(?,?)}");
				stmt.setArray(1, array_);
				stmt.registerOutParameter(2, Types.VARCHAR);
				stmt.execute();

				String mName = stmt.getString(2);
				System.out.println("从oracle存储过程返回的结果:____________" + mName);

				conn.commit();
				stmt.close();
				
				/*conn = ((SessionImplementor)getSessionFactory().getCurrentSession()).connection();				
				OracleConnection oracleConnection = null;
				try {
					//加上这段，否则报错：java.sql.sqlexception: 出现不支持的 SQL92 标记: 2
				    if (conn.isWrapperFor(OracleConnection.class)) {
				        oracleConnection = conn.unwrap(OracleConnection.class);
				    }
				} catch (SQLException ex) {
					ex.printStackTrace();
				}			      
				cstmt = oracleConnection.prepareCall("{call test_Dept(?,?)}");
			    int returnCode;
				ARRAY array_ = DepartmentDAOImpl2.getArray(oracleConnection, "ODEPT", "LDEPT", uniDepartmentList);
				cstmt.setArray(1, array_);
				cstmt.registerOutParameter(2, OracleTypes.NUMBER);
				cstmt.execute();
				cstmt.close();*/
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}