Utilities Blog by JuCa Cruz: Pass Collection of Objects with Attributes to a Stored Procedure using iBatis

In my last entry I had a requirement which needed to pass a List to an Oracle Stored Procedure, well, that thing is history.
But now the problem becomes more complex, because, what happen if I'm expecting on my Stored Proc and Type (STRUCT)
This is my Collection in Oracle!
And this is my Stored Proc (this is just a sample which receives the TABLE of Records)

CREATE OR REPLACE PROCEDURE test_list_params (
   p_cust_id    IN   NUMBER,
   p_acct_num   IN   NUMBER,
   p_list IN CUST_ACCNT_MTN_PRE_ORDER_LIST
)
IS             
BEGIN 
    FOR i in 1 .. p_list.count
    LOOP
        INSERT INTO TESTING_SP (CUST_ID,
                    ACCT_NUM,
                    MTN,
                    CIDB_ORDER_ID,
                    MTN_ORDER_STATE,
                    MTN_ORDER_TYPE,
                    BDY_MTN) 
             VALUES (p_cust_id,
                    p_acct_num,
                    p_list(i).mtn,
                    p_list(i).cidb_order_id,
                    p_list(i).mtn_order_state,
                    p_list(i).mtn_order_type,
                    p_list(i).bdy_mtn);
        DBMS_OUTPUT.put_line ('STRUCT '||i);
        DBMS_OUTPUT.put_line ('mtn='||p_list(i).mtn||', cidb_order_id='||p_list(i).cidb_order_id||', mtn_order_state='||p_list(i).mtn_order_state);
        DBMS_OUTPUT.put_line ('mtn_order_type='||p_list(i).mtn_order_type||', bdy_mtn='||p_list(i).bdy_mtn);        
    END LOOP;   
    COMMIT;
EXCEPTION                                          
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Some other kind of error occurred.');
      raise_application_error(-20102, 'Some other kind of error occurred.');
      ROLLBACK;
END;

This is the table where I'm going to INSERT
Obviously, the solution is Create a Handler like I did on my last entry, but is not going to be as simple, because I need to Map my Oracle STRUCT with my Java Code, so I have to do an domain object (POJO) coupled to Oracle and my Handler.

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;

import oracle.jdbc.driver.OracleTypes;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.Datum;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.STRUCT;
/**
 * Java Implementation of an Oracle STRUCT/TYPE.<BR>
 * This is a nasty looking model because it implements ORAData and ORADataFactory.
 * 
 * It's really important to follow the order/index of each attribute on the Oracle Type (see getter/setter methods)
 * 
 * <PRE>
 * CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER AS OBJECT(
 *  cust_id NUMBER, 
 *  acct_num NUMBER,  
 *  mtn NUMBER,  
 *  cidb_order_id NUMBER,  
 *  mtn_order_state VARCHAR(1),  
 *  mtn_order_type VARCHAR(1),  
 *  mtn_order_state_date DATE,  
 *  bdy_mtn NUMBER )
 * </PRE>
 * 
 * @author Juan Cruz
 * @since 09/20/2010
 * 
 * @see oracle.sql.ORAData
 * @see oracle.sql.ORADataFactory
 * @see oracle.sql.STRUCT
 *
 */
