Announcement Announcement Module
No announcement yet.
Using JDBC to transform/split a payload Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using JDBC to transform/split a payload


    I have a frequent need for the ability to take the current payload, run a SELECT query against some or all of its properties, convert the result set into a transformed payload, and pass it to the next channel or step in the chain.

    For example, when taking in a request from an HTTP endpoint, I may only have an ID column for a row, and I want to transform that ID into the full object. Alternatively, I may have the whole object, but I may want to split that payload into related entities from a one-to-many relationship.

    I would love to be able to do something like this:

    <int-jdbc:transformer input-channel="userIds" output-channel="users" row-mapper="userMapper" 
      query="select * from users u where u.user_id = :payload" />

    <int-jdbc:splitter input-channel="users" output-channel="transactions" row-mapper="userTransactionMapper" 
      query="select * from transaction t where t.user_id = :payload.userId" />
    Unfortunately, it seems that the int-jdbc namespace doesn't support this. Not having this seems like a real deficiency because I run into the need for this quite often.

    Is there a reason this type of behavior isn't supported? Perhaps I am going about this the wrong way. If that's true, is there a better way to accomplish what I'm trying to do?

  • #2

    Thanks for your question! There are certainly several avenues available for your requirement. Please take a look at the JDBC Outbound Gateway[1]. It allows you to call the database (using a select) and continue your Spring Integration flow with the result. We still have an open Jira [2] as the Gateway currently does not provide the best user experience. Right now the JDBC Outbound Gateway requires you to make an SQL Update first before you can make a select. We hope to have that issue resolved soon. Thus, in order to work around that you have to make a dummy update e.g. "UPDATE DUMMY SET DUMMY_VALUE='test'". Please see the JDBC Sample [3] for more details.

    Depending on your specific requirements you may also look at the Payload Enricher. For example, you have a Message payload consisting of an order summary but you still need to load the order details (augment the existing payload without replacing it). I that case you can use a Payload enricher[4] to make a separate request (e.g. using the JDBC Outbound Gateway). We also have an example to illustrate its usage [5].

    I hope this helps. Please let us know if you encounter further issues.





    • #3

      Gunnar, and what do you think about <jdbc:splitter>?

      We could use here an experience of Spring Batch: org.springframework.batch.item.database.JdbcCursor ItemReader
      and call AbstractReplyProducingMessageHandler#produceReply on every rowMapper.mapRow(rs, currentRow).

      Artem Bilan


      • #4

        Thanks for the quick response. JDBC Outbound Gateway is exactly what I was using before, but I didn't like the idea of having to use a no-op update. Instead, what I ended up doing was writing a quick handler class like this:

        public class ExpressionEvaluatingDAO extends NamedParameterJdbcDaoSupport {
        	private RowMapper<?> rowMapper = new ColumnMapRowMapper();
        	private String expression;
        	public void setRowMapper(RowMapper<?> rowMapper) {
        		this.rowMapper = rowMapper;
        	public void setExpression(String expression) {
        		this.expression = expression;
        	public Object handleMessage(Message<?> message) {
        		SqlParameterSource src = new ExpressionEvaluatingSqlParameterSourceFactory().createParameterSource(message);
        		return getNamedParameterJdbcTemplate().query(expression,src,rowMapper);
        That allows me to, with only slightly more XML, do the same as above, only without namespace support:

        <int:transformer input-channel="userIds" output-channel="users">
            <bean class="com.mycompany.ExpressionEvaluatingDAO" p:rowMapper-ref="userMapper" 
                p:expression="select * from users u where u.user_id = :payload" p:jdbcTemplate-ref="jdbcTemplate" />
        I will subscribe to the JIRA tickets so I know when the namespace support is made available. Meanwhile this works well for my needs. Thanks!
        Last edited by bernerbits; Apr 10th, 2012, 10:46 AM.