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"; 
    }

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

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

}

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")); 

Next we look at the code that executes this SimpleJdbcCall.

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

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);
        }

    }

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", 
                            new SqlReturnSqlData(SqlActor.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); 
    }

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");
        }

    }

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")); 

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)); 
        addActorCall.execute(in); 
    }

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);
        }

    }

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", 
                            new SqlReturnStruct(Actor.class)) 
            );

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); 
    }

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");
        }

    }

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")); 

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)); 
        deleteActorsCall.execute(in);
    }

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);
        }

    }

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", 
                         new SqlReturnArray())); 

Next we look at the code that executes this SimpleJdbcCall.

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

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");
        }

    }

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", 
                        ParameterizedBeanPropertyRowMapper.newInstance(Actor.class)); 

To execute this call we use the following code:

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

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)) 

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");
        }

    }