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 totrue
. Optional. -
data-source
: Reference to ajavax.sql.DataSource
, which is used to access the database. Required. -
id
: Identifies the underlying Spring bean definition, which is an instance of eitherEventDrivenConsumer
orPollingConsumer
, depending on whether the outbound channel adapter’schannel
attribute references aSubscribableChannel
or aPollableChannel
. Optional. -
ignore-column-meta-data
: For fully supported databases, the underlyingSimpleJdbcCall
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 tofalse
. Optional. -
is-function
: Iftrue
, a SQL Function is called. In that case, thestored-procedure-name
orstored-procedure-name-expression
attributes define the name of the called function. It defaults tofalse
. Optional. -
stored-procedure-name
: This attribute specifies the name of the stored procedure. If theis-function
attribute is set totrue
, this attribute specifies the function name instead. Either this property orstored-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 aString
.If the
is-function
attribute is set totrue
, this attribute specifies a stored function. Either this property orstored-procedure-name
must be specified. -
jdbc-call-operations-cache-size
: Defines the maximum number of cachedSimpleJdbcCallOperations
instances. Basically, for each stored procedure name, a newSimpleJdbcCallOperations
instance is created that, in return, is cached.Spring Integration 2.2 added the stored-procedure-name-expression
attribute and thejdbc-call-operations-cache-size
attribute.The default cache size is
10
. A value of0
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 aSqlParameterSourceFactory
. By default, bean properties of the passed inMessage
payload are used as a source for the stored procedure’s input parameters by using aBeanPropertySqlParameterSourceFactory
.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 totrue
, the payload of theMessage
is used as a source for providing parameters. If set tofalse
, however, the entireMessage
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 defaultBeanPropertySqlParameterSourceFactory
, 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 entireMessage
. The property set on the underlyingStoredProcExecutor
. 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 thevalue
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 theexpression
, thevalue
attribute is not allowed. Optional. Optional. -
returning-resultset
: Stored procedures may return multiple result sets. By setting one or morereturning-resultset
elements, you can specifyRowMappers
to convert each returnedResultSet
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 thesql-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.
|
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>