JDBC stored procedure outbound gateway
Sends messages to a database and/or receives messages using a stored prodecure.
Outbound Channel Gateway are used for updating a database using a stored procedure. The response of the stored procedure is used to populate the message on the reply channel.
Results of the gateway can be transformed to xml using the JDBC result transformer.
Stored procedure name
Specifies the name of the stored procedure. If the stored procedure is a function, this attribute specifies the function name.
Is a function
If true
, a SQL function is called. In that case the stored procedure name attribute defines the name of the called function.
Defaults to false
.
e.g. Functions in PostgreSQL require this attribute to be set to true
.
SQL data source
Reference to a JDBC data source, usually including some form of connection pooling, used for accessing the database.
Required
Specify the stored procedure or function call input parameters here.
Stored procedure name expression
The name of the stored procedure provided as a SpEL expression.
Components that use a message as source of parameters have full access to the message and its headers, in order to derive a stored procedure name.
Optional ; mutually exclusive with stored procedure name.
Ignore column meta data
Fully supported databases, can automatically retrieve the parameter definition information for the to be invoked Stored Procedure or Function from the JDBC Meta-data.
However, if the used database does not support meta data lookups or if you like to provide customized parameter definitions, this flag can be set to true
.
It defaults to false
.
Fully Supported Databases (Stored Procedures):
- Apache Derby
- DB2
- MySQL
- Microsoft SQL Server
- Oracle
- PostgreSQL
- Sybase
Fully Supported Databases (Functions):
- MySQL
- Microsoft SQL Server
- Oracle
- PostgreSQL
Skip undeclared results
If this attribute is set to true
, then all results from a stored procedure call that don't have a corresponding SQL parameter definition will be bypassed.
E.g. 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 used database.
The value is set on the underlying JdbcTemplate
.
Only few developers will probably ever like to process update counts, thus the value defaults to true
.
Expect single result
This attribute indicates that only one result object shall be returned from the stored procedure/function call. If set to true
and the result map from the stored procedure/function call contains only 1 element, then that 1 element is extracted and returned as payload.
If the result map contains more than 1 element and expect single result is true
, then a MessagingException
is thrown.
Otherwise the complete result map is returned as the payload.
Important Note: Several databases such as H2 are not fully supported for stored procedure and/or function calls.
The H2 database, for example, does not fully support the CallableStatement
semantics and when executing function calls against H2, a result list is returned, rather than a single value.
Return value required
Indicates the procedure's return value should be included in the results returned.
Default is false
.
Requires reply
Specify whether this outbound gateway must return a non-null
value, i.e. whether every request message must result in a reply message.
If true
, a ReplyRequiredException
will be thrown when the underlying service returns a null
value.
Default is false
.
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 being cached.
The default cache size is 10
. A value of 0
disables caching.
Reply timeout
Allows you to specify how long (in milliseconds) this gateway will wait for the reply message to be sent successfully to the reply channel before throwing an exception. This attribute only applies when the channel might block, for example when using a bounded queue channel that is currently full.
Also, keep in mind that when sending to a direct channel, the invocation will occur in the sender's thread. Therefore, the failing of the send operation may be caused by other components further downstream.
If not specified, by default the gateway will wait indefinitely.
SQL parameter definitions define the type and value of each parameter.
If you are using a database that is fully supported, you typically don't have to specify the stored procedure parameter definitions.
Instead, those parameters can be automatically derived from the JDBC meta-data. However, if you are using databases that are not fully supported or if you like to provide customized parameter definitions, you can set those parameters explicitly. See also the ignore column meta-data attribute.
Note that the order, name, type, direction and scale of the SQL parameters should exactly match the settings specified on the database.
Request channel
Channel to consume the request messages from.
Required
Reply channel
Channel where reply messages should be sent to.
You can select the nullChannel
here to silently drop the reply messages.
Required
Advice can be added to change the behaviour of this endpoint, for example to add retry logic in case of failures. The following types of advice are available:
Retry advice: allows configuration of sophisticated retry scenarios; this includes specifying policies for retry attemps, backoff periods between attempts and the recovery strategy when retries are exhausted Circuit breaker: if a certain number of consecutive attempts fails, new requests will fail fast and no attempt will be made to invoke the request handler again until some time has expired Expression evaluating advice: general advice that evaluates a configurable SpEL expression on successful and/or failed attempts, and optionally can send a message to a success channel and/or failure channel
By adding multiple advices to this endpoint you can create even more complex combined behaviour. For example, if you add a circuit breaker and a retry advice, you can create a scenario where the circuit breaker only opens when all retries are exhaused. Note that the order of the advice types is important, as switching the order will change the combined behaviour: the first item in the list will be the top of the advice chain, meaning it will be the last advice that is evaluated. Also note that if any advice "traps" exceptions, all advices higher up in the chain won't know about any failures.
Id
Name that uniquely identifies this flow component.
Required