Utilities Blog by JuCa Cruz: Array

Showing posts with label Array. Show all posts

Monday, September 20, 2010

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

Friday, September 17, 2010

Pass Collection to Stored Procedure using iBatis

Today I had a really crazy idea: "Why not making my query work with a list of parameters like I do with Hibernate using the operator IN???"
It sounds simple, Isn't it??? Ok, then try to do it with an Stored Procedure in Oracle :P
Then, this is my Stored Proc (I had to create a Type to have support for Arrays in Oracle, because an Array is not a simple-provided data type): As you can see, my dear visitor, I'm expecting some simple parameters and I'm returning a ResultSet, really straight forward, but the problem comes with the Array of NUMBER (that's why I created my

TYPE NUMBER_ARRAY AS TABLE OF NUMBER

)

The solution to this problem is easier than it seems, just create a Handler dude!!!

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

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

public class NumberArrayTypeHandlerCallBack implements TypeHandlerCallback {
 
 @SuppressWarnings("unchecked")
 public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
  if(parameter == null) {
   setter.setNull(Types.ARRAY);
  }
  else if(parameter instanceof ArrayList) {
   Statement stmt = setter.getPreparedStatement();
   Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection());
   
   ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection);
   parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray());
  }
  setter.setObject(parameter);
 }
 
 public Object getResult(ResultGetter getter) throws SQLException {
  return null;  
 }
 
 public Object valueOf(String arg0) {
  return null;
 }

}

The magic begins when I transform the Connection (because I have to work at the connection level) provided by the Application Server (WebSphere on this case - If this happens to me on another App Server or with another Connection Pool, I'm going to be in trouble, because the issue will be how to retrieve a Native java.sql.Connection, but that will be another story).
Let's analyze this monster:
* Get Native Connection
*Create my ArrayDescriptor for my Type (these NUMBER_ARRAY is my TYPE I created previously)
*Create my parameter, an ARRAY (al fin y al cabo) and we're set!

Finally this is my mapping file for my stored procedure

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<procedure id="retrieve_mtn_eligibility_state" parameterMap="objectParameters" resultMap="resultSetMap" >  
  {call ${env.qualifier}.retrieve_mtn_eligibility_state(?,?,?,?)}
 </procedure>

</sqlMap>

And my code lived happily ever after!!! Abur!!!