Using JDBC to transform/split a payload

[bernerbits]

Hi,

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" />

or

<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?

[ghillert]

Hi,

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.

Cheers,

Gunnar

[1] http://static.springsource.org/sprin...tbound-gateway
[2] https://jira.springsource.org/browse/INT-2289
[3] https://github.com/SpringSource/spri...ter/basic/jdbc
[4] http://static.springsource.org/sprin...yload-enricher
[5] https://github.com/ghillert/spring-i...basic/enricher

[Cleric]

Hello

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).

Cheers,
Artem Bilan

[bernerbits]

Gunnar,

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;
	}
	
	@Required
	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" />
</int:transformer>

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!