如何将List从java传递给Oracle程序? [英] How to pass List from java to Oracle Procedure?
问题描述
我想将一个List从java发送到Oracle程序。
例如,
有一所学校,学校有一份学生名单。
此外,学生还有一份讲座清单。
我创建了一个讲座列表,
和一份有学校讲座的学生名单,
和一所学校有一份学生名单。
I want to send a List from java to Oracle procedure. Forexample, There is a school and the school has a list of students. Also, the students have a list of lectures. I create a list of lectures, and a list of students who has the list of lectures, and a school has a list of the students.
讲座。
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");
讲座清单。
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);
以及有讲座的学生名单。
And the list of students who have lectures.
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);
我想将 school 发送到oracle程序。但是我无法直接发送列表。 Oracle库允许发送数组但我想发送列表。
i want to send "school" to an oracle procedure. However I couldn't send a list directly. Oracle library allow to send array but I want to send list.
我该怎么做这个操作?你能帮助我吗?
How can I do this operation? Could you help me.
谢谢。
推荐答案
转换你的清单到一个多维数组然后你可以做类似的事情:
Convert your lists to a mutli-dimensional array and then you can do something like:
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);
}
}
}
这篇关于如何将List从java传递给Oracle程序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!