7. Advanced Data Types

The Oracle database and the PL/SQL language used for stored procedures in Oracle has built in support for some advanced data types. These data types can't easily be accessed using standard JDBC APIs, so it is necessary to rely on Oracle's JDBC extensions like ARRAY and STRUCT and the APIs that are used to access them.

The JDBC framework provided with the Spring Framework supports most of this already via SqlTypeValue and SqlReturnType. The SqlTypeValue interface is used to pass IN parameter values. This is easiest accomplished by extending the AbstractSqlTypeValue class. Here you need to implement the createTypeValue method. In this method you have access to the current connection, the SqlType and the type name for any custom processing that is necessary. When you retrieve advanced data types you need to implement the SqlReturnType interface and pass that implementation into the SqlOutParameter constructor. The SqlReturnType interface has one method named getTypeValue that must be implemented. Here you have access to the CallableStatement that is currently executing as well as the parameterIndex, the SqlType and the type name for customizing the processing.

When implementing these interfaces there is some boilerplate type code that is necessary and it makes your data access code look unnecessarily complex. That is the reason why we are providing a number of Oracle specific implementations that can handle the Oracle advanced types. The usage of these type handlers is documented in this chapter. These features are specifically targeted for an Oracle environment so teams can take advantage of this support and have a reference for best practices for the use of these features in an Oracle/Spring environment.

We will use the following simple table for all the examples in this chapter. This table is used to store some basic information about actors.

CREATE TABLE actor (
    id NUMBER(10),
    name VARCHAR2(50),
    age NUMBER,
    PRIMARY KEY (id));

7.1 Using a STRUCT parameter

When your stored procedures has parameters that are declared using custom object types that aren't part of the standard JDBC types they are managed using JDBC Struct objects. When working with Oracle it's easier to work with Oracle's extension to Struct which is oracle.sql.STRUCT.

For the STRUCT examples we will use the following type declaration.

CREATE OR REPLACE TYPE actor_type
    AS OBJECT (id NUMBER(10), name VARCHAR2(50), age NUMBER);

The data contained in a STRUCT parameter can be accessed in two ways. Either using the SQLData interface which is part of the JDBC specification, or by using Oracle specific calls accessing the attributes directly. We will cover both methods.

Now we will look at the sample procedures used for this example. First one is the procedure we use to add the actor data.

CREATE OR REPLACE PROCEDURE add_actor (in_actor IN actor_type)
AS
BEGIN
  INSERT into actor (id, name, age) VALUES(in_actor.id, in_actor.name, in_actor.age);
END;

This procedure has one IN parameter (in_actor) of object type actor_type.

Next we show the procedure used to retrieve the actor data.

CREATE OR REPLACE PROCEDURE get_actor (in_actor_id IN NUMBER, out_actor OUT actor_type)
AS
BEGIN
  SELECT actor_type(id, name, age) INTO out_actor FROM actor WHERE id = in_actor_id;
END;

This procedure has two parameters, one IN parameter (in_actor_id) that is the id of the actor to retrieve and one OUT parameter (out_actor) of type actor_type to pass back the data retrieved.

The last piece we will cover here is the Java class that represents the type we are accessing. Here is the Actor implementation used in this example. It has the Java equivalent of the variables we defined for the type in the database. We also have setters and getters for all fields.

package org.springframework.data.jdbc.samples;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.math.BigDecimal;

public class Actor {

    private Long id;
    private String name;
    private int age;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    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 String toString() {
    return "Actor: [" + id + "] " + name + " " + age;
    }

}

7.1.1 Using an SQLData implementation for a STRUCT IN parameter

For the examples that use SQLData we first need a Java class that implements the SQLData interface. For this example we create an SqlActor class that extends the Actor class shown earlier and provides the SQLData implementation for our ACTOR_TYPE.

package org.springframework.data.jdbc.samples;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.math.BigDecimal;

public class SqlActor extends Actor implements SQLData {

    public String getSQLTypeName() throws SQLException {
        return "ACTOR_TYPE"; 1
    }