public class CustomerAccountMtnOrderStruct implements ORAData, ORADataFactory {

public static final String SQL_NAME = "CUST_ACCNT_MTN_PRE_ORDER";
 public static final int SQL_TYPECODE = OracleTypes.STRUCT;
 protected MutableStruct struct;
 static int[] sqlTypes = { OracleTypes.NUMBER, OracleTypes.NUMBER,
   OracleTypes.NUMBER, OracleTypes.NUMBER, OracleTypes.VARCHAR,
   OracleTypes.VARCHAR, OracleTypes.DATE, OracleTypes.NUMBER };
 static ORADataFactory[] oraDataFactories = new ORADataFactory[sqlTypes.length];
 static final CustomerAccountMtnOrderStruct myInstance = new CustomerAccountMtnOrderStruct();

private Long customerId;
 private Integer accountNumber;
 private Long mtn;
 private Integer cidbOrderId;
 private String mtnOrderState; 
 private String mtnOrderType;
 private Date mtnOrderStateDate;
 private Long buddyMtn;

protected CustomerAccountMtnOrderStruct(boolean init) {
  if (init) {
   struct = new MutableStruct(new Object[sqlTypes.length], sqlTypes, oraDataFactories);
  }
 }

public CustomerAccountMtnOrderStruct() {
  this(true);
 }

public Datum toDatum(Connection conn) throws SQLException {
  Datum datum = struct.toDatum(conn, SQL_NAME);
  return datum;
 }

public ORAData create(Datum datum, int sqlType) throws SQLException {
  return create(null, datum, sqlType);
 }

protected ORAData create(CustomerAccountMtnOrderStruct customerAccountMtnOrderStruct, Datum datum,
   int sqlType) throws SQLException {
  if (datum == null) {
   return null;
  }
  if (customerAccountMtnOrderStruct == null) {
   customerAccountMtnOrderStruct = new CustomerAccountMtnOrderStruct(false);
  }
  customerAccountMtnOrderStruct.struct = new MutableStruct((STRUCT) datum, sqlTypes, oraDataFactories);
  return customerAccountMtnOrderStruct;
 }

public static ORADataFactory getORADataFactory() {
  return myInstance;
 }

public Long getCustomerId() throws SQLException {
  return (Long)struct.getAttribute(0);
 }

public void setCustomerId(Long customerId) throws SQLException {
  this.customerId = customerId;
  struct.setAttribute(0, customerId);
 }

public Integer getAccountNumber() throws SQLException {
  return (Integer)struct.getAttribute(1);
 }

public void setAccountNumber(Integer accountNumber) throws SQLException {
  this.accountNumber = accountNumber;
  struct.setAttribute(1, accountNumber);
 }

public Long getMtn() throws SQLException {
  return (Long)struct.getAttribute(2);
 }

public void setMtn(Long mtn) throws SQLException {
  this.mtn = mtn;
  struct.setAttribute(2, mtn);
 }

public Integer getCidbOrderId() throws SQLException {
  return (Integer)struct.getAttribute(3);
 }

public void setCidbOrderId(Integer cidbOrderId) throws SQLException {
  this.cidbOrderId = cidbOrderId;
  struct.setAttribute(3, cidbOrderId);
 }

public String getMtnOrderState() throws SQLException {
  return (String)struct.getAttribute(4);
 }

public void setMtnOrderState(String mtnOrderState) throws SQLException {
  this.mtnOrderState = mtnOrderState;
  struct.setAttribute(4, mtnOrderState);
 }
 
 public String getMtnOrderType() throws SQLException {
  return (String)struct.getAttribute(5);
 }

public void setMtnOrderType(String mtnOrderType) throws SQLException {
  this.mtnOrderType = mtnOrderType;
  struct.setAttribute(5, mtnOrderType);
 }

public Date getMtnOrderStateDate() throws SQLException {
  return (Date)struct.getAttribute(6);
 }

public void setMtnOrderStateDate(Date mtnOrderStateDate) throws SQLException {
  this.mtnOrderStateDate = mtnOrderStateDate;
  struct.setAttribute(6, mtnOrderStateDate);
 }

public Long getBuddyMtn() throws SQLException {
  return (Long)struct.getAttribute(7);
 }

public void setBuddyMtn(Long buddyMtn) throws SQLException {
  this.buddyMtn = buddyMtn;
  struct.setAttribute(7, buddyMtn);
 }

@Override
 public int hashCode() {
  final int prime = 31;
  int result = 1;
  result = prime * result
    + ((accountNumber == null) ? 0 : accountNumber.hashCode());
  result = prime * result
    + ((cidbOrderId == null) ? 0 : cidbOrderId.hashCode());
  result = prime * result
    + ((customerId == null) ? 0 : customerId.hashCode());
  result = prime * result + ((mtn == null) ? 0 : mtn.hashCode());
  return result;
 }

@Override
 public boolean equals(Object obj) {
  if (this == obj)
   return true;
  if (obj == null)
   return false;
  if (getClass() != obj.getClass())
   return false;
  CustomerAccountMtnOrderStruct other = (CustomerAccountMtnOrderStruct) obj;
  if (accountNumber == null) {
   if (other.accountNumber != null)
    return false;
  } else if (!accountNumber.equals(other.accountNumber))
   return false;
  if (cidbOrderId == null) {
   if (other.cidbOrderId != null)
    return false;
  } else if (!cidbOrderId.equals(other.cidbOrderId))
   return false;
  if (customerId == null) {
   if (other.customerId != null)
    return false;
  } else if (!customerId.equals(other.customerId))
   return false;
  if (mtn == null) {
   if (other.mtn != null)
    return false;
  } else if (!mtn.equals(other.mtn))
   return false;
  return true;
 }

public String toString() {
  StringBuffer buffer = new StringBuffer();
  buffer.append("[CustomerAccountMtnOrderStruct:");
  buffer.append(" customerId: ");
  buffer.append(customerId);
  buffer.append(" accountNumber: ");
  buffer.append(accountNumber);
  buffer.append(" mtn: ");
  buffer.append(mtn);
  buffer.append(" cidbOrderId: ");
  buffer.append(cidbOrderId);
  buffer.append(" mtnOrderState: ");
  buffer.append(mtnOrderState);
  buffer.append(" mtnOrderStateDate: ");
  buffer.append(mtnOrderStateDate);
  buffer.append(" mtnOrderType: ");
  buffer.append(mtnOrderType);
  buffer.append(" buddyMtn: ");
  buffer.append(buddyMtn);
  buffer.append("]");
  return buffer.toString();
 }
 
}

