How do you write arrays to an Oracle 10g XE db usi

Question

I have looked for the answer to this high and low but cannot get the answer.

Basically I have an object I am writing to my db using iBatis. This works fine with primitive types like strings, int's etc but one of the attributes of my object is an array of other objects. I would like to be able to persist this and then later call the 'selectById' statement and retrieve the full object including the array.

Here is the code I have so far:

Mapper.xml

  <insert id="insertTrade" parameterClass="TradeObject">
insert into TESTTABLE (
  ORDERID,
  MAXPX,
  COMMISSION,
  ACCOUNTGRP )
values (
  #orderID#, #maxPx#, #commission#, #accountGrp#
)

accountGrp is my array but its currently throwing an error. The statement works fine without this field.

The java is like so:

  public static void insertTrade (Trade obj) throws SQLException {
  logger.debug("inserting trade. Order Id: " + obj.toString());
sqlMapper.insert("insertTrade", obj);

}

Thanks for any help in advance!!

Answer 1

I've done with Mybatis3, should be similar in the old iBatis stuff. To get the JDBC stuff, read this thread. It's a huge thread, but it is there. Look for "ArrayDescriptor".

Basicually, you need to write a TypeHandler. In the TypeHandler, call setArray. Should be something like this in mybatis 3.x. Your working with a List, just convert with the toArray method. This is an example, where the parameter is a String[].

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;   
.....
public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException
{
 //null check?

   ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STRARRAY ", ps.getConnection());
   ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), parameter);
   ps.setArray(i, oracleArray);
}

and maybe something like this in ibatis,

public void setParameter(ParameterSetter setter, Object parameter) throws SQLException
{
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STRARRAY", setter.getPreparedStatement().getConnection());
    ARRAY oracleArray = new ARRAY(desc, setter.getPreparedStatement().getConnection(), parameter);
    setter.setArray(oracleArray);
}

Make your you've built a type, like it says in that thread.

i.e.

CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)

Then in the SQL map, make sure to reference the type handler.