How do you handle EmptyResultDataAccessException with Spring Integration?

Question

I have a situation where before I process an input file I want to check if certain information is setup in the database. In this particular case it is a client's name and parameters used for processing. If this information is not setup, the file import shall fail.

In many StackOverflow pages, the users resolve handling EmptyResultDataAccessException exceptions generated by queryForObject returning no rows by catching them in the Java code.

The issue is that Spring Integration is catching the exception well before my code is catching it and in theory, I would not be able to tell this error from any number of EmptyResultDataAccessException exceptions which may be thrown with other queries in the code.

Example code segment showing try...catch with queryForObject:

    MapSqlParameterSource mapParameters = new MapSqlParameterSource();

    // Step 1 check if client exists at all
    mapParameters.addValue("clientname", clientName);
    try {
        clientID = this.namedParameterJdbcTemplate.queryForObject(FIND_BY_NAME, mapParameters, Long.class);
    } catch (EmptyResultDataAccessException e) {
        SQLException sqle = (SQLException) e.getCause();
        logger.debug("No client was found");
        logger.debug(sqle.getMessage());
        return null;
    }

    return clientID;

In the above code, no row was returned and I want to properly handle it (I have not coded that portion yet). Instead, the catch block is never triggered and instead, my generic error handler and associated error channel is triggered instead.

Segment from file BatchIntegrationConfig.java:

    @Bean
    @ServiceActivator(inputChannel="errorChannel")
    public DefaultErrorHandlingServiceActivator errorLauncher(JobLauncher jobLauncher){
        logger.debug("====> Default Error Handler <====");
        return new DefaultErrorHandlingServiceActivator();
    }

Segment from file DefaultErrorHandlingServiceActivator.java:

public class DefaultErrorHandlingServiceActivator {
    @ServiceActivator 
    public void handleThrowable(Message<Throwable> errorMessage) throws Throwable {
        // error handling code should go here
    }
}

Tested Facts:

1. queryForObject expects a row to be returned and will thrown an exception if otherwise, therefore you have to handle the exception or use a different query which returns a row.
2. Spring Integration is monitoring exceptions and catching them before my own code can hand them.

What I want to be able to do:

• Catch the very specific condition and log it or let the end user know what they need to do to fix the problem.

Edit on 10/26/2016 per recommendation from @Artem:

Changed my existing input channel to Spring provided Handler Advice:

@Transformer(inputChannel = "memberInputChannel", outputChannel = "commonJobGateway", adviceChain="handleAdvice")  

Added support Bean and method for the advice:

    @Bean
ExpressionEvaluatingRequestHandlerAdvice handleAdvice() {
    ExpressionEvaluatingRequestHandlerAdvice advice = new ExpressionEvaluatingRequestHandlerAdvice();
    advice.setOnFailureExpression("payload");
    advice.setFailureChannel(customErrorChannel());
    advice.setReturnFailureExpressionResult(true);
    advice.setTrapException(true);
    return advice;
}

private QueueChannel customErrorChannel() {
    return new DirectChannel();
}

I initially had some issues with wiring up this feature, but in the end, I realized that it is creating yet another channel which will need to be monitored for errors and handled appropriately. For simplicity, I have chosen to not use another channel at this time.

Answer 1

Although potentially not the best solution, I switched to checking for row counts instead of returning actual data. In this situation, the data exception is avoided.

The main code above moved to:

    MapSqlParameterSource mapParameters = new MapSqlParameterSource();
    mapParameters.addValue("clientname", clientName);

    // Step 1 check if client exists at all; if exists, continue
    // Step 2 check if client enrollment rules are available
    if (this.namedParameterJdbcTemplate.queryForObject(COUNT_BY_NAME, mapParameters, Integer.class) == 1) {
        if (this.namedParameterJdbcTemplate.queryForObject(CHECK_RULES_BY_NAME, mapParameters, Integer.class) != 1) return null;
    } else return null;

    return findClientByName(clientName);

I then check the data upon return to the calling method in Spring Batch:

        if (clientID != null) {
        logger.info("Found client ID ====> " + clientID);
    }
    else {
        throw new ClientSetupJobExecutionException("Client " + 
                fileNameParts[1] + " does not exist or is improperly setup in the database.");
    }

Although not needed, I created a custom Java Exception which could be useful at a later point in time.

Answer 2

Spring Integration Service Activator can be supplied with the ExpressionEvaluatingRequestHandlerAdvice, which works like a try...catch and let you to perform some logic onFailureExpression: http://docs.spring.io/spring-integration/reference/html/messaging-endpoints-chapter.html#expression-advice

Your problem might be that you catch (EmptyResultDataAccessException e), but it is a cause, not root on the this.namedParameterJdbcTemplate.queryForObject() invocation.