oracle 如何使用java读取从存储过程返回的类型数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16937775/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read a ARRAY of types returned from a stored proc using java?
提问by sandy
This is a continuation of the question posted under the following location: 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
这是在以下位置发布的问题的延续： 将 Bean 列表传递给 oracle 存储过程的 Java 程序 - 一次性传递整个列表，而不是一个接一个地附加对象
I have been trying to enhance the stored procedure mentioned in the above link location and am confused in the implementation. Rather than VARCHAR2 as a output from the procedure i now want to return NUM_ARRAY as the output from the procedure. Can you please help me in implementing the logic to read the NUM_ARRAY in my java code. Normally output is returned using Map out = super.execute(inParams); How can i now extract the NUM_ARRAY to my bean?
我一直在尝试增强上述链接位置中提到的存储过程，但在实现中感到困惑。而不是 VARCHAR2 作为过程的输出，我现在想返回 NUM_ARRAY 作为过程的输出。你能帮我实现在我的java代码中读取NUM_ARRAY的逻辑吗？通常使用 Map out = super.execute(inParams); 返回输出。我现在如何将 NUM_ARRAY 提取到我的 bean 中？
The source code implementation is as follows.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.object.StoredProcedure;
public class RevPrdBrkDwnSP extends StoredProcedure{
private final Logger log = Logger.getLogger(this.getClass().getName());
public RevPrdBrkDwnSP(DataSource dataSource, String storeProcName) {
// Run the Parent
super(dataSource, storeProcName);
// Declare the Parameter Details
declareParameter(new SqlParameter("IN_ARRAY", OracleTypes.ARRAY, "****.PROD_PRCT_BRKDWN_TYPE_ARRAY"));
declareParameter(new SqlOutParameter("OUT_ARRAY", OracleTypes.ARRAY, "****.PROD_PRCT_BRKDWN_TYPE_ARRAY"));
// Compile the SP
compile();
}
public boolean execute(final RevAppViewBean appViewBean$Session, final DataSource dataSource) throws Exception {
boolean returnVal = false;
Map<String, Object> inParams = new HashMap<String, Object>();
log.info("Setting up the Store Procedure Params");
inParams.put("IN_ARRAY", new SqlTypeValue() {
public void setTypeValue(PreparedStatement cs, int index, int sqlType, String typeName) throws SQLException {
Connection con = cs.getConnection();
ArrayDescriptor des = ArrayDescriptor.createDescriptor("****.PROD_PRCT_BRKDWN_TYPE_ARRAY", con);
ARRAY a = new ARRAY(des, con, appViewBean$Session.getExcelRecLst().toArray());
cs.setObject(1, (Object)a);
}
});
inParams.put("OUT_ARRAY", identifier); // what should the identifier be ?????????
if (log.isDebugEnabled()) {
log.debug("Executing the **** Store Procedure ");
}
Map out = super.execute(inParams); // how to get the same array as value ??????
log.info("output size is --------------------->>>>>>>>>> "+out.size());
for(Object o : out.keySet()){
log.info((String)out.get(o));
returnVal = Boolean.parseBoolean((String)out.get(o));
}
if (log.isDebugEnabled()) {
log.info("Output from **** Store Procedure :" + out);
}
return returnVal;
}
}
Update:After making use of the Spring Data JDBC Extension the source code had to be changed to accommodate the the new response which is pasted below, but the problem of connection still exists when the bean.getAttributes() method is called. Looks like a way needs to be found to not close the connection or access the values before the connection gets closed.
更新：使用 Spring Data JDBC Extension 后，必须更改源代码以适应下面粘贴的新响应，但是调用 bean.getAttributes() 方法时仍然存在连接问题。看起来需要找到一种方法来在连接关闭之前不关闭连接或访问值。
Map out = super.execute(inParams);
log.info("output size is --------------------->>>>>>>>>> "+out.size()); //prints the actual value
Object[] idOutArraz = (Object[])out.get("OUT_ARRAY");
log.info("size of returnValue is "+idOutArraz.length); //prints the right number of results
for(int i= 0; i<idOutArraz.length;i++){
Object[] attrs = null;
Struct bean = (Struct) idOutArraz[i];
attrs = bean.getAttributes();
if (attrs != null) {
System.out.println(Arrays.asList(attrs));
}
}
回答by sandy
Answered after lot many trial and errors with different approaches. After trying to implement lot many solutions the Callable statement worked for me. Looks like a workaround, but any solution to resolve the actual implementation is welcome.
经过多次尝试和错误的不同方法的回答。在尝试实施很多解决方案之后，Callable 语句对我有用。看起来像是一种解决方法，但欢迎任何解决实际实现的解决方案。
Please find below the working copy of the implementation.
请在下面找到实现的工作副本。
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
import com.****.****.****.ExcelListenerBean;
import com.****.****.****.RevAppViewBean;
public class RevPrdBrkDwnSP extends StoredProcedure{
private final Logger log = Logger.getLogger(this.getClass().getName());
private Connection con = null;
private DataSource ds = null;
public RevPrdBrkDwnSP(DataSource dataSource, String storeProcName) throws SQLException {
// Run the Parent
super(dataSource, storeProcName);
con = dataSource.getConnection();
ds = dataSource;
if (log.isInfoEnabled()) {
log.info("Stored Procedure Name : "+ storeProcName);
}
// Declare the Parameter Details
declareParameter(new SqlParameter("IN_ARRAY", OracleTypes.ARRAY, "****.PROD_PRCT_BRKDWN_TYPE_ARRAY"));
declareParameter(new SqlOutParameter("OUT_ARRAY", OracleTypes.ARRAY, "****.PROD_PRCT_BRKDWN_TYPE_ARRAY"));
// Compile the SP
compile();
}
public List<ExcelListenerBean> execute(final RevAppViewBean appViewBean$Session, DataSource dataSource) throws Exception {
dataSource = ds;
List<ExcelListenerBean> beans = new ArrayList<ExcelListenerBean>();
log.info("Setting up the Store Procedure Params");
String getDBUSERByUserIdSql = "{call ****.PRCS_PROD_PRCT_BRKDWN_ENTRIES(?,?)}";
CallableStatement cs = con.prepareCall(getDBUSERByUserIdSql);
ArrayDescriptor des = ArrayDescriptor.createDescriptor("PBAREV.PROD_PRCT_BRKDWN_TYPE_ARRAY", con);
ARRAY a = new ARRAY(des, con, appViewBean$Session.getExcelRecLst().toArray());
cs.setObject(1, (Object)a);
cs.registerOutParameter(2, OracleTypes.ARRAY, "****.PROD_PRCT_BRKDWN_TYPE_ARRAY");
if (log.isDebugEnabled()) {
log.debug("Executing the PBAREV Store Procedure ");
}
cs.execute();
log.info("Executed ****.PRCS_PROD_PRCT_BRKDWN_ENTRIES... Processing values to beans");
Array arr = cs.getArray(2);
Object[] objArr = (Object[]) arr.getArray();
for(int i=0; i<objArr.length;i++){
STRUCT st = (STRUCT)objArr[i];
ExcelListenerBean bean = new ExcelListenerBean();
Object[] obj = st.getAttributes();
bean.setPrntGdwIdN(((BigDecimal)obj[1]).longValue());
bean.setChldGdwIdN(((BigDecimal)obj[2]).longValue());
bean.setChldAsetPrcntN(Double.valueOf(String.valueOf(obj[4])));
bean.setStatus(String.valueOf(obj[8]));
bean.setStatusMessage(String.valueOf(obj[9]));
beans.add(bean);
}
if (log.isDebugEnabled()) {
log.info("Finised processing SP output values to ExcelListenerBeans");
}
return beans;
}
}
回答by Codo
On the Oracle side, your code could look like this:
在 Oracle 方面，您的代码可能如下所示：
Global type delcration:
全局类型声明：
CREATE OR REPLACE TYPE NUM_ARRAY AS TABLE OF NUMBER;
Stored procedure:
存储过程：
CREATE OR REPLACE PROCEDURE PROD_PRCT_BRKDWN_TYPE_ARRAY (
in_array IN NUM_ARRAY,
out_status OUT VARCHAR2)
IS
...
Plain JDBC code (with some Oracle specific parts):
普通 JDBC 代码（包含一些 Oracle 特定部分）：
Connection con = ...;
CallableStatementcs = con.prepareCall(" ... ");
ArrayDescriptor des = ArrayDescriptor.createDescriptor("PBAREV.PROD_PRCT_BRKDWN_TYPE_ARRAY", con);
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);
cs.registerOutParameter(2, OracleTypes.ARRAY, "PBAREV.PROD_PRCT_BRKDWN_TYPE_ARRAY");
cs.execute();
ARRAY outArray = (ARRAY)cs.getArray(2);
Integer[] idOutArraz = (Integer[])outArray.getArray();
I haven't tested the code. But it should give you an idea.
我还没有测试代码。但它应该给你一个想法。
Update:
更新：
For the conversion to the Spring Framework, you might want to look at the Spring Data JDBC Extensionproject that contains the class org.springframework.data.jdbc.support.oracle.SqlReturnArrayand declare you parameter like this:
对于到 Spring 框架的转换，您可能需要查看包含该类的Spring Data JDBC Extension项目org.springframework.data.jdbc.support.oracle.SqlReturnArray并像这样声明您的参数：
declareParameter(new SqlOutParameter("OUT_ARRAY", Types.ARRAY,
"PBAREV.PROD_PRCT_BRKDWN_TYPE_ARRAY", new SqlReturnArray()));
I wonder what the Mapof the executemethod contains for the out array because the documentation doesn't say anything.
我想知道out 数组Map的execute方法包含什么，因为文档没有说明任何内容。