    public void readSQL(SQLInput sqlInput, String string) throws SQLException { 2
        setId(Long.valueOf(sqlInput.readLong()));
        setName(sqlInput.readString());
        setAge(sqlInput.readInt());
    }

    public void writeSQL(SQLOutput sqlOutput) throws SQLException { 3
        sqlOutput.writeLong(getId().longValue());
        sqlOutput.writeString(getName());
        sqlOutput.writeInt(getAge());
    }

}

1

Here we specify the ACTOR_TYPE advanced data type as the type supported by this implementation. Note: Since Oracle's metadata is stored using all caps, unless the name was explicitly defined as mixed case using quotes, we define the type name here as all caps.

2

Here we specify the implementation used to map data between the ACTOR_TYPE advanced data type and the Actor class during a read operation.

3

Here we specify the implementation used to map data between the Actor class and the ACTOR_TYPE advanced data type during a write operation.

As you can see, the SQLData implementation is fairly straightforward. We implemented the three methods required in the SQLData interface. These methods are getSQLTypeName, readSQL and writeSQL.

Now we can move on to actually call the stored procedure. First example is using the newer SimpleJdbcCall API but the SqlParameter would be the same if you used the classic StoredProcedure approach. We create the SimpleJdbcCall and in the declareParameters method call we pass in an SqlParameter that defines the parameter with the type as OracleTypes.STRUCT and a type name of ACTOR_TYPE to match what it is defined in the database. Note that the type name is defined here as all caps since that is how it is stored in the database metadata. Any type names declared here are case sensitive and must match what is actually stored in the database metadata.

    this.addSqlActorCall =
            new SimpleJdbcCall(dataSource).withProcedureName("add_actor")
                .declareParameters(
                    new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE")); 1

1

We define the SqlParameter with parameter name, the Oracle type and the type name as it is declared in the database.

Next we look at the code that executes this SimpleJdbcCall.

    public void addSqlActor(final SqlActor actor) {
        Map in = Collections.singletonMap("in_actor", actor); 1
        addSqlActorCall.execute(in);
    }

1

We execute the call by passing in a Map containing any in parameters - in this case the actor object.

What happens here is that the JDBC driver access the data in the passed in Actor instance via the SQLData interface and maps that data to the ACTOR_TYPE passed in to the stored procedure. There is no need to implement anything else since this is all handled by the JDBC layer.

Please note that since SimpleJdbcCall is relying on database metadata, the parameter names used for the input must match the names used when declaring the stored procedure. They are however not case sensitive, only the type names are case sensitive.

If you prefer to use the classic StoredProcedure class then the equivalent configuration would look like this:

    private class AddSqlActorProc extends StoredProcedure {

        public AddSqlActorProc(DataSource dataSource) {
            super(dataSource, "add_actor");
            declareParameter(new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE"));
        }

        public void execute(Actor actor) {
            Map in = Collections.singletonMap("in_actor", actor);
            this.execute(in);
        }

    }

7.1.2 Using SqlReturnSqlData with an SQLData implementation from a STRUCT OUT parameter

Now we will call the stored procedure that retrieves actor data. We are still using the newer SimpleJdbcCall API. We create the SimpleJdbcCall and in the declareParameters call we pass in an SqlOutParameter that uses an SqlReturnType to handle the configuration necessary for the mapping between the Oracle type and the Java type which is still the SqlActor. We also need to link between the Actor class and the ACTOR_TYPE since the JDBC driver is not aware of this relationship when we are reading data from the database. This is done by declaring a SqlReturnSqlData class and passing in the target class in the constructor.

        this.getSqlActorCall =
                new SimpleJdbcCall(dataSource).withProcedureName("get_actor")
                    .declareParameters(
                        new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE", 1
                            new SqlReturnSqlData(SqlActor.class)) 2
                    );

1

We define the parameter name and the Oracle type and the type name as it is declared in the database.

2

Here we define the SqlReturnSqlData and the desired target class.

Next we look at the code that executes this SimpleJdbcCall.

    public SqlActor getSqlActor(int id) {
        Map in = Collections.singletonMap("in_actor_id", id);
        return getSqlActorCall.executeObject(SqlActor.class, in); 1
    }

1

We execute the call by passing in a Map containing any in parameters. The executeObject method returns an SqlActor containing the data returned by the stored procedure call.

If you prefer to use the classic StoredProcedure class then the equivalent configuration would look like this:

    private class GetSqlActorProc extends StoredProcedure {

        public GetSqlActorProc(DataSource dataSource) {
            super(dataSource, "get_actor");
            declareParameter(new SqlParameter("in_actor_id", Types.NUMERIC));
            declareParameter(
                new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE",
                    new SqlReturnSqlData(SqlActor.class))
            );
        }

        public SqlActor execute(Long id) {
            Map in = Collections.singletonMap("in_actor_id", id);
            Map out = this.execute(in);
            return (SqlActor) out.get("out_actor");
        }

    }

