Trouble using jdbc:outbound-gateway when query returns empty result set

Question

I have noticed the following unexpected behavior and can reproduce it with the Spring Integration sample basic/jdbc

Run the Spring Integration sample basic/jdbc and immediately attempt to find a name that does not exist i.e. run using the Spring Integration sample basic/jdbc run Main.java, enter 1 to Find person, enter Mark

A ReplyRequiredException is thrown because no row is found. If I change the int-jdbc:outbound-gateway definition in spring-integration-context.xml to use requiresReply="false", then the call to findPersonByName blocks until the int:gateway times out after the configured 5 seconds.

This is a very typical operation where I want to use Spring Integration jdbc:outbound-gateway to return a list of 0 or more items. It seems to work fine for result sizes 1 or more. However, neither behavior is desirable when 0 results are found. I don't want the exception because sometimes this behavior is expected and I also don't want to block until timeout. In the case of the Exception, I can use try/catch but the framework logs the exception at the WARN level and I don't want to handle expected behavior, 0 items returned, with exception handling, try/catch. Is there a way to configure the flow in Spring Integration sample basic/jdbc to return without blocking or throwing an Exception when the name is not found, i.e. just return an empty list?

Answer 1

A number of users have asked for this and we will look into it.

In the meantime, there are a couple of work-arounds.

1. Add an error-channel to some upstream component (e.g. <gateway/>) to handle the exception.
2. Add an ExpressionEvaluatingRequestHandlerAdvice to the JDBC gateway's request-handler-advice-chain. Set it's returnFailureExpressionResult property to true, which causes the result of the onFailureExpression to be returned to the calling thread instead of throwing the exception.

Answer 2

One other option is to use a service activator with a jdbcTemplate instead. This way, you control the response yourself.

Obviously this is not an ideal approach, but it is arguably cleaner than resorting to the use of error-channels or handler advice for what feels like it should be a normal response scenario. One potential downside (though I haven't explored this in depth yet) is you may lose some additional out-of-the-box instrumentation provided by the framework for the jdbc gateway (say additional JMX properties/method exposure, etc.)