sandy sandy - 1 month ago 11
Java Question

How to read a ARRAY of types returned from a stored proc using java?

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

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?

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.

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));
}
}

Answer

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.

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;
    }
}