oralce存储过程输入数据java List示例

一: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 oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

/**
 * @author Leewinq
 *
 */
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();   
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (conn != null && !conn.isClosed()) {     
     conn.close();
     conn = null;
    }
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}

 

二,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;

 

SELECT * FROM T_list_test

 

以上代码,本人测试可以直接运行,请修改对应的数据库连接字符串