PROSAGA码农传奇-plsql-如何将列表从Java传递给Oracle Procedure？

如何将列表从Java传递给Oracle Procedure？

作者: 狗头军师
发布时间: 2024-11-10 03:47:05 (1天前)

我想将列表从Java发送到Oracle过程。例如，有一所学校，该学校有学生名单。此外，学生还有一份讲座清单。我创建了一个演讲列表，一个创建了演讲列表的学生列表，一个学校创建了一个学生列表。讲座。
<pre><code>ArrayList<String> lecture1 = new ArrayList<String>();
 lecture1.add("Mat");
 lecture1.add("physics");

ArrayList<String> lecture2 = new ArrayList<String>();
 lecture2.add("English");
 lecture2.add("Spanish");

ArrayList<String> lecture3 = new ArrayList<String>();
 lecture3.add("Germany");
 lecture3.add("French");
</code></pre>讲座清单。
<pre><code>ArrayList<ArrayList<String>> lectureList1 = new ArrayList<ArrayList<String>>();
 lectureList1.add(lecture1);
 lectureList1.add(lecture3);

ArrayList<ArrayList<String>> lectureList2 = new ArrayList<ArrayList<String>>();
 lectureList2.add(lecture2);
 lectureList2.add(lecture3);
</code></pre>以及有讲座的学生名单。
<pre><code> ArrayList<ArrayList<String>> StudentList = new ArrayList<ArrayList<String>>();
 StudentList.addAll(lectureList2);
 StudentList.addAll(lectureList2);
ArrayList<ArrayList<String>> StudentList2 = new ArrayList<ArrayList<String>>();

StudentList2.addAll(lectureList1);
    StudentList2.addAll(lectureList2);

ArrayList<ArrayList<ArrayList<String>>> school = new ArrayList<ArrayList<ArrayList<String>>>();
 school.add(StudentList2);
 school.add(StudentList);
</code></pre>

将列表转换为多维数组，然后可以执行以下操作：

Oracle安装程序：

```
CREATE TYPE stringlist AS TABLE OF VARCHAR2(100);
/

CREATE TYPE stringlist_list AS TABLE OF stringlist;
/

CREATE TYPE stringlist_list_list AS TABLE OF stringlist_list;
/

CREATE PROCEDURE load_list (
  in_list IN stringlist_list_list
)
AS
BEGIN
  NULL; -- Do something with the list
END;
/
Java的：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestDatabase2 {
  public static void main(String args[]){
    try{
      Class.forName("oracle.jdbc.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

// Convert your lists to arrays using #toArray( T[] )

String[] l1 = { "Math", "Physics" };
      String[] l2 = { "English", "Spanish" };
      String[] l3 = { "French", "German" };

ARRAY school = new ARRAY( des, con, newString[][][]{
        new String[][]{ l1, l3 },
        new String[][]{ l2, l3 }
      } );

ArrayDescriptor des = ArrayDescriptor.createDescriptor("STRINGLIST_LIST_LIST", con);

CallableStatement st = con.prepareCall("{ call add_school( :school )}");

// Passing an array to the procedure - 
      ((OracleCallableStatement) st).setARRAYAtName( "school", school );

st.execute();
    } catch(ClassNotFoundException | SQLException e) {
      System.out.println(e);
    }
  }
}
```