SQL Objects, JDeveloper, JPublisher and Java

This is one for my application developer friends.

Say your application uses stored procedures. Not just any stored procedures, but ones that accept arrays or SQL Object types (User Defined Types - UDTs).

One thing you have to do is map a java object to that SQL Object before you can pass it back to the stored procedure. I can't remember the specifics, but I can show you how to "automatically" create the Java code that maps to the SQL Object.

First, create type UDT.

If will be a table of a record...is it called a multi-dimensional array? Whatever.

CREATE TYPE r_record IS OBJECT
(
  id NUMBER(10),
  col1 VARCHAR2(30),
  col2 VARCHAR2(30),
  col3 VARCHAR2(30)
)
/

CREATE TYPE t_table AS TABLE OF R_RECORD
/

I'll create a procedure that accepts T_TABLE as an input parameter. It won't do anything, but just to give you an idea.

CREATE OR REPLACE
PROCEDURE test( p_t_table IN T_TABLE )
IS
BEGIN
  NULL;
END test;
/

Now go into JDeveloper and through the Database Navigator, select the schema you placed these objects.



Right click on the object and select Generate Java (as shown above).

You'll then be presented with an options menu:



It's all foreign to me so I accept the defaults. I do notice however that it's pretty customizable, for instance, you can select how you want to Number Types to be generated, either objectjdbc, oracle, jdbc or bigdecimal. You'll know better about what this means than I will.



From that, you get 3 files, TTable.java which maps to the TABLE OF R_RECORD, RRecord.java which maps to the SQL Type R_RECORD, and finally RRecordRef.java...which I don't really know what it does. I'm sure you will though.

Here's the code generated, in order.

TTable.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.jpub.runtime.MutableArray;

public class TTable implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "CJUSTICE.T_TABLE";
  public static final int _SQL_TYPECODE = OracleTypes.ARRAY;

  MutableArray _array;

private static final TTable _TTableFactory = new TTable();

  public static ORADataFactory getORADataFactory()
  { return _TTableFactory; }
  /* constructors */
  public TTable()
  {
    this((RRecord[])null);
  }

  public TTable(RRecord[] a)
  {
    _array = new MutableArray(2002, a, RRecord.getORADataFactory());
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _array.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    TTable a = new TTable();
    a._array = new MutableArray(2002, (ARRAY) d, RRecord.getORADataFactory());
    return a;
  }

  public int length() throws SQLException
  {
    return _array.length();
  }

  public int getBaseType() throws SQLException
  {
    return _array.getBaseType();
  }

  public String getBaseTypeName() throws SQLException
  {
    return _array.getBaseTypeName();
  }

  public ArrayDescriptor getDescriptor() throws SQLException
  {
    return _array.getDescriptor();
  }

  /* array accessor methods */
  public RRecord[] getArray() throws SQLException
  {
    return (RRecord[]) _array.getObjectArray(
      new RRecord[_array.length()]);
  }

  public RRecord[] getArray(long index, int count) throws SQLException
  {
    return (RRecord[]) _array.getObjectArray(index,
      new RRecord[_array.sliceLength(index, count)]);
  }

  public void setArray(RRecord[] a) throws SQLException
  {
    _array.setObjectArray(a);
  }

  public void setArray(RRecord[] a, long index) throws SQLException
  {
    _array.setObjectArray(a, index);
  }

  public RRecord getElement(long index) throws SQLException
  {
    return (RRecord) _array.getObjectElement(index);
  }

  public void setElement(RRecord a, long index) throws SQLException
  {
    _array.setObjectElement(a, index);
  }
}

RRecord.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class RRecord implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "CJUSTICE.R_RECORD";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  protected static int[] _sqlType =  { 2,12,12,12 };
  protected static ORADataFactory[] _factory = new ORADataFactory[4];
  protected static final RRecord _RRecordFactory = new RRecord();

  public static ORADataFactory getORADataFactory()
  { return _RRecordFactory; }
  /* constructors */
  protected void _init_struct(boolean init)
  { if (init) _struct = new MutableStruct(new Object[4], _sqlType, _factory); }
  public RRecord()
  { _init_struct(true); }
  public RRecord(java.math.BigDecimal id, String col1, String col2, String col3) throws SQLException
  { _init_struct(true);
    setId(id);
    setCol1(col1);
    setCol2(col2);
    setCol3(col3);
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }


  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(RRecord o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    if (o == null) o = new RRecord();
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }
  /* accessor methods */
  public java.math.BigDecimal getId() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(0); }

  public void setId(java.math.BigDecimal id) throws SQLException
  { _struct.setAttribute(0, id); }


  public String getCol1() throws SQLException
  { return (String) _struct.getAttribute(1); }

  public void setCol1(String col1) throws SQLException
  { _struct.setAttribute(1, col1); }


  public String getCol2() throws SQLException
  { return (String) _struct.getAttribute(2); }

  public void setCol2(String col2) throws SQLException
  { _struct.setAttribute(2, col2); }


  public String getCol3() throws SQLException
  { return (String) _struct.getAttribute(3); }

  public void setCol3(String col3) throws SQLException
  { _struct.setAttribute(3, col3); }
}

RRecordRef.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class RRecordRef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "CJUSTICE.R_RECORD";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

private static final RRecordRef _RRecordRefFactory = new RRecordRef();

  public static ORADataFactory getORADataFactory()
  { return _RRecordRefFactory; }
  /* constructor */
  public RRecordRef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    RRecordRef r = new RRecordRef();
    r._ref = (REF) d;
    return r;
  }

  public static RRecordRef cast(ORAData o) throws SQLException
  {
     if (o == null) return null;
     try { return (RRecordRef) getORADataFactory().create(o.toDatum(null), OracleTypes.REF); }
     catch (Exception exn)
     { throw new SQLException("Unable to convert "+o.getClass().getName()+" to RRecordRef: "+exn.toString()); }
  }

  public RRecord getValue() throws SQLException
  {
     return (RRecord) RRecord.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(RRecord c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

How do you integrate all that? I'm not really sure, but Marc recently wrote up a piece on using this method to tie into SQLUnit, pretty nice one too.