How do I pass a user-defined type as an input to a stored procedure?
After a fair bit of mucking around, I was able to develop a solution. A few observations:
- There is not much documentation about how to do this on the web.
- It seems to me that using user-defined types as inputs is not well supported.
- I found I had to use a
Struct
which was counter-intuitive (as only arrays were used for outputs). - The
SQLData
interface was not used, ie.writeSQL()
was never called as I found I had to build the struct manually.readSQL()
is called when mapping outputs. - I had to use DB-specific code for array creation, in my case this meant Oracle classes.
It's possible I may be going about things the wrong way, so I'd welcome comments on my solution.
public void useExample(Example example) throws SQLException {
String query = "begin example.useExample(?); end;";
Connection connection = jdbcTemplate.getDataSource().getConnection();
CallableStatement callableStatement = connection.prepareCall(query);
Map<String, Class<?>> typeMap = connection.getTypeMap();
typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
connection.setTypeMap(typeMap);
// Manually convert the example object into an SQL type.
Object[] exampleAttributes = new Object[]{example.ID, example.Name};
Struct struct = connection.createStruct(type.getObjectType(), exampleAttributes);
// Build the array using Oracle specific code.
DelegatingConnection<OracleConnection> delegatingConnection = (DelegatingConnection<OracleConnection>) new DelegatingConnection(connection);
OracleConnection oracleConnection = (OracleConnection) delegatingConnection.getInnermostDelegate();
Object[] data = new Object[]{struct};
Array array oracleConnection.createOracleArray(Example.SQL_TABLE_TYPE_NAME, data);
// Set the input value (finally).
callableStatement.setObject("p_example", array);
callableStatement.execute();
}