7.1.3 Setting STRUCT attribute values using SqlStructValue for an IN parameter

An alternate access technique is to use the Struct interface to access a generic collection of attributes representing the type. The SqlStructValue implementation will map properties in a JavaBean to the corresponding attributes of the STRUCT so there is no need to provide custom mapping code. The following example will perform the same operations as the previous example using this alternate technique.

The SimpleJdbcCall declaration for the "add_actor" call looks the same.

    this.addActorCall =
            new SimpleJdbcCall(dataSource).withProcedureName("add_actor")
                .declareParameters(
                    new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE")); 1

1

We define the SqlParameter with parameter name, the Oracle type and the type name as it is declared in the database.

Next we'll look at the code used to execute this procedure call. The difference is in the execution and the mapping of attributes. Instead of relying on the SqlActor class to do the mapping, we create a SqlStructValue and pass in the Actor instance in the constructor. The SqlStructValue class will do the mapping between the bean properties of the Actor class and the attributes of the STRUCT. This SqlStructValue is then passed in as the data value in the input map for the execute call.

    public void addActor(final Actor actor) {
        Map in = Collections.singletonMap("in_actor", new SqlStructValue(actor)); 1
        addActorCall.execute(in); 2
    }

1

We create an SqlStructValue that will handle the type creation and mapping and add it to the Map containing the in parameters.

2

We execute the call by passing in the input Map.

If you prefer to use the classic StoredProcedure class then the equivalent configuration would look like this:

    private class AddActorProc extends StoredProcedure {

        public AddActorProc(DataSource dataSource) {
            super(dataSource, "add_actor");
            declareParameter(new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE"));
        }

        public void execute(Actor actor) {
            Map in = Collections.singletonMap("in_actor", new SqlStructValue(actor));
            this.execute(in);
        }

    }

7.1.4 Using SqlReturnStruct to access STRUCT data from an OUT parameter

You can use the SqlReturnStruct class to map between the attributes of a STRUCT object and properties of a JavaBean. This is more convenient than providing this mapping yourself. This example will show how this can be done using an SqlOutParameter combined with the SqlReturnStruct class.

    this.getActorCall =
            new SimpleJdbcCall(dataSource).withProcedureName("get_actor")
                .declareParameters(
                    new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE", 1
                            new SqlReturnStruct(Actor.class)) 2
            );

1

We define the SqlParameter with parameter name, the Oracle type and the type name as it is declared in the database.

2

The SqlReturnStruct will retrieve the STRUCT and access the array of objects representing the attributes and then map them to the properties of the JavaBean instance provided n the constructor.

Next we look at the code that executes this SimpleJdbcCall.

    public Actor getActor(int id) {
        Map in = Collections.singletonMap("in_actor_id", id);
        return getActorCall.executeObject(Actor.class, in); 1
    }

1

We execute the call by passing in a Map containing any in parameters. The executeObject method returns an Actor containing the data returned by the stored procedure call.

If you prefer to use the classic StoredProcedure class then the equivalent configuration would look like this:

    private class GetActorProc extends StoredProcedure {

        public GetActorProc(DataSource dataSource) {
            super(dataSource, "get_actor");
            declareParameter(new SqlParameter("in_actor_id", Types.NUMERIC));
            declareParameter(
                new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE",
                        new SqlReturnStruct(Actor.class))
            );
        }

        public Actor execute(Long id) {
            Map in = Collections.singletonMap("in_actor_id", id);
            Map out = this.execute(in);
            return (Actor) out.get("out_actor");
        }

    }

7.2 Using an ARRAY parameter

Sometimes your stored procedures has parameters that are declared as arrays of some type. These arrays are managed using JDBC Array objects. When working with Oracle it's sometimes easier to work with Oracle's extension to Array which is oracle.sql.ARRAY.

For the ARRAY examples we will use the following type declarations.

CREATE OR REPLACE TYPE actor_name_array 
    AS VARRAY(20) OF VARCHAR2(50);
CREATE OR REPLACE TYPE actor_id_array 
    AS VARRAY(20) OF NUMBER;

We will show how to access parameters using these declarations in two JDBC calls. The first one is a procedure call that deletes actor entries based on ids provided in an actor_id_array. The second example calls a function to retrieve an array of the names for all actors in the table.

7.2.1 Setting ARRAY values using SqlArrayValue for an IN parameter

We are using the SimpleJdbcCall for this example and when we configure this call its important to note that we can't rely on the database metadata. Whenever a collection type is used the metadata reported back from the JDBC driver contains entries bot for the collection type and for the type contained in the collection so it looks like there are additional parameters. Because of this it is best to turn off the metadata processing by calling thewithoutProcedureColumnMetaDataAccess method.

This example calls a procedure that deletes actors based on the ids provided in an array. Here is the source for this procedure:

CREATE OR REPLACE PROCEDURE delete_actors (in_actor_ids IN actor_id_array)
AS
BEGIN
  FOR i IN 1..in_actor_ids.count loop
    DELETE FROM actor WHERE id = in_actor_ids(i);
  END LOOP;
END;

The declaration of the ARRAY parameter follows the same pattern as we used previously for the STRUCT parameters. We are simply providing the OracleTypes.ARRAY SQL type along with the type name as it is specified in the database metadata.

    this.deleteActorsCall =
            new SimpleJdbcCall(dataSource).withProcedureName("delete_actors")
                .withoutProcedureColumnMetaDataAccess()
                .declareParameters(
                    new SqlParameter("in_actor_ids", OracleTypes.ARRAY, "ACTOR_ID_ARRAY")); 1

1

We define the SqlParameter with parameter name, the Oracle type and the type name as it is declared in the database.

Next we look at the code that executes this SimpleJdbcCall. For IN parameters the arrays are managed using an SqlArrayValue implementation that will handle the ArrayDescriptor creation and the mapping of the array to an oracle.sql.ARRAY instance.

    public void deleteActors(final Long[] ids) {
        Map in = Collections.singletonMap("in_actor_ids", new SqlArrayValue(ids)); 1
        deleteActorsCall.execute(in);
    }

1

We declare an SqlArrayValue instance that will handle creating the ArrayDesriptor and the ARRAY to be passed in as the parameter value.

If you prefer to use the classic StoredProcedure class then the equivalent configuration would look like this:

    private class DeleteActorsProc extends StoredProcedure {

        public DeleteActorsProc(DataSource dataSource) {
            super(dataSource, "delete_actors");
            declareParameter(new SqlParameter("in_actor_ids", OracleTypes.ARRAY, "ACTOR_ID_ARRAY"));
        }

        public void execute(Long[] ids) {
            Map in = Collections.singletonMap("in_actor_ids", new SqlArrayValue(ids));
            Map out = this.execute(in);
        }

    }

7.2.2 Using SqlReturnArray to handle the ARRAY from an OUT parameter

Now it is time to handle the OUT parameter scenario. Here it is an SqlOutParameter combined with an SqlReturnArray instance that is responsible for handling the Array.

    this.getActorNamesCall =
            new SimpleJdbcCall(dataSource).withFunctionName("get_actor_names")
                .withoutProcedureColumnMetaDataAccess()
                .declareParameters(new SqlOutParameter("return", Types.ARRAY, "ACTOR_NAME_ARRAY", 1
                        new SqlReturnArray())); 2

1

We declare an SqlOutParameter with parameter name, the Oracle type and the type name as it is declared in the database.

2

The SqlReturnArray accesses the ARRAY parameter using the JDBC calls and creates the String array that is the return value for this example.

Next we look at the code that executes this SimpleJdbcCall.

    public String[] getActorNames() {
        Map in = Collections.emptyMap();
        return getActorNamesCall.executeFunction(String[].class, in); 1
    }

1

Here we just have to call executeFunction passing in the expected output class and an empty map since there are no IN parameters.

If you prefer to use the classic StoredProcedure class then the equivalent configuration would look like this:

    private class GetActorNamesProc extends StoredProcedure {

        public GetActorNamesProc(DataSource dataSource) {
            super(dataSource, "get_actor_names");
            setFunction(true);
            declareParameter(new SqlOutParameter("return", Types.ARRAY, "ACTOR_NAME_ARRAY",
                    new SqlReturnArray()));
        }

        public String[] execute() {
            Map in = Collections.emptyMap();
            Map out = this.execute(in);
            return (String[]) out.get("return");
        }

    }

7.3 Handling a REF CURSOR

The Spring Framework already contains implementations that simplify the handling of REF CURSORS but we include an example here just to complete the coverage of the handling of Oracle specific advanced data types. The procedure we are calling is declared as follows:

CREATE OR REPLACE PROCEDURE read_actors (out_actors_cur OUT sys_refcursor)
AS
BEGIN
  OPEN out_actors_cur FOR 'select * from actor';
END;

7.3.1 Retrieving data using a ParameterizedBeanPropertyRowMapper from a REF CURSOR

First we'll look at a SimpleJdbcCall implementation where we use the returningResultSet method to declare the RowMapper we want to use. We have an Actor class that is a JavaBean and the properties match the column names so we can use the ParameterizedBeanPropertyRowMapper to automatically map data from the ResultSet to the bean properties. Here is the code used to declare this SimpleJdbcCall:

    this.readActorsCall =
            new SimpleJdbcCall(dataSource).withProcedureName("read_actors")
                .returningResultSet("out_actors_cur", 1
                        ParameterizedBeanPropertyRowMapper.newInstance(Actor.class)); 2

1

We declare a returningResultSet with parameter name and the RowMapper we would like to use.

2

The ParameterizedBeanPropertyRowMapper accesses the ResultSetMetaData and maps the row columns to corresponding bean properties in the class specified as parameter to the newInstace method call.

To execute this call we use the following code:

    public List<Actor> getActors() {
        return readActorsCall.executeObject(List.class, Collections.emptyMap()); 1
    }

1

Here we just have to call executeObject passing in the expected output class which is a List and an empty map since there are no IN parameters.

When using the StoredProcedure class we would need to use an SqlOutParameter that accepts a RowMapper. Here is an example of an SqlOutParameter configured with a ParameterizedBeanPropertyRowMapper.

        new SqlOutParameter("out_actors_cur", OracleTypes.CURSOR, 
                ParameterizedBeanPropertyRowMapper.newInstance(Actor.class)) 1

1

Here we specify the parameter name and the SQL type which is OracleTypes.CURSOR and instantiate a ParameterizedBeanPropertyRowMapper to be used to map row data to the Actor class.

If you prefer to use the classic StoredProcedure class then the equivalent configuration would look like this:

    private class ReadActorsProc extends StoredProcedure {

        public ReadActorsProc(DataSource dataSource) {
            super(dataSource, "read_actors");
            declareParameter(
                new SqlOutParameter("out_actors_cur", OracleTypes.CURSOR,
                        ParameterizedBeanPropertyRowMapper.newInstance(Actor.class))
            );

        }

        public List execute() {
            Map in = Collections.emptyMap();
            Map out = this.execute(in);
            return (List) out.get("out_actors_cur");
        }

    }