java怎樣將一個List傳入Oracle預存程序

標籤：catch   name   import   loop   track   imp   .sql   rac   des   

 java怎樣將一個List傳入Oracle預存程序。範例例如以下：

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

CREATE OR REPLACE TYPE tables_array AS VARRAY(100) OF VARCHAR2(32) ;drop table test purge;create table test(  name varchar2(32));create or replace procedure t_list_to_p(arr_t in tables_array) isbegin  for i in arr_t.first .. arr_t.last loop    insert  into test values(arr_t(i));  end loop;  commit;end t_list_to_p;

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;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);            stmt = con.prepareCall("{call t_list_to_p(?
)}");              ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY",con);            List list = new ArrayList();            list.add("張三");            list.add("李四");            list.add("王五");            ARRAY array = new ARRAY(descriptor,con,list.toArray());            stmt.setArray(1, array);            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();    }}

java怎樣將一個List傳入Oracle預存程序