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

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  >
                <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"  >
        <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;
        }

CodePudding user response：

I guess your point is to return an empty list as is, not null as it is there by default in the JdbcOutboundGateway.

The null is valid a result from the Joinpoint execution in the AOP Advice. The logic in that ExpressionEvaluatingRequestHandlerAdvice is like this:

    try {
        Object result = callback.execute();
        if (this.onSuccessExpression != null) {
            evaluateSuccessExpression(message);
        }
        return result;
    }

Since null is OK return, yo just go to the evaluateSuccessExpression() without expecting its result at all. So, in the end we just return that null.

This null is consulted in the AbstractReplyProducingMessageHandler:

    if (result != null) {
        sendOutputs(result, message);
    }
    else if (this.requiresReply && !isAsync()) {
        throw new ReplyRequiredException(message, "No reply produced by handler '"  
                getComponentName()   "', and its 'requiresReply' property is set to true.");
    }

You may really consider to set that requiresReply to false to ignore empty lists from query execution. We may revise our "empty list" logic though, but for now it is converted directly to null: https://jira.spring.io/browse/INT-3333.

You may consider to implement a a custom AbstractRequestHandlerAdvice and check for the callback.execute() result and return an empty list as you might expect.

It is also possible with the mentioned ExpressionEvaluatingRequestHandlerAdvice, but it is a bit involved with other options and exceptions throwing from the onSuccessExpression.

CodePudding user response：

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  />
        </int-jdbc:request-handler-advice-chain>
    </int-jdbc:outbound-gateway>