For the latest stable version, please use Spring Integration 6.3.2! |
Outbound Channel Adapter
The outbound channel adapter is the inverse of the inbound: its role is to handle a message and use it to execute a SQL query. By default, the message payload and headers are available as input parameters to the query, as the following example shows:
<int-jdbc:outbound-channel-adapter
query="insert into items (id, status, name) values (:headers[id], 0, :payload[something])"
data-source="dataSource"
channel="input"/>
In the preceding example, messages arriving at the channel labelled input
have a payload of a map with a key of something
, so the []
operator dereferences that value from the map.
The headers are also accessed as a map.
The parameters in the preceding query are bean property expressions on the incoming message (not SpEL expressions).
This behavior is part of the SqlParameterSource , which is the default source created by the outbound adapter.
You can inject a different SqlParameterSourceFactory to get different behavior.
|
The outbound adapter requires a reference to either a DataSource
or a JdbcTemplate
.
You can also inject a SqlParameterSourceFactory
to control the binding of each incoming message to a query.
If the input channel is a direct channel, the outbound adapter runs its query in the same thread and, therefore, the same transaction (if there is one) as the sender of the message.
Passing Parameters by Using SpEL Expressions
A common requirement for most JDBC channel adapters is to pass parameters as part of SQL queries or stored procedures or functions.
As mentioned earlier, these parameters are by default bean property expressions, not SpEL expressions.
However, if you need to pass SpEL expression as parameters, you must explicitly inject a SqlParameterSourceFactory
.
The following example uses a ExpressionEvaluatingSqlParameterSourceFactory
to achieve that requirement:
<jdbc:outbound-channel-adapter data-source="dataSource" channel="input"
query="insert into MESSAGES (MESSAGE_ID,PAYLOAD,CREATED_DATE) values (:id, :payload, :createdDate)"
sql-parameter-source-factory="spelSource"/>
<bean id="spelSource"
class="o.s.integration.jdbc.ExpressionEvaluatingSqlParameterSourceFactory">
<property name="parameterExpressions">
<map>
<entry key="id" value="headers['id'].toString()"/>
<entry key="createdDate" value="new java.util.Date()"/>
<entry key="payload" value="payload"/>
</map>
</property>
</bean>
For further information, see Defining Parameter Sources.
Using the PreparedStatement
Callback
Sometimes, the flexibility and loose-coupling of SqlParameterSourceFactory
does not do what we need for the target PreparedStatement
or we need to do some low-level JDBC work.
The Spring JDBC module provides APIs to configure the execution environment (such as ConnectionCallback
or PreparedStatementCreator
) and manipulate parameter values (such as SqlParameterSource
).
It can even access APIs for low-level operations, such as StatementCallback
.
Starting with Spring Integration 4.2, MessagePreparedStatementSetter
allows the specification of parameters on the PreparedStatement
manually, in the requestMessage
context.
This class plays exactly the same role as PreparedStatementSetter
in the standard Spring JDBC API.
Actually, it is invoked directly from an inline PreparedStatementSetter
implementation when the JdbcMessageHandler
invokes execute
on the JdbcTemplate
.
This functional interface option is mutually exclusive with sqlParameterSourceFactory
and can be used as a more powerful alternative to populate parameters of the PreparedStatement
from the requestMessage
.
For example, it is useful when we need to store File
data to the DataBase BLOB
column in a streaming manner.
The following example shows how to do so:
@Bean
@ServiceActivator(inputChannel = "storeFileChannel")
public MessageHandler jdbcMessageHandler(DataSource dataSource) {
JdbcMessageHandler jdbcMessageHandler = new JdbcMessageHandler(dataSource,
"INSERT INTO imagedb (image_name, content, description) VALUES (?, ?, ?)");
jdbcMessageHandler.setPreparedStatementSetter((ps, m) -> {
ps.setString(1, m.getHeaders().get(FileHeaders.FILENAME));
try (FileInputStream inputStream = new FileInputStream((File) m.getPayload()); ) {
ps.setBlob(2, inputStream);
}
catch (Exception e) {
throw new MessageHandlingException(m, e);
}
ps.setClob(3, new StringReader(m.getHeaders().get("description", String.class)));
});
return jdbcMessageHandler;
}
From the XML configuration perspective, the prepared-statement-setter
attribute is available on the <int-jdbc:outbound-channel-adapter>
component.
It lets you specify a MessagePreparedStatementSetter
bean reference.
Batch Update
Starting with version 5.1, the JdbcMessageHandler
performs a JdbcOperations.batchUpdate()
if the payload of the request message is an Iterable
instance.
Each element of the Iterable
is wrapped to a Message
with the headers from the request message if such an element is not a Message
already.
In the case of regular SqlParameterSourceFactory
-based configuration these messages are used to build an SqlParameterSource[]
for an argument used in the mentioned JdbcOperations.batchUpdate()
function.
When a MessagePreparedStatementSetter
configuration is applied, a BatchPreparedStatementSetter
variant is used to iterate over those messages for each item and the provided MessagePreparedStatementSetter
is called against them.
The batch update is not supported when keysGenerated
mode is selected.