注：本文来源 《    》

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：存储过程

1   REPLACE TYPE TEST_OBJECT  OBJECT  2 (  3   id number,  4   name 2(32)  5 );  6   REPLACE TYPE TABLES_ARRAY  VARRAY(100)  TEST_OBJECT;  7   test purge;  8   test  9 ( 10   id number, 11   name 2(32) 12 ); 13   replace  t_list_to_p(arr_t  tables_array) 14  15  16    i  1..arr_t. loop 17          test (arr_t(i).id,arr_t(i).name); 18    loop; 19   ; 20  t_list_to_p;

2:Java 实现逻辑代码

1 import java.sql.CallableStatement;  2 import java.sql.Connection;  3 import java.sql.DriverManager;  4 import java.sql.SQLException;  5 import java.util.ArrayList;  6 import java.util.List;  7 import oracle.sql.ARRAY;  8 import oracle.sql.ArrayDescriptor;  9 import oracle.sql.STRUCT; 10 import oracle.sql.StructDescriptor; 11  12 public class TestListToProcedure { 13     static final String driver_class  = "oracle.jdbc.driver.OracleDriver"; 14     static final String connectionURL = "jdbc:oracle:thin:@10.150.15.150:1521:orcl"; 15     static final String userID        = "test"; 16     static final String userPassword  = "test"; 17     public void runTest() { 18         Connection  con = null; 19         CallableStatement stmt = null ; 20         try { 21             Class.forName (driver_class).newInstance(); 22             con = DriverManager.getConnection(connectionURL, userID, userPassword); 23             StructDescriptor tDescriptor = StructDescriptor.createDescriptor("TEST_OBJECT", con); 24         	List
      
        structs = new ArrayList
       
        (); 25         	Object[] 	tObject  = null ; 26         	//能够将系统中VO，DTO转化成Object对象,先创建struts 27         	for(int i = 0; i<10; i++){ 28         		tObject = new Object[2]; 29         		tObject[0] = i; 30         		tObject[1] = "name"+i; 31                 STRUCT tStruct = new STRUCT(tDescriptor, con, tObject); 32                 structs.add(tStruct); 33         	} 34         	ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY", con); 35         	ARRAY tArray = new ARRAY(arrayDescriptor, con, structs.toArray()); 36             stmt = con.prepareCall("{call t_list_to_p(? 37 )}");   38             stmt.setArray(1, tArray); 39             stmt.execute(); 40         }  catch (SQLException e) { 41             e.printStackTrace(); 42         } catch (Exception e) { 43             e.printStackTrace(); 44         }finally{ 45         	if(stmt != null){ 46         		try { 47 					stmt.close(); 48 				} catch (SQLException e) { 49 					e.printStackTrace(); 50 				} 51         	} 52         	if(con != null){ 53         		try { 54         			con.close(); 55 				} catch (SQLException e) { 56 					e.printStackTrace(); 57 				} 58         	} 59         } 60     } 61     public static void main(String[] args) { 62     	TestListToProcedure testListToProcedure = new TestListToProcedure(); 63     	testListToProcedure.runTest(); 64     } 65  66 } 67  68