java如何將一個List傳入Oracle預存程序

標籤：lex   des   line   import   exe   script   value   cat   https   

註：本文來源於  深圳gg  《 java如何將一個List傳入Oracle預存程序   》

 

一： 資料庫端建一個PL/SQL的數組。

  1 CREATE OR REPLACE TYPE tables_array AS VARRAY(100) OF VARCHAR2(32) ;  2   3 drop table test purge;  4 create table test  5 (  6   name varchar2(32)  7 );  8   9  10 create or replace procedure t_list_to_p(arr_t in tables_array) is 11 begin 12   for i in arr_t.first .. arr_t.last loop 13     insert  into test values(arr_t(i)); 14   end loop; 15   commit; 16 end t_list_to_p;

 

二：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   8 import oracle.sql.ARRAY;  9 import oracle.sql.ArrayDescriptor; 10  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             stmt = con.prepareCall("{call t_list_to_p(?)}"); 24             ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY",con); 25             List list = new ArrayList(); 26             list.add("張三"); 27             list.add("李四"); 28             list.add("王五"); 29             ARRAY array = new ARRAY(descriptor,con,list.toArray()); 30             stmt.setArray(1, array); 31             stmt.execute(); 32         }  catch (SQLException e) { 33             e.printStackTrace(); 34         } catch (Exception e) { 35             e.printStackTrace(); 36         }finally{ 37         if(stmt != null){ 38         try { 39 stmt.close(); 40 } catch (SQLException e) { 41 e.printStackTrace(); 42 } 43         } 44         if(con != null){ 45         try { 46         con.close(); 47 } catch (SQLException e) { 48 e.printStackTrace(); 49 } 50         } 51         } 52     } 53  54     public static void main(String[] args) { 55     TestListToProcedure testListToProcedure = new TestListToProcedure(); 56     testListToProcedure.runTest(); 57     } 58  59 }

 

 

 

 

 

 

 

 

 

 

java如何將一個List傳入Oracle預存程序