springjdbcspring-integrationdeclarative

spring-integration jdbc outbound-gateway advice for handling empty result-sets


I'm taking @gary-russel's suggestion and opening a new question w.r.t this older question ( Trouble using jdbc:outbound-gateway when query returns empty result set ) about spring-integration JDBC outbound-gateway calls on requests that return an empty result-set.

I've tried to use handler advice to get the request to return an empty array rather than throwing an exception.

Could you advise why this advice is not right?

<beans:beans xmlns:xsi      = "http://www.w3.org/2001/XMLSchema-instance"

             xmlns:beans    = "http://www.springframework.org/schema/beans"
             xmlns:jdbc     = "http://www.springframework.org/schema/jdbc"

             xmlns:int      = "http://www.springframework.org/schema/integration"
             xmlns:int-jdbc = "http://www.springframework.org/schema/integration/jdbc"

             xsi:schemaLocation="http://www.springframework.org/schema/beans            https://www.springframework.org/schema/beans/spring-beans.xsd
                                 http://www.springframework.org/schema/jdbc             https://www.springframework.org/schema/jdbc/spring-jdbc.xsd
                                 http://www.springframework.org/schema/integration      https://www.springframework.org/schema/integration/spring-integration.xsd
                                 http://www.springframework.org/schema/integration/jdbc https://www.springframework.org/schema/integration/jdbc/spring-integration-jdbc.xsd">

    <int:gateway id="getAllCustomers-Gateway"
                 default-request-channel="getAllCustomers"
                 service-interface="demo.StringInputJsonOutputGatewayMethod" />

    <int:channel id="getAllCustomers" />

    <int-jdbc:outbound-gateway id="getAllCustomers-OutboundGateway"
                               request-channel="getAllCustomers"
                               query="select * from Customer"
                               data-source="dataSource"
                               max-rows="0" >
        <int-jdbc:request-handler-advice-chain>
            <beans:bean class="org.springframework.integration.handler.advice.ExpressionEvaluatingRequestHandlerAdvice" >
                <beans:property name="onSuccessExpressionString"     value="payload ?: {} " />
                <beans:property name="returnFailureExpressionResult" value="#{true}"        />
                <beans:property name="onFailureExpressionString"     value="{}"             />
            </beans:bean>
        </int-jdbc:request-handler-advice-chain>
    </int-jdbc:outbound-gateway>

    <beans:bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource" >
        <beans:property name="driverClass" value="org.h2.Driver" />
        <beans:property name="url"         value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE" />
        <beans:property name="username"    value="sa" />
        <beans:property name="password"    value=""   />
    </beans:bean>

    <jdbc:initialize-database data-source="dataSource" >
        <jdbc:script location="classpath:/schema.sql" />
    </jdbc:initialize-database>
</beans:beans>

The test database is initialized with this script (schema.sql:

CREATE TABLE Customer (
    ID         BIGINT      NOT NULL AUTO_INCREMENT,
    FIRST_NAME VARCHAR(30) NOT NULL,
    LAST_NAME  VARCHAR(30) NOT NULL,

    PRIMARY KEY (ID)
);

The outbound-gateway is throwing an exception:

org.springframework.integration.handler.ReplyRequiredException: No reply produced by handler 'getAllCustomers-OutboundGateway', and its 'requiresReply' property is set to true.

Any suggestions or pointers appreciated.

Some debugging followup:

I can see that the ExpressionEvaluatingRequestHandlerAdvice is called to evaluate the successExpression but that this does not alter return result, even when a success expression is provided, something like payload ?: {}. The failureExpression is not consulted because the null result failure exception from the JdbcOutboundGateway is thrown after AdviceChain is run.

The most surprising part of this JdbcOutboundGateway problem is that the method handleRequestMessage() does receive an empty list from the JDBC call, which seems perfectly valid, but it then goes on to explicitly set this to null.

        if (this.poller != null) {
            ...
            list = this.poller.doPoll(sqlQueryParameterSource);
        }
        Object payload = list;
        if (list.isEmpty()) {
            return null;
        }

Solution

  • Thanks to @artem-bilan's suggestions. This seems to do the trick in the end.

    Add a custom advice handler extension to ExpressionEvaluatingRequestHandlerAdvice:

    package demo;
    
    import org.springframework.integration.handler.advice.ExpressionEvaluatingRequestHandlerAdvice;
    import org.springframework.messaging.Message;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import static java.util.Collections.unmodifiableList;
    
    public class ReplaceNullWithEmptyListHandlerAdvice extends ExpressionEvaluatingRequestHandlerAdvice {
    
        private static final List<Object> EMPTY_LIST = unmodifiableList(new ArrayList<>());
    
        @Override
        protected Object doInvoke(ExecutionCallback callback, Object target, Message<?> message) {
            final Object result = super.doInvoke(callback, target, message);
            return result != null ? result : EMPTY_LIST;
        }
    }
    

    Now can set the advicechain like this:

        <int-jdbc:outbound-gateway id="getAllCustomers-OutboundGateway"
                                   request-channel="getAllCustomers"
                                   query="select * from Customer"
                                   data-source="dataSource"
                                   max-rows="0" >
            <int-jdbc:request-handler-advice-chain>
                <beans:bean class="demo.ReplaceNullWithEmptyListHandlerAdvice" />
            </int-jdbc:request-handler-advice-chain>
        </int-jdbc:outbound-gateway>