存储过程与java类型对照_如何使用java读取从存储过程返回的类型的ARRAY？

我一直在尝试增强上面链接位置中提到的存储过程,并在实现中感到困惑.而不是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 inParams = new HashMap();

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;

}

}

更新：

在使用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

Object[] attrs = null;

Struct bean = (Struct) idOutArraz[i];

attrs = bean.getAttributes();

if (attrs != null) {

System.out.println(Arrays.asList(attrs));

}

}

解决方法:

经过多次试验和不同方法的错误后回答.

在尝试实现很多解决方案后,Callable语句为我工作.看起来像是一种解决方法,但欢迎解决实际实施的任何解决方案.

请在下面找到实施的工作副本.

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 execute(final RevAppViewBean appViewBean$Session, DataSource dataSource) throws Exception {

dataSource = ds;

List beans = new ArrayList();

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

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;

}

}

标签：java,stored-procedures,spring,oracle,bulk-load

来源： https://codeday.me/bug/20190716/1478891.html