Java程序将Bean列表传递给oracle存储过程-一次传递整个列表，而不是一个接一个地附加对象 [英] Java program to pass List of Bean to a oracle stored procedure - Pass entire list at one shot rather than appending objects one after the other
问题描述
我有以下一组TYPE，PROCEDURE和Java代码.我能够成功调用存储过程，但是我必须一个接一个地追加对象.我希望该过程能够一口气完成，因为我正在处理超过5万条以上的记录.任何人都可以让我知道需要进行哪些更改，以便我可以一次发送整个列表.该代码可以在下面找到.
I have the following set of TYPE, PROCEDURE and Java code. I am able to call the stored procedure successfully but i have to append the objects one after the other. I want the process to be happening at one shot as i am dealing with over 50K+ records. Can anyone please let me know what changes needs to be done so that i can send the entire list at one. The code can be found below.
TYPES:
CREATE OR REPLACE TYPE CER_GL_ENTRY_TYPE AS OBJECT
(idKey NUMBER(10) );
CREATE or REPLACE TYPE CER_GL_ENTRY_TYPE_LIST AS TABLE OF CER_GL_ENTRY_TYPE;
PROCEDURE:
CREATE OR REPLACE PROCEDURE GL_PROCESS_BULK_ENTRIES (
p_array IN CER_GL_ENTRY_TYPE_LIST ,p_status OUT VARCHAR2)
AS
v_count NUMBER(5);
row_detail CER_GL_ENTRY_TYPE;
BEGIN
--p_arr_int := NEW array_int ();
--p_arr_int.EXTEND (10);
--len := p_array.COUNT;
v_count := 0;
FOR i IN 1 .. p_array.COUNT
LOOP
row_detail := p_array (i);
DBMS_OUTPUT.put_line('hello');
DBMS_OUTPUT.put_line (row_detail.idKey);
--p_arr_int (i) := v_count;
v_count := v_count + 1;
p_status := 'true';
END LOOP;
DBMS_OUTPUT.put_line (v_count);
DBMS_OUTPUT.put_line (p_status);
EXCEPTION
WHEN OTHERS
THEN
-- handle errors here...
DBMS_OUTPUT.put_line ('Error: ' || SUBSTR (1, 255, SQLERRM));
END;
/
Java Bean:
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
public class SampleListenerBean implements SQLData
{
private String sql_type="CER_GL_ENTRY_TYPE";
private int id;
public SampleListenerBean() {
}
public SampleListenerBean(String sqlType, int id) {
this.sql_type = sqlType;
this.id = id;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSQLTypeName() throws SQLException {
return sql_type;
}
public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
id = stream.readInt();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeInt(id);
}
}
Main class:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class StProcExample {
public static void main(String args[]){
Connection con=null;
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
con = DriverManager.getConnection("jdbc:oracle:thin:@******:1521: TUW1", "*******", "*********");
String query = "{call GL_PROCESS_BULK_ENTRIES(?,?)}";
CallableStatement cs = con.prepareCall(query);
ArrayDescriptor des = ArrayDescriptor.createDescriptor("CER_GL_ENTRY_TYPE_LIST", con);
List<SampleListenerBean> sampleLst = new ArrayList<SampleListenerBean>();
SampleListenerBean bean = null;
for (int i = 0; i < 20; i++) {
bean = new SampleListenerBean("CER_GL_ENTRY_TYPE",i);
sampleLst.add(bean);
}
SampleListenerBean emp=new SampleListenerBean("TS.TEST_EMP_OBJ",234);
SampleListenerBean emp1=new SampleListenerBean("TS.TEST_EMP_OBJ",235);
Object[] employees= new Object[]{emp,emp1};
Object[] employees= new Object[]{sampleLst};
ARRAY a = new ARRAY(des, con, employees);
cs.setObject(1, (Object)a);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String status = cs.getString(2);
System.out.print("The status is " + status);
if (cs != null) {
cs.close();
}
}
catch(SQLException e){
e.printStackTrace();
}
}
}
我想要替换下面的代码
SampleListenerBean emp=new SampleListenerBean("TS.TEST_EMP_OBJ",234);
SampleListenerBean emp1=new SampleListenerBean("TS.TEST_EMP_OBJ",235);
Object[] employees= new Object[]{emp,emp1};
ARRAY a = new ARRAY(des, con, employees);
cs.setObject(1, (Object)a);
我想直接使用sampleLst代替Object数组"employees"，而不是分别设置每个对象.当我处理50K +个对象时，我无法继续将它们添加到object []中.我也会遇到堆问题.有人可以在这里帮我吗?
Instead of setting each object separately, i want to directly use sampleLst instead of the Object array "employees". When i deal with 50K+ objects i cannot keep adding them to the object[]. i will run into heap problems as well. Can anyone please help me out here?
推荐答案
我将使用更简单的表类型:
I would use a simpler table type:
CREATE OR REPLACE TYPE NUM_ARRAY AS TABLE OF NUMBER;
然后稍微简化存储过程:
And then somewhat simplify the stored procedure:
CREATE OR REPLACE PROCEDURE GL_PROCESS_BULK_ENTRIES (
p_array IN NUM_ARRAY,
p_status OUT VARCHAR2)
IS
...
然后您创建一个Integer数组，该数组将很容易装入内存:
You then create an array of Integer that will easily fit in memory:
Integer[] idArray = new Integer[50000];
// fill the array of integers here
for (int i = 0; i < idArray.length; i++)
idArray[i] = ....;
ARRAY a = new ARRAY(des, con, idArray);
cs.setObject(1, (Object)a);
无需传递任何ID列表即可创建任何重量级的bean.
There's no need to create any heavy-weight beans just to pass a list of IDs.
这篇关于Java程序将Bean列表传递给oracle存储过程-一次传递整个列表，而不是一个接一个地附加对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
