The main function of an inbound Channel Adapter is to execute a SQL SELECT query and turn the result set into a message. The message payload is the whole result set, expressed as a List, and the types of the items in the list depends on the row-mapping strategy that is used. The default strategy is a generic mapper that just returns a Map for each row i nthe query. Optionally this can be changed by adding a reference to requires a reference to a RowMapper instance (see the Spring JDBC documentation for more detailed information about row mapping).

The inbound adapter also requires a reference to either JdbcTemplate instance or DataSource.

As well as the SELECT statement to generate the messages, the adapter above also has an UPDATE statement that is being used to mark the records as processed, so they don't show up in the next poll. The update can be parameterised by the list of ids from the original select. This is done through a naming convention by default (a column in the input result set called "id" is translated into a list in the parameter map for the update called "id"). The following example defines an inbound Channel Adapter with an update query and a DataSource reference.

<jdbc:inbound-channel-adapter query="select * from item where status=2"
  channel="target" data-source="dataSource"
  update="update item set status=10 where id in (:id)" />

To change the parameter generation strategy you can inject a SqlParameterSourceFactory into the adapter to override the default behaviour (the adapter has a sql-parameter-source-factory attribute).

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. The message payload and headers are available by default as input parameters to the query, for instance:

<jdbc:outbound-channel-adapter
  query="insert into foos (id, status, name) values (:headers[$id], 0, :payload[foo])"
  channel="input" data-source="dataSource"/>

In the example above, messages arriving on the channel "input" have a payload of a map with key "foo", so the [] operator dereferences that value from the map. The headers are also accessed as a map.

The outbound adapter requires a reference to either a DataSource or a JdbcTemplate. It can also have a SqlParameterSourceFactory injected to control the binding of incoming message to the query.

If the input channel is a direct channel then the outbound adapter runs its query in the same thread, and therefor ethe same transaction (if there is one) as the sender of the message.

The outbound Gateway is like a combination of the outbound and inbound adapters: its role is to handle a message and use it to execute a SQL query and then respond with the result sending it to a reply channel. The message payload and headers are available by default as input parameters to the query, for instance:

<jdbc:outbound-gateway
  update="insert into foos (id, status, name) values (:headers[$id], 0, :payload[foo])"
  request-channel="input" reply-channel="output" data-source="dataSource" />

The result of the above would be to insert a record into the "foos" table and return a message to the output channel indicating the number of rows affected (the payload is a map {UPDATED=1}.

If the update query is an insert with auto-generated keys, the reply message can be populated with the generated keys by adding keys-generated="true" to the above example (this is not the default because it is not supported by some database platforms). For example:

<jdbc:outbound-gateway
  update="insert into foos (status, name) values (0, :payload[foo])"
  request-channel="input" reply-channel="output" data-source="dataSource"
  keys-generated="true"/>

Instead of the update count or the generated keys, you can also provide a select query to execute and generate a reply message that way (like the inbound adapter), e.g:

<jdbc:outbound-gateway
  update="insert into foos (id, status, name) values (:headers[$id], 0, :payload[foo])"
  query="select * from foos where id=:headers[$id]"
  request-channel="input" reply-channel="output" data-source="dataSource" />

Like with the adapters there is also the option to provide SqlParameterSourceFactory instances for request and reply. The default is the same as for the outbound adapter, so the request message is available as the root of an expression. If keys-generated="true" then the root of the expression is the generated keys (a map if there is only one or a list of maps if multi-valued).

The outbound gateway requires a reference to either a DataSource or a JdbcTemplate. It can also have a SqlParameterSourceFactory injected to control the binding of incoming message to the query.

The JDBC module provides an implementation of the Spring Integration MessageStore (important in the Claim Check pattern) and MessageGroupStore (important in stateful patterns like Aggregator) backed by a database. Both interfaces are implemented by the JdbcMessageStore and there is also support for configuring store instances in XML. For example:

<jdbc:message-store id="messageStore" data-source="dataSource"/>

A JdbcTemplate can be specified instead of a DataSource.

Other optional attributes are show in the next example:

<jdbc:message-store id="messageStore" data-source="dataSource"
  lob-handler="lobHandler" table-prefix="MY_INT_"/>

Here we have specified a LobHandler for dealing with messages as large objects (e.g. often necessary if using Oracle) and a prefix for the table names in the queries generated by the store. The table name prefix defaults to "INT_".