Java SQLData - Cast to user object with a list/array?

Question

I'm learning on how to use SQLData and having an issue with casting back to my object.

My Oracle Types looks something like this:

CREATE OR REPLACE TYPE activities_t AS OBJECT
(
   list   activity_list_t;
);

CREATE OR REPLACE TYPE activity_list_t AS TABLE OF activity_t;

CREATE OR REPLACE TYPE activity_t AS OBJECT
(
   startDate  DATE;
   endDate    DATE;
);

And my Java looks like this:

public class Activities implements SQLData {
    private String sqlType = "ACTIVITIES_T";
    List<Activity> list;

    // must have default ctor!
    public Activities() {
    }

    public String getSQLTypeName() throws SQLException
    { 
        return sqlType; 
    }

    public List getList() {
        return list;
    }

    public void setList(List list) {
        this.list = list;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException 
    {
        Array a = stream.readArray();
        // :(
    }

    public void writeSQL(SQLOutput stream) throws SQLException 
    {
        // stream.writeArray(this.list);
    }
}

I've tried a few things in readSQL but I am not having much success - what am I missing?

I am calling a PLSQL stored procedure which has an OUT parameter of "activities_t" using JDBC:

Map map = connection.getTypeMap();
map.put("ACTIVITIES_T", Class.forName("Activities"));
connection.setTypeMap(map);

callableStatement = connection.prepareCall("{call GET_ACTIVITIES(?)}");
callableStatement.execute();

Thanks! Steve

(most of the above is from memory as the code is at work...)

Answer 1

You'll need to add a type mapping for the type ACTIVITY_T as well as the one for ACTIVITIES_T. It's not clear from your question whether you've already done this.

Let's assume you've done this and created a class called Activity which implements SQLData as well. Once you've done that, the following should suffice to read the activity list within Activities:

    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        Array array = stream.readArray();
        this.list = new ArrayList<Activity>();
        for (Object obj : (Object[])array.getArray()) {
            list.add((Activity)obj);
        }
    }

Answer 2

Tips:

• JDBC APIs are case-sensitive with regard to type names; you will see a Unable to resolve type error if your type name does not exactly match. Oracle will uppercase your type name unless you double-quoted the name in its create statement.
• You may need to specify SCHEMA.TYPE_NAME if the type isn't in your default schema.
• Remember to grant execute on types if the user you are connecting with is not the owner.
  If you have execute on the package, but not the type, getArray() will throw an exception when it tries to look for type metadata.

getArray()

My solution is essentially the same as Luke's. However, I needed to provide a type mapping when getting the array: array.getArray(typeMap) You can also set a default type map on the Connection, but this didn't work for me.

When calling getArray() you get an array of the object type, i.e. the SQLData implementation you created that represents activity_t

Here is a generic function you might find useful:

public static <T> List<T> listFromArray(Array array, Class<T> typeClass) throws SQLException {
    if (array == null) {
        return Collections.emptyList();
    }
    // Java does not allow casting Object[] to T[]
    final Object[] objectArray = (Object[]) array.getArray(getTypeMap());
    List<T> list = new ArrayList<>(objectArray.length);
    for (Object o : objectArray) {
        list.add(typeClass.cast(o));
    }
    return list;
}

writeArray()

Figuring out how to write an array was frustrating, Oracle APIs require a Connection to create an Array, but you don't have an obvious Connection in the context of writeSQL(SQLOutput sqlOutput). Fortunately, this blog has a trick/hack to get the OracleConnection, which I've used here.

When you create an array with createOracleArray() you specify the list type for the type name, NOT the object type. i.e. activity_list_t

Here's a generic function for writing arrays. In your case, listType would be "activity_list_t" and you would pass in a List<Activity>

public static <T> void writeArrayFromList(SQLOutput sqlOutput, String listType, @Nullable List<T> list) throws SQLException {
    final OracleSQLOutput out = (OracleSQLOutput) sqlOutput;
    OracleConnection conn = (OracleConnection) out.getSTRUCT().getJavaSqlConnection();
    conn.setTypeMap(getTypeMap());  // not needed?
    if (list == null) {
        list = Collections.emptyList();
    }
    final Array array = conn.createOracleArray(listType, list.toArray());
    out.writeArray(array);
}

Note: at one point I thought setTypeMap was required, but now when I remove that line my code still works, so I'm not sure if it's necessary.