Spring Integration - JDBC channel adaptar without query result

Question

I have a problem when my query return null or zero result inside ResultSet.

I have a int-jdbc:inbound-channel-adapter with a rowMapper

<int-jdbc:inbound-channel-adapter query="SELECT * FROM ENGINE_LOGS WHERE COUNTRY_ID = 2 AND BUSINESS_ID = 100 and rownum &lt; 10"   
    channel="oracle.db.resultSet.engineLogs"
    update="" 
    row-mapper="engineLogsRowMapper"
    data-source="jdbcTemplate">
    <!-- Cron Time -->
    <int:poller fixed-rate="5" time-unit="SECONDS"></int:poller>
</int-jdbc:inbound-channel-adapter>`

When the query return null o zero (0) result, the row mapper class or the transformer (with input-channel: oracle.db.resultSet.engineLogs) never call.

I need execute an update in a auxiliary database if the query not find records in database, is it possible add a router or something to fix my problem?

Regards

Artem Bilan · Answer 1 · 2015-03-29T14:02:10.967

That's correct. A message with null payload does not make sense for messaging system, like Spring Integration. Actually no one existing messaging protocol support null payload.

Hence it is standard behavior for many result-aware adapter to do nothing, when there is no data from the underlying system, like JDBC in your case.

The code looks like:

private Object poll() {
    List<?> payload = doPoll(this.sqlQueryParameterSource);
    if (payload.size() < 1) {
        payload = null;
    }
    if (payload != null && updateSql != null) {
        if (this.updatePerRow) {
            for (Object row : payload) {
                executeUpdateQuery(row);
            }
        }
        else {
            executeUpdateQuery(payload);
        }
    }
    return payload;
}

As you see an update isn't reachable when ResultSet is empty.

There is a hook for you to achieve your requirements. The <poller> can be configured with <advice-chain>, where and Advice is applied for the Callable<Boolean>.call() method in the AbstractPollingEndpoint. in that custom MethodInterceptor you can check the result of invocation.proceed(), which is true if the underlying MessageSource (JdbcPollingChannelAdapter in our case) returns a data or not.

With the false result you really can do that required UPDATE in that auxiliary database.

We have a open issue, by the way, to support null payloads. I have an idea for the solution to use Java 8's Optional<?>, which can be trasparently converted to target object for nay downstream POJO service-activator, transformer, splitter etc.

Feel free to add any comments there!

UPDATE

How implement this class com.service.ValidateResultAdvice ?

public class ValidateResultAdvice implements MethodInterceptor {

    private JdbcTemplate jdbcTemplate;

    public Object invoke(MethodInvocation invocation) throws Throwable {
         Boolean result = (Boolean) invocation.proceed();
         if (!result) {
               this.jdbcTemplate.update(...);
         }
         return result;
    }

}

Something like that.

Can you provide me some example for How implement this class com.service.ValidateResultAdvice ? — joel.delvalle, Mar 27 '15 at 15:14
Can you please have a look at the example in [my answer](https://stackoverflow.com/a/50129520/3127111) and tell me if that's a proper advice usage? — watery, May 02 '18 at 07:50

score 0 · Answer 2 · answered May 02 '18 at 07:47

I'm in a similar situation, where upon an empty result set I need to stop that same jdbc endpoint, so here's an example using Spring Integration 5.

Spring context configuration:

<int-jdbc:inbound-channel-adapter role="jdbc-endpoint" auto-startup="false"
        data-source="dbDataSource" channel="resultSetsIn"
        query="SELECT ... FROM ... WHERE ..."
        update="UPDATE ... SET ... WHERE id IN (:id)"
        max-rows-per-poll="1">

    <int:poller fixed-rate="5000">
        <int:advice-chain>
            <bean class="services.EmptySourceService" />
        </int:advice-chain>
    </int:poller>

</int-jdbc:inbound-channel-adapter>

Advice implementation:

package services;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.integration.aop.AbstractMessageSourceAdvice;
import org.springframework.integration.core.MessageSource;
import org.springframework.integration.support.SmartLifecycleRoleController;
import org.springframework.messaging.Message;

public class EmptySourceService extends AbstractMessageSourceAdvice {

    @Autowired
    private SmartLifecycleRoleController roleController;

    @Override
    public boolean beforeReceive(MessageSource<?> source) {
        return true;
    }

    @Override
    public Message<?> afterReceive(Message<?> result, MessageSource<?> source) {
        if(result == null) {
            // empty query result logic
            roleController.stopLifecyclesInRole("jdbc-endpoint");
        }

        return result;
    }

}

Endpoint stopping is done as explained in Endpoint roles.

Spring Integration - JDBC channel adaptar without query result

2 Answers2