Accessing Oracle User-Defined Type with Spring JDBC

It's not so long I wrote some single-purpose utility which called a stored procedure in Oracle database. The procedure returned as its result an user-defined data type.

Definition of the type looked approximately as this one:

CREATE OR REPLACE TYPE Summary as OBJECT 
( 
  ID Number(10),
  Sum1 NUMBER(20,3),
  Sum2 NUMBER(20,3)

  CONSTRUCTOR FUNCTION Summary RETURN SELF AS RESULT
);

I could have specified the mappings between Oracle object and Java class by implementing the SQLData interface, the approach supported by Spring Data. But I had only Spring JDBC available so I used the SqlReturnType.

First Maven dependencies:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>org.bithill.projectx</groupId>
 <artifactId>MyTestUtil</artifactId>
 <version>1.0</version>

 <dependencies>

  <dependency>
    <groupId>com.oracle.oracle</groupId>
    <artifactId>ojdbc14</artifactId>
    <version>10.2.0.4</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>3.0.7.RELEASE</version>
  </dependency>

 </dependencies>
   
</project> 

The utility to read the data is really simple - just load the driver, define the SimpleJdbcCall and call it and get the result. Only thing you have to remember,is to register the hendler for the user-defined type.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.sql.SQLException;

import static java.lang.System.out;
import static oracle.jdbc.OracleTypes.*;

public class Main
{
   public static void main (String... args) throws SQLException 
   {
      String connectionString = args[0]; // JDBC URL

      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName("oracle.jdbc.OracleDriver");
      ds.setUrl(connectionString);

      SimpleJdbcCall sumProcCall = new SimpleJdbcCall(new JdbcTemplate(ds))
        .withSchemaName("SCHEMA").withCatalogName("PKG")
        .withProcedureName("GET_SUMMARY")
        .declareParameters
        (
          // IN params
          new SqlParameter("P_Id", NUMBER),
          new SqlParameter("P_OrgUnit", VARCHAR),

          // OUT params
          new SqlOutParameter("P_Summary", STRUCT, "SUMMARY", new SummaryHandler() ),
          new SqlOutParameter("P_err_msg", VARCHAR),
          new SqlOutParameter("P_err_code", NUMBER)
        );

       (Summary)sumProcCall.execute
       ( Integer.valueOf(1),"ou.subsidiary1").get("P_Summary");

} 

Here is the type handler converting the raw information from Oracle's STRUCT to a Java class.

import oracle.sql.STRUCT;
import org.springframework.jdbc.core.SqlReturnType;

import java.sql.CallableStatement;
import java.sql.SQLException;

public class SummaryHandler implements SqlReturnType
{
    // struct data from jdbc
    private Object[] data;

    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName)
    throws SQLException
    {
        data = ((STRUCT)cs.getObject(INDEX_OF_SUMMARY_PARAMETER)).getAttributes();

        Summary result = new Summary
        (new SummaryLine( (BigDecimal)data[0]) /*, ...*/);

        return result;
    }
}