Once we have our Mapper class, then let's do the Handler!!!

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry;
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;
import com.vzw.common.globalrouter.dao.db.domain.CustomerAccountMtnOrder;
import com.vzw.common.globalrouter.dao.db.typehandler.struct.CustomerAccountMtnOrderStruct;

public class CustomerAccountMtnPreOrderListTypeHandlerCallBack implements TypeHandlerCallback {
 private static final String STRUCT_NAME = CustomerAccountMtnOrderStruct.SQL_NAME;
 private static final String ARRAY_NAME = "CUST_ACCNT_MTN_PRE_ORDER_LIST";

/*static {
        JdbcTypeRegistry.setType(STRUCT_NAME, OracleTypes.STRUCT);
        JdbcTypeRegistry.setType(ARRAY_NAME, OracleTypes.ARRAY);
    };*/

@SuppressWarnings("unchecked")
 public void setParameter(ParameterSetter parameterSetter, Object param) throws SQLException {
  if (param ==null) {
   parameterSetter.setNull(Types.ARRAY); 
  }else{
   List<CustomerAccountMtnOrder> listCustomerAccountMtnOrder = (ArrayList<CustomerAccountMtnOrder>) param;
   Connection nativeConnection = (Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)parameterSetter.getPreparedStatement().getConnection());
   
   CustomerAccountMtnOrderStruct[] structArray = new CustomerAccountMtnOrderStruct[listCustomerAccountMtnOrder.size()];
   for(int i=0; i<listCustomerAccountMtnOrder.size(); i++){ 
    CustomerAccountMtnOrder customerAccountMtnOrder = listCustomerAccountMtnOrder.get(i);    
    structArray[i] = transformCustomerAccountMtnOrder(customerAccountMtnOrder);     
   }
   
   ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(ARRAY_NAME, nativeConnection);    
      
   ARRAY dataArray = new ARRAY(arrayDescriptor, nativeConnection, structArray); 
   parameterSetter.setArray(dataArray);
  }
 }
 
 public Object valueOf(String arg0) {
  // TODO Auto-generated method stub
  return null;
 }

public Object getResult(ResultGetter resultGetter) throws SQLException {
  // TODO Auto-generated method stub
  return null;
 }
 
 private CustomerAccountMtnOrderStruct transformCustomerAccountMtnOrder(final CustomerAccountMtnOrder customerAccountMtnOrder) throws SQLException {
  CustomerAccountMtnOrderStruct customerAccountMtnOrderStruct = new CustomerAccountMtnOrderStruct();
  customerAccountMtnOrderStruct.setAccountNumber(customerAccountMtnOrder.getAccountNumber());
  customerAccountMtnOrderStruct.setBuddyMtn(customerAccountMtnOrder.getBuddyMtn());
  customerAccountMtnOrderStruct.setCidbOrderId(customerAccountMtnOrder.getCidbOrderId());
  customerAccountMtnOrderStruct.setCustomerId(customerAccountMtnOrder.getCustomerId());
  customerAccountMtnOrderStruct.setMtn(customerAccountMtnOrder.getMtn());
  customerAccountMtnOrderStruct.setMtnOrderState(customerAccountMtnOrder.getMtnOrderState());
  customerAccountMtnOrderStruct.setMtnOrderStateDate(customerAccountMtnOrder.getMtnOrderStateDate());
  customerAccountMtnOrderStruct.setMtnOrderType(customerAccountMtnOrder.getMtnOrderType());
  
  return customerAccountMtnOrderStruct;
 }

}

And this is how it looks my Map (XML)

In case you have to receive the Array from your Stored Proc, you just have to write in your Handler somethig like this:

References: Using iBatis with a stored function returning a Collection Type and Oracle UDT Collections as IN and OUT parameters with iBatis

1 comment:

DeepuApril 18, 2012 at 2:55 AM
Hi ,

I am trying to pass a oracle user defined Type(Array of records) from Java to Oracle PL SQL.
My Application server is Websphere Application Server 6.1 and database is Oracle 10g. I am using ojdbc1.4.jar as thin driver.
The issue is that i am not able to pass the data from Java to PL SQL. AT the PL SQL side, the data is coming as empty.However when i try to run the same code in standalone application(without WAS) i am able to pass the data to PL SQL. So i assume that the issue is between WAS 6.1 and Oracle 10g. Can some one suggest a way out?

Utilities Blog by JuCa Cruz

Monday, September 20, 2010

Pass Collection of Objects with Attributes to a Stored Procedure using iBatis

1 comment:

Twitter