Working With Custom Oracle Object Types In IBatis

Today we got a requirement for working With Custom Oracle Object Types In IBatis.

One of our stored procedure takes a "table of object type" as one of the parameter.

Our requirement is to call this stored proceudure from IBatis.

I would like to thank the original author found in below link as i found solution only after following his instructions.

http://mail-archives.apache.org/mod_mbox/ibatis-user-java/200802.mbox/%3C8B243E70CE6BFB438E3CDE03E1E4F698925024@zil01exm62.ds.mot.com%3E

create or replace TYPE JOB_MEASURES_OBJ_TYPE AS OBJECT
( 
MEASURE_ID NUMBER,
SERVICE_CODE VARCHAR2(30 BYTE),
QUANTITY NUMBER,
RECEIPT_DATE DATE,
COUNTER NUMBER,
JOB_ID NUMBER,
ERROR_MSG VARCHAR2(4000 BYTE),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(30 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY VARCHAR2(30 BYTE)
);

create or replace TYPE JOB_MEASURES_TAB_TYPE AS TABLE OF JOB_MEASURES_OBJ_TYPE;

 PROCEDURE pr_insert_job_measures_row
(
  pi_tbl_rec  IN JOB_MEASURES_TAB_TYPE,
  po_message_type   OUT NUMBER,
  po_message  OUT VARCHAR2
) IS

v_rec_index   PLS_INTEGER;

  BEGIN
    
       v_rec_index       := pi_tbl_rec.FIRST;
       
       FOR v_rec_index IN pi_tbl_rec.FIRST .. pi_tbl_rec.LAST LOOP

     INSERT INTO dummy
        JOB_ID,
       ) 
       values 
       (
        pi_tbl_rec(v_rec_index).JOB_ID,
       );

  END LOOP;
   END;

IBatis Xml file entries goes like this

 <typeAlias type="JobMeasuresTypeHandler" alias="JobMeasuresTypeHandler"   />
    
  <parameterMap id="insertJobMeasuresProcParameters" class="java.util.Map">
    <parameter property="jobMeasuresTableRec"      typeHandler="JobMeasuresTypeHandler" mode="IN" />
    <parameter property="messageType"              jdbcType="NUMERIC" javaType="int"              mode="OUT"/>
    <parameter property="message"                  jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
  </parameterMap>
  
  <procedure id="insertJobMeasuresProc" parameterMap="JobMeasures.insertJobMeasuresProcParameters">
    {call pr_insert_job_measures_row(?,?,?)}
  </procedure>

Our Java Code goes like this

JobMeasureOracleDomainObject.java

public class JobMeasureOracleDomainObject implements ORAData,ORADataFactory {

 private Long measureId;
 private String serviceCode;
 private Long quantity;
 private Date receiptDate;
 private Long counter;
 private Long jobId;
 private String errorMsg;
 private Date creationDate;
 private String creationBy;
 private Date lastUpdatedDate;
 private String lastUpdatedBy;
 
    public static final String _SQL_NAME = "JOB_MEASURES_OBJ_TYPE";
    public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
    protected MutableStruct _struct;
    
    static int[] _sqlType = {OracleTypes.NUMBER, OracleTypes.CHAR,OracleTypes.NUMBER,OracleTypes.DATE,OracleTypes.NUMBER
     ,OracleTypes.NUMBER,OracleTypes.CHAR,OracleTypes.DATE,OracleTypes.CHAR,OracleTypes.DATE};
    
    static ORADataFactory[] _factory = new ORADataFactory[11];
    
    static final JobMeasureOracleDomainObject _JobMeasureFactory = new JobMeasureOracleDomainObject();
    // logger (if you wish)
    //private final Log log = LogFactory.getLog(JobMeasureOracleDomainObject.class);

    private static final long serialVersionUID = -7710368639791237838L;
 
    /* constructor */
    protected JobMeasureOracleDomainObject(boolean init) {
        if (init) {
            _struct = new MutableStruct(new Object[11], _sqlType,_factory);
        }
    }
    
    public JobMeasureOracleDomainObject() {
     this(true);
    }
    
    /* ORAData interface */
    public Datum toDatum(Connection conn) throws SQLException {
        System.out.println("Calling method toDatum...");
        Datum d = _struct.toDatum(conn, _SQL_NAME);
        return d;
    }
    
    /* ORADataFactory interface */
    public ORAData create(Datum d, int sqlType) throws SQLException {
        return create(null, d, sqlType);
    }
    
    protected ORAData create(JobMeasureOracleDomainObject o, Datum d, int sqlType) throws SQLException {
     System.out.println("Calling method create...");
     if (d == null) {
      return null;
     }
     if (o == null) {
      o = new JobMeasureOracleDomainObject(false);
     }
     o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
     return o;
    }
    
    public static ORADataFactory getORADataFactory() {
        return _JobMeasureFactory;
    }
    
 public Long getMeasureId() throws SQLException {
  return (Long) _struct.getAttribute(0);
 }
 public void setMeasureId(Long measureId) throws SQLException{
  this.measureId = measureId;
  _struct.setAttribute(0, this.measureId);
 }
     ........
}

JobMeasuresTypeHandler.java

public class JobMeasuresTypeHandler implements TypeHandlerCallback
{
 

    private static final String JOB_MEASURES_TAB = "JOB_MEASURES_TAB_TYPE";
    private static final String JOB_MEASURES_REC = "JOB_MEASURES_OBJ_TYPE";

    static {
        JdbcTypeRegistry.setType(JOB_MEASURES_REC, OracleTypes.STRUCT);
        JdbcTypeRegistry.setType(JOB_MEASURES_TAB, OracleTypes.ARRAY);
    };
 
    @SuppressWarnings("unchecked")
    public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
        System.out.println("calling setParameter..." + parameter);
        System.out.println("calling setParameter..." + parameter.getClass());
        try {
            List<JobMeasureOracleDomainObject> jobMeasuresList = (List<JobMeasureOracleDomainObject>) parameter;
            // System.out.println("Converting list to array...");
            JobMeasureOracleDomainObject[] recArray = new JobMeasureOracleDomainObject[jobMeasuresList.size()];
            for (int i = 0; i < recArray.length; i++) {
                recArray[i] = jobMeasuresList.get(i);
            }
            System.out.println("Converted list to array.");
            Connection conn = setter.getPreparedStatement().getConnection();
            if (conn instanceof DelegatingConnection) {
                DelegatingConnection dcon = (DelegatingConnection) conn;
                conn = dcon.getInnermostDelegate();
            }
            conn = (OracleConnection) conn;
            //ARRAY array=createJobMeasuresDataARRAY(conn);
            ArrayDescriptor arrayDescriptor =ArrayDescriptor.createDescriptor(JOB_MEASURES_TAB, conn);
            ARRAY array = new ARRAY(arrayDescriptor, conn, recArray);
            setter.setArray(array);
        } catch (SQLException sqle) {
            System.out.println("SQLException: " + sqle);
            throw sqle;
        }
    }
    
    public Object getResult(ResultGetter getter) throws SQLException {
     System.out.println("calling getResult..." + getter.getObject());
     return getter.getObject();
    }
 
    /**
     * Nothing here can help us anyway...
     */
    public Object valueOf(String arg0) {
     System.out.println("calling valueOf..." + arg0);
     if (arg0 == null) {
            return new ArrayList<JobMeasureOracleDomainObject>();
        }
        return arg0;
    }
}

ObjectTypeArraysJobMeasuresTest.java


public class ObjectTypeArraysJobMeasuresTest {

 public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
  
     new ObjectTypeArraysJobMeasuresTest().insertJobMeasuresProcParameters();
 }
 
 private void insertJobMeasuresProcParameters() throws IOException, SQLException, ClassNotFoundException {
  //Connection   conn     = null;
  Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
  SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
  
  List<JobMeasureOracleDomainObject> jobMeasuresList=new ArrayList<JobMeasureOracleDomainObject>();
  JobMeasureOracleDomainObject jobMeasureOracleDomainObject=new JobMeasureOracleDomainObject();
  jobMeasureOracleDomainObject.setJobId(999L);
  jobMeasureOracleDomainObject.setMeasureId(999L);
  jobMeasuresList.add(jobMeasureOracleDomainObject);

  Map parameterMap = new HashMap();
  parameterMap.put("jobMeasuresTableRec", jobMeasuresList);
  sqlMap.update("JobMeasures.insertJobMeasuresProc", parameterMap);

  Integer resultCode = (Integer) parameterMap.get("messageType");
  String resultMessage = (String)parameterMap.get("message");
  System.out.println(resultCode + ":" + resultMessage);
 }
}