java怎样将一组对象传入Oracle存储过程

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

目录

• java怎样将一组对象传入Oracle存储过程
  • 1：存储过程
  • 2:Java 实现逻辑代码

 

正文

注：本文来源 《  java怎样将一组对象传入Oracle存储过程  》

回到顶部

java怎样将一组对象传入Oracle存储过程

java怎样将一组对象传入Oracle存储过程。须要注意的是jar包是有要求的，假设使用不当会导致仅仅有数字能传过去，字符串传只是去。

假设是Oracle11g则须要选用例如以下的jar包，F:\app\Administrator\product\11.2.0\dbhome_1\jlib\orai18n.jar、D:\program\weblogic\oracle_common\modules\oracle.jdbc_11.2.0\ojdbc6.jar

 

1：存储过程

CREATE OR REPLACE TYPE TEST_OBJECT AS OBJECT
(
  id number,
  name varchar2(32)
);
CREATE OR REPLACE TYPE TABLES_ARRAY AS VARRAY(100) OF TEST_OBJECT;
drop table test purge;
create table test
(
  id number,
  name varchar2(32)
);
create or replace procedure t_list_to_p(arr_t in tables_array)
is
begin
  for i in 1..arr_t.count loop
      insert  into test values(arr_t(i).id,arr_t(i).name);
  end loop;
  commit;
end t_list_to_p;

2:Java 实现逻辑代码

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class TestListToProcedure {
    static final String driver_class  = "oracle.jdbc.driver.OracleDriver";
    static final String connectionURL = "jdbc:oracle:thin:@10.150.15.150:1521:orcl";
    static final String userID        = "test";
    static final String userPassword  = "test";
    public void runTest() {
        Connection  con = null;
        CallableStatement stmt = null ;
        try {
            Class.forName (driver_class).newInstance();
            con = DriverManager.getConnection(connectionURL, userID, userPassword);
            StructDescriptor tDescriptor = StructDescriptor.createDescriptor("TEST_OBJECT", con);
        	List<STRUCT> structs = new ArrayList<STRUCT>();
        	Object[] 	tObject  = null ;
        	//能够将系统中VO，DTO转化成Object对象,先创建struts
        	for(int i = 0; i<10; i++){
        		tObject = new Object[2];
        		tObject[0] = i;
        		tObject[1] = "name"+i;
                STRUCT tStruct = new STRUCT(tDescriptor, con, tObject);
                structs.add(tStruct);
        	}
        	ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY", con);
        	ARRAY tArray = new ARRAY(arrayDescriptor, con, structs.toArray());
            stmt = con.prepareCall("{call t_list_to_p(?
)}");  
            stmt.setArray(1, tArray);
            stmt.execute();
        }  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();
				}
        	}
        }
    }
    public static void main(String[] args) {
    	TestListToProcedure testListToProcedure = new TestListToProcedure();
    	testListToProcedure.runTest();
    }

}