How to convert Oracle user defined Type into java object in spring jdbc stored procedure

Question

I am working on springjdbcTemplate, and all db call will be done through stored procedures. In Oracle 11g I have created one user defined type containing with other type as field inside it as below.

create or replace type WORKER AS Object (NAME VARCHAR2(30),
                                         age NUMBER);

create or replace type WORKER_LIST IS TABLE OF WORKER;

create or replace type MANAGER AS Object(
NAME VARCHAR2(30),
workers WORKER_LIST
);

And at Java side I have created the classes as follows.

public class Worker implements SQLData {
    private String name;
    private int age;
    @Override
    public String getSQLTypeName() throws SQLException {
        return "WORKER";
    }
    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        setName(stream.readString());
        setAge(stream.readInt());

    }
    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeString(getName());
        stream.writeInt(getAge());
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
}

public class Manager implements SQLData  {

    private String name;
    private List<Worker> workers;

    @Override
    public String getSQLTypeName() throws SQLException {
        return "Manager";
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        setName(stream.readString());
        setWorkers((List<Worker>) stream.readObject());
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeString(getName());
        stream.writeObject((SQLData) getWorkers());
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Worker> getWorkers() {
        return workers;
    }

    public void setWorkers(List<Worker> workers) {
            this.workers = workers;
        }
    }

I have mentioned in typeMap about the mappings.

But I am not getting expected results.

Worker type is returned as Struct and List<Worker> is returned as array.

Please let me know what should I have do and what is the standard protocol to get the expected object as I mentioned above. I'm new to JDBCTemplate. Please suggest.

Thanks Ram

Answer 1

I think I've managed to get something working.

You mentioned something about the connection's type map. When using Spring it's difficult to get hold of the database connection in order to add the types to the connection's type map, so I'm not sure what you mean when you write 'I have mentioned in typeMap about the mappings'.

Spring offers one way to add an entry to the connection's type map, in the form of the class SqlReturnSqlData. This can be used to call a stored procedure or function which returns a user-defined type. It adds an entry to the connection's type map to specify the database type of the object and the class to map this object to just before it retrieves a value from a CallableStatement. However, this only works if you only need to map a single type. You have two such types that need mapping: MANAGER and WORKER.

Fortunately, it's not difficult to come up with a replacement for SqlReturnSqlData that can add more than one entry to the connection's type map:

import org.springframework.jdbc.core.SqlReturnType;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

public class SqlReturnSqlDataWithAuxiliaryTypes implements SqlReturnType {

    private Class<?> targetClass;

    private Map<String, Class<?>> auxiliaryTypes;

    public SqlReturnSqlDataWithAuxiliaryTypes(Class<?> targetClass, Map<String, Class<?>> auxiliaryTypes) {
        this.targetClass = targetClass;
        this.auxiliaryTypes = auxiliaryTypes;
    }

    @Override
    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
        Connection con = cs.getConnection();
        Map<String, Class<?>> typeMap = con.getTypeMap();
        typeMap.put(typeName, this.targetClass);
        typeMap.putAll(auxiliaryTypes);
        Object o = cs.getObject(paramIndex);
        return o;
    }
}

The above has been adapted from the source of SqlReturnSqlData. All I've really done is added an extra field auxiliaryTypes, the contents of which gets added into the connection's type map in the call to getTypeValue().

I also needed to adjust the readSQL method of your Manager class. The object you read back from the stream will be an implementation of java.sql.Array. You can't just cast this to a list. Sadly, getting this out is a little fiddly:

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        setName(stream.readString());
        Array array = (Array) stream.readObject();
        Object[] objects = (Object[]) array.getArray();
        List<Worker> workers = Arrays.stream(objects).map(o -> (Worker)o).collect(toList());
        setWorkers(workers);
    }

(If you're not using Java 8, replace the line with Arrays.stream(...) with a loop.)

To test this I wrote a short stored function to return a MANAGER object:

CREATE OR REPLACE FUNCTION f_get_manager
RETURN manager
AS
BEGIN
  RETURN manager('Big Boss Man', worker_list(worker('Bill', 40), worker('Fred', 36)));
END;
/

The code to call this stored function was then as follows:

    Map<String, Class<?>> auxiliaryTypes = Collections.singletonMap("WORKER", Worker.class);
    SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
        .withSchemaName("my_schema")
        .withFunctionName("f_get_manager")
        .declareParameters(
            new SqlOutParameter(
                "return",
                OracleTypes.STRUCT,
                "MANAGER",
                new SqlReturnSqlDataWithAuxiliaryTypes(Manager.class, auxiliaryTypes)));

    Manager manager = jdbcCall.executeFunction(Manager.class);
    // ... do something with manager.

This worked, in that it returned a Manager object with two Workers in it.

---

Finally, if you have stored procedures that save a Manager object to the database, be aware that your Manager class's writeSQL method will not work. Unless you've written your own List implementation, List<Worker> cannot be casted to SQLData. Instead, you'll need to create an Oracle array object and put the entries in that. That however is awkward because you'll need the database connection to create the array, but that won't be available in the writeSQL method. See this question for one possible solution.