Stored Procedures

In certain situations, plain JDBC support is not sufficient. Maybe you deal with legacy relational database schemas or you have complex data processing needs, but, ultimately, you have to use stored procedures or stored functions. Since Spring Integration 2.1, we provide three components to execute stored procedures or stored functions:

Stored Procedures Inbound Channel Adapter
Stored Procedures Outbound Channel Adapter
Stored Procedures Outbound Gateway

Supported Databases

In order to enable calls to stored procedures and stored functions, the stored procedure components use the org.springframework.jdbc.core.simple.SimpleJdbcCall class. Consequently, the following databases are fully supported for executing stored procedures:

Apache Derby
DB2
MySQL
Microsoft SQL Server
Oracle
PostgreSQL
Sybase

If you want to execute stored functions instead, the following databases are fully supported:

MySQL
Microsoft SQL Server
Oracle
PostgreSQL

Even though your particular database may not be fully supported, chances are that you can use the stored procedure Spring Integration components quite successfully anyway, provided your RDBMS supports stored procedures or stored functions.

As a matter of fact, some provided integration tests use the H2 database. Nevertheless, it is very important to thoroughly test those usage scenarios.

Configuration

The stored procedure components provide full XML Namespace support, and configuring the components is similar as for the general purpose JDBC components discussed earlier.

Common Configuration Attributes

All stored procedure components share certain configuration parameters:

auto-startup: Lifecycle attribute signaling whether this component should be started during application context startup. It defaults to true. Optional.
data-source: Reference to a javax.sql.DataSource, which is used to access the database. Required.
id: Identifies the underlying Spring bean definition, which is an instance of either EventDrivenConsumer or PollingConsumer, depending on whether the outbound channel adapter’s channel attribute references a SubscribableChannel or a PollableChannel. Optional.
ignore-column-meta-data: For fully supported databases, the underlying SimpleJdbcCall class can automatically retrieve the parameter information for the stored procedure or stored function from the JDBC metadata.

However, if the database does not support metadata lookups or if you need to provide customized parameter definitions, this flag can be set to true. It defaults to false. Optional.
is-function: If true, a SQL Function is called. In that case, the stored-procedure-name or stored-procedure-name-expression attributes define the name of the called function. It defaults to false. Optional.
stored-procedure-name: This attribute specifies the name of the stored procedure. If the is-function attribute is set to true, this attribute specifies the function name instead. Either this property or stored-procedure-name-expression must be specified.
stored-procedure-name-expression: This attribute specifies the name of the stored procedure by using a SpEL expression. By using SpEL, you have access to the full message (if available), including its headers and payload. You can use this attribute to invoke different stored procedures at runtime. For example, you can provide stored procedure names that you would like to execute as a message header. The expression must resolve to a String.

If the is-function attribute is set to true, this attribute specifies a stored function. Either this property or stored-procedure-name must be specified.
jdbc-call-operations-cache-size: Defines the maximum number of cached SimpleJdbcCallOperations instances. Basically, for each stored procedure name, a new SimpleJdbcCallOperations instance is created that, in return, is cached.

Spring Integration 2.2 added the stored-procedure-name-expression attribute and the jdbc-call-operations-cache-size attribute.

The default cache size is 10. A value of 0 disables caching. Negative values are not permitted.

If you enable JMX, statistical information about the jdbc-call-operations-cache is exposed as an MBean. See MBean Exporter for more information.
sql-parameter-source-factory: (Not available for the stored procedure inbound channel adapter.) Reference to a SqlParameterSourceFactory. By default, bean properties of the passed in Message payload are used as a source for the stored procedure’s input parameters by using a BeanPropertySqlParameterSourceFactory.

This may suffice for basic use cases. For more sophisticated options, consider passing in one or more ProcedureParameter values. See Defining Parameter Sources. Optional.
use-payload-as-parameter-source: (Not available for the stored procedure inbound channel adapter.) If set to true, the payload of the Message is used as a source for providing parameters. If set to false, however, the entire Message is available as a source for parameters.

If no procedure parameters are passed in, this property defaults to true. This means that, by using a default BeanPropertySqlParameterSourceFactory, the bean properties of the payload are used as a source for parameter values for the stored procedure or stored function.

However, if procedure parameters are passed in, this property (by default) evaluates to false. ProcedureParameter lets SpEL Expressions be provided. Therefore, it is highly beneficial to have access to the entire Message. The property set on the underlying StoredProcExecutor. Optional.

Common Configuration Sub-Elements

The stored procedure components share a common set of child elements that you can use to define and pass parameters to stored procedures or stored functions. The following elements are available:

parameter
returning-resultset
sql-parameter-definition
poller

parameter: Provides a mechanism to provide stored procedure parameters. Parameters can be either static or provided by using a SpEL Expressions.

<int-jdbc:parameter name=""         (1)
                    type=""         (2)
                    value=""/>      (3)

<int-jdbc:parameter name=""
                    expression=""/> (4)

1	The name of the parameter to be passed into the Stored Procedure or Stored Function. Required.
2	This attribute specifies the type of the value. If nothing is provided, this attribute defaults to `java.lang.String`. This attribute is used only when the `value` attribute is used. Optional.
3	The value of the parameter. You must provide either this attribute or the `expression` attribute. Optional.
4	Instead of the `value` attribute, you can specify a SpEL expression for passing the value of the parameter. If you specify the `expression`, the `value` attribute is not allowed. Optional. Optional.

returning-resultset: Stored procedures may return multiple result sets. By setting one or more returning-resultset elements, you can specify RowMappers to convert each returned ResultSet to meaningful objects. Optional.
```
<int-jdbc:returning-resultset name="" row-mapper="" />
```

sql-parameter-definition: If you use a database that is fully supported, you typically do not have to specify the stored procedure parameter definitions. Instead, those parameters can be automatically derived from the JDBC metadata. However, if you use databases that are not fully supported, you must set those parameters explicitly by using the sql-parameter-definition element.

You can also choose to turn off any processing of parameter metadata information obtained through JDBC by using the ignore-column-meta-data attribute.

<int-jdbc:sql-parameter-definition
                                   name=""                           (1)
                                   direction="IN"                    (2)
                                   type="STRING"                     (3)
                                   scale="5"                         (4)
                                   type-name="FOO_STRUCT"            (5)
                                   return-type="fooSqlReturnType"/>  (6)

1	Specifies the name of the SQL parameter. Required.
2	Specifies the direction of the SQL parameter definition. Defaults to `IN`. Valid values are: `IN`, `OUT`, and `INOUT`. If your procedure is returning result sets, use the `returning-resultset` element. Optional.
3	The SQL type used for this SQL parameter definition. Translates into an integer value, as defined by `java.sql.Types`. Alternatively, you can provide the integer value as well. If this attribute is not explicitly set, it defaults to 'VARCHAR'. Optional.
4	The scale of the SQL parameter. Only used for numeric and decimal parameters. Optional.
5	The `typeName` for types that are user-named, such as: `STRUCT`, `DISTINCT`, `JAVA_OBJECT`, and named array types. This attribute is mutually exclusive with the `scale` attribute. Optional.
6	The reference to a custom value handler for complex types. An implementation of `SqlReturnType`. This attribute is mutually exclusive with the `scale` attribute and is only applicable for OUT and INOUT parameters. Optional. `poller`: Lets you configure a message poller if this endpoint is a `PollingConsumer`. Optional.

Defining Parameter Sources

Parameter sources govern the techniques of retrieving and mapping the Spring Integration message properties to the relevant stored procedure input parameters.

The stored procedure components follow certain rules. By default, the bean properties of the Message payload are used as a source for the stored procedure’s input parameters. In that case, a BeanPropertySqlParameterSourceFactory is used. This may suffice for basic use cases. The next example illustrates that default behavior.

For the “automatic” lookup of bean properties by using the BeanPropertySqlParameterSourceFactory to work, your bean properties must be defined in lower case. This is due to the fact that in org.springframework.jdbc.core.metadata.CallMetaDataContext (the Java method is matchInParameterValuesWithCallParameters()), the retrieved stored procedure parameter declarations are converted to lower case. As a result, if you have camel-case bean properties (such as lastName), the lookup fails. In that case, provide an explicit ProcedureParameter.

Suppose we have a payload that consists of a simple bean with the following three properties: id, name, and description. Furthermore, we have a simplistic Stored Procedure called INSERT_COFFEE that accepts three input parameters: id, name, and description. We also use a fully supported database. In that case, the following configuration for a stored procedure outbound adapter suffices:

<int-jdbc:stored-proc-outbound-channel-adapter data-source="dataSource"
    channel="insertCoffeeProcedureRequestChannel"
    stored-procedure-name="INSERT_COFFEE"/>

For more sophisticated options, consider passing in one or more ProcedureParameter values.

If you do provide ProcedureParameter values explicitly, by default, an ExpressionEvaluatingSqlParameterSourceFactory is used for parameter processing, to enable the full power of SpEL expressions.

If you need even more control over how parameters are retrieved, consider passing in a custom implementation of SqlParameterSourceFactory by using the sql-parameter-source-factory attribute.

Stored Procedure Inbound Channel Adapter

The following listing calls out the attributes that matter for a stored procedure inbound channel adapter:

<int-jdbc:stored-proc-inbound-channel-adapter
                                   channel=""                                    (1)
                                   stored-procedure-name=""
                                   data-source=""
                                   auto-startup="true"
                                   id=""
                                   ignore-column-meta-data="false"
                                   is-function="false"
                                   skip-undeclared-results=""                    (2)
                                   return-value-required="false"                 (3)
    <int:poller/>
    <int-jdbc:sql-parameter-definition name="" direction="IN"
                                               type="STRING"
                                               scale=""/>
    <int-jdbc:parameter name="" type="" value=""/>
    <int-jdbc:parameter name="" expression=""/>
    <int-jdbc:returning-resultset name="" row-mapper="" />
</int-jdbc:stored-proc-inbound-channel-adapter>

1	Channel to which polled messages are sent. If the stored procedure or function does not return any data, the payload of the `Message` is null. Required.
2	If this attribute is set to `true`, all results from a stored procedure call that do not have a corresponding `SqlOutParameter` declaration are bypassed. For example, stored procedures can return an update count value, even though your stored procedure declared only a single result parameter. The exact behavior depends on the database implementation. The value is set on the underlying `JdbcTemplate`. The value defaults to `true`. Optional.
3	Indicates whether this procedure’s return value should be included. Since Spring Integration 3.0. Optional.

Stored Procedure Outbound Channel Adapter

The following listing calls out the attributes that matter for a stored procedure outbound channel adapter:

<int-jdbc:stored-proc-outbound-channel-adapter channel=""                        (1)
                                               stored-procedure-name=""
                                               data-source=""
                                               auto-startup="true"
                                               id=""
                                               ignore-column-meta-data="false"
                                               order=""                          (2)
                                               sql-parameter-source-factory=""
                                               use-payload-as-parameter-source="">
    <int:poller fixed-rate=""/>
    <int-jdbc:sql-parameter-definition name=""/>
    <int-jdbc:parameter name=""/>

</int-jdbc:stored-proc-outbound-channel-adapter>

1	The receiving message channel of this endpoint. Required.
2	Specifies the order for invocation when this endpoint is connected as a subscriber to a channel. This is particularly relevant when that channel is using a `failover` dispatching strategy. It has no effect when this endpoint is itself a polling consumer for a channel with a queue. Optional.

Stored Procedure Outbound Gateway

The following listing calls out the attributes that matter for a stored procedure outbound channel adapter:

<int-jdbc:stored-proc-outbound-gateway request-channel=""                        (1)
                                       stored-procedure-name=""
                                       data-source=""
                                   auto-startup="true"
                                   id=""
                                   ignore-column-meta-data="false"
                                   is-function="false"
                                   order=""
                                   reply-channel=""                              (2)
                                   reply-timeout=""                              (3)
                                   return-value-required="false"                 (4)
                                   skip-undeclared-results=""                    (5)
                                   sql-parameter-source-factory=""
                                   use-payload-as-parameter-source="">
<int-jdbc:sql-parameter-definition name="" direction="IN"
                                   type=""
                                   scale="10"/>
<int-jdbc:sql-parameter-definition name=""/>
<int-jdbc:parameter name="" type="" value=""/>
<int-jdbc:parameter name="" expression=""/>
<int-jdbc:returning-resultset name="" row-mapper="" />

1	The receiving message channel of this endpoint. Required.
2	Message channel to which replies should be sent after receiving the database response. Optional.
3	Lets you specify how long this gateway waits for the reply message to be sent successfully before throwing an exception. Keep in mind that, when sending to a `DirectChannel`, the invocation occurs in the sender’s thread. Consequently, the failing of the send operation may be caused by other components further downstream. The value is specified in milliseconds. Optional.
4	Indicates whether this procedure’s return value should be included. Optional.
5	If the `skip-undeclared-results` attribute is set to `true`, all results from a stored procedure call that do not have a corresponding `SqlOutParameter` declaration are bypassed. For example, stored procedures may return an update count value, even though your stored procedure only declared a single result parameter. The exact behavior depends on the database. The value is set on the underlying `JdbcTemplate`. The value defaults to `true`. Optional.

Examples

This section contains two examples that call Apache Derby stored procedures. The first procedure calls a stored procedure that returns a ResultSet. By using a RowMapper, the data is converted into a domain object, which then becomes the Spring Integration message payload.

In the second sample, we call a stored procedure that uses output parameters to return data instead.

Have a look at the Spring Integration Samples project.

The project contains the Apache Derby example referenced here, as well as instructions on how to run it. The Spring Integration Samples project also provides an example of using Oracle stored procedures.

In the first example, we call a stored procedure named FIND_ALL_COFFEE_BEVERAGES that does not define any input parameters but that returns a ResultSet.

In Apache Derby, stored procedures are implemented in Java. The following listing shows the method signature:

public static void findAllCoffeeBeverages(ResultSet[] coffeeBeverages)
            throws SQLException {
    ...
}

The following listing shows the corresponding SQL:

CREATE PROCEDURE FIND_ALL_COFFEE_BEVERAGES() \
PARAMETER STYLE JAVA LANGUAGE JAVA MODIFIES SQL DATA DYNAMIC RESULT SETS 1 \
EXTERNAL NAME 'o.s.i.jdbc.storedproc.derby.DerbyStoredProcedures.findAllCoffeeBeverages';

In Spring Integration, you can now call this stored procedure by using, for example, a stored-proc-outbound-gateway, as the following example shows:

<int-jdbc:stored-proc-outbound-gateway id="outbound-gateway-storedproc-find-all"
                                       data-source="dataSource"
                                       request-channel="findAllProcedureRequestChannel"
                                       expect-single-result="true"
                                       stored-procedure-name="FIND_ALL_COFFEE_BEVERAGES">
<int-jdbc:returning-resultset name="coffeeBeverages"
    row-mapper="org.springframework.integration.support.CoffeBeverageMapper"/>
</int-jdbc:stored-proc-outbound-gateway>

In the second example, we call a stored procedure named FIND_COFFEE that has one input parameter. Instead of returning a ResultSet, it uses an output parameter. The following example shows the method signature:

public static void findCoffee(int coffeeId, String[] coffeeDescription)
            throws SQLException {
    ...
}

The following listing shows the corresponding SQL:

CREATE PROCEDURE FIND_COFFEE(IN ID INTEGER, OUT COFFEE_DESCRIPTION VARCHAR(200)) \
PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME \
'org.springframework.integration.jdbc.storedproc.derby.DerbyStoredProcedures.findCoffee';

In Spring Integration, you can now call this Stored Procedure by using, for example, a stored-proc-outbound-gateway, as the following example shows:

<int-jdbc:stored-proc-outbound-gateway id="outbound-gateway-storedproc-find-coffee"
                                       data-source="dataSource"
                                       request-channel="findCoffeeProcedureRequestChannel"
                                       skip-undeclared-results="true"
                                       stored-procedure-name="FIND_COFFEE"
                                       expect-single-result="true">
    <int-jdbc:parameter name="ID" expression="payload" />
</int-jdbc:stored-proc-outbound-gateway>