从Java将值作为数组传递到数据库存储过程

Question

我在数据库中有以下存储过程

CREATE OR REPLACE PROCEDURE my_proc (
my_array         IN     my_array_type,
my_var    IN OUT VARCHAR2)
....
....

在Java中，我有以下代码片段来调用上面的存储过程

public void callProc(String prodCode, 
String prodName, 
String prodDesc, 
) {
callableStatement = 
this.getOADBTransaction().getJdbcConnection().prepareCall("{call my_proc (?,?)}");
Object[] object = 
new Object[] { prodCode, prodName, prodDesc};
StructDescriptor structDescriptor = 
StructDescriptor.createDescriptor("my_array_type",this.getOADBTransaction().getJdbcConnection());
STRUCT struct = 
new STRUCT(structDescriptor, this.getOADBTransaction().getJdbcConnection(), 
object);
STRUCT[] structArray = { struct };
ArrayDescriptor arrayDescriptor = 
ArrayDescriptor.createDescriptor("my_array",this.getOADBTransaction().getJdbcConnection());
ARRAY array = 
new ARRAY(arrayDescriptor, this.getOADBTransaction().getJdbcConnection(), 
structArray);
callableStatement.setArray(1, array);
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.execute();
....

上面的方法是从另一个类调用的，该类位于for循环中

for(....){
Serializable[] param = 
{ prodCode, prodName, prodDesc};
db.callProc(param )
}

我想要实现的不是在for循环中调用db.callProc，我想使用ListArray或其他集合对象，并将值传递给db.callProc方法，在db.callProc方法中，我想迭代并将其作为数组传递给数据库过程，这样我的存储过程就可以处理数组并进行处理。

Answer 1

首先，我们将创建一个Pojo

public class ParamHolder{
private String param1;
private String param2;
private String param3;
//getters and setters.
}

在DB端创建一个相同类型的对象

CREATE OR REPLACE TYPE PARAM_HOLDER_OBJ 
AS OBJECT ( PARAM1 VARCHAR2(200), PARAM2 VARCHAR2(200), PARAM3 VARCHAR3(200));

一旦您创建了对象，就可以创建这些对象的表

CREATE OR REPLACE TYPE PARAM_HOLDER_OBJ_TABLE
IS TABLE OF PARAM_HOLDER_OBJ

我们的程序可能采用类似的输入模板

custom(p_param_holder_tab IN TYPE PARAM_HOLDER_OBJ_TABLE)

例如，假设我们的proc看起来像这样，现在我们需要做的是从我们的java代码中调用这个proc，并传入一个ParamHolder数组。

Code Snippet:
//variable declaration
//ParamHolder[] paramHolders = ..getTheParamHolderArray();
try (Connection con = createConnWithDbDetails(getDBDetails());
CallableStatement stmnt =
con.prepareCall("{ call custom(?) }")) {
//Create a arrayDescriptor
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("PARAM_HOLDER_OBJ_TABLE", con);
Array array = new ARRAY(descriptor , con, paramHolders);
stmnt.setArray(1, array );
}Catch(Exception e){
e.printStackTrace();
}

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++编辑日期：2016年6月21日用于添加调用方法。IST下午5点

你能做的就是创建一个类似的方法

public void callProc(ParamHolder[] paramHolders){
try (Connection con = createConnWithDbDetails(getDBDetails());
CallableStatement stmnt =
con.prepareCall("{ call custom(?,?) }")) {
//Create a arrayDescriptor
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("PARAM_HOLDER_OBJ_TABLE", con);
Array array = new ARRAY(descriptor , con, paramHolders);
stmnt.setArray(1, array );
stmnt.registerOutParameter(2,OracleType.VARCHAR,"p_out_var");           //Register any output variable if your procedure returns any thing.
stmmnt.execute();                                              //this will take the Array of ParamHolder straight to the DB for processing.
String result = stmnt.getString(2);                                  //Will fetch yuou the result form the DB to your local String.
}Catch(Exception e){
e.printStackTrace();
}
}

它将采用一个ParamHolder类的数组，该数组将直接传递给您的db-proc，您将得到相应的结果。因为当前的过程定义没有指定任何out参数，但您可以定义并注册以捕获它。

假设你有一段调用代码，它将使用

public class ProcDaoImpl{
public void executeProc(){
ParamHolder[] paramArray = new ParamHolder[]{                                          //create an array of four elements
new ParamHolder("param1","param2","param3"),
new ParamHolder("param1","param2","param3"),
new ParamHolder("param1","param2","param3"),            
new ParamHolder("param1","param2","param3")                                                   //Each array element represents a set of InputParams
}
//call the DB procedure now..
SomeClass.callProc(paramArray);                                                                     // pass in the created array to it.
}
}

希望能有所帮助。：)

感谢

Answer 2

这可能会有所帮助：

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor; 
public class TestSP{
public static void arrayDataToSP()
{
try{
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:url ","[user]","[password]");
String alphabets[] = {"a", "b", "c","d","e","f","g"}; 
//use ARRAY_TABLE as ArrayDescriptor 
ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.ARRAY_TABLE", con);
ARRAY array_to_pass = new ARRAY(des,con,alphabets);
CallableStatement cst = con.prepareCall("call SchemaName.my_proc(?,?)");
// Passing an alphabets array to the procedure 
cst.setArray(1, array_to_pass);
st.registerOutParameter(2, Types.VARCHAR);
cst.execute();
// Retrive output of procedure execute
ARRAY output = ((OracleCallableStatement)cst).getARRAY(2);
BigDecimal[] outputArray = (BigDecimal[])(output.getArray());
for(int i=0;i<outputArray.length;i++)
System.out.println("element" + i + ":" + outputArray[i] + "n");
} catch(Exception e) {
System.out.println(e);
}
}
public static void main(String args[]){ arrayDataToSP(); } }