How do I configure this JdbcMessageHandler to pull parameters from the message instead of static beans?

Question

The Symptom:

When invoked, the cleanupMessageHandler component (a JdbcMessageHandler) of this subflow is not pulling parameters from the message, but is instead attempting to pull from a static bean.

The exception message:

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'ALC_startTime': Invalid property 'ALC_startTime' of bean class [org.springframework.integration.jdbc.JdbcMessageHandler$1]: Bean property 'ALC_startTime' is not readable or has an invalid getter method: Does the return type of the getter match the parameter type of the setter?

The SQL

Delete from SB_STREAM.TT_9321383 
WHERE (LASTUPDATESTAMP >= to_timestamp (:ALC_startTime, 'YYYY-MM-DD HH24:MI:SS.FF') 
    and LASTUPDATESTAMP <= to_timestamp (:ALC_endTime, 'YYYY-MM-DD HH24:MI:SS.FF')) 
AND ttkey = BIGINT(:ID) 
AND (ttobjectname = :ALC_object AND ttschema = :ALC_schema)

The Flow:

   return flowDef
        .filter(getFilterExpression(rule)).channel(new DirectChannel())
        .handle(inboundAdapter) // a JdbcOutboundAdapter
        .split(insertDeleteSplitter)
        .publishSubscribeChannel(taskExecutor, c -> 
                c.subscribe(s -> s
                    .filter ("....")
                    .transform(genericTransformer)
                    .handle(insertUpdateMessageHandler(rule))) // a JDBCMessageHandler
                .subscribe(s -> s
                    .filter("....")
                    .transform(genericTransformer)
                    .handle(deleteMessageHandler(rule))) // a JDBCMessageHandler
                .subscribe(sub -> sub
                    .handle(cleanupMessageHandler(rule))) // a JDBCMessageHandler
              .errorHandler(new CustomErrorHandler() // 
        );

The cleanupMessagehandler

private MessageHandler cleanupMessageHandler(RuleMetadata rule) {

<snip ...>
    SQLTextAndParamsList sql = sqlFactory.getCleanupSQL(rule);
    JdbcMessageHandler handler = new JdbcMessageHandler(dbprx.getDatasource(), sql.getSql());

    return handler;
}

Message as received by JdbcMessageHandler

SQLParameterSources at point where JDBCOperation is invoked

Alternate coding - batchArray of values at JDBCOperation is invoked (added ExpressionEvaluatingSqlParameterSourceFactory to the JdbcMessageHandler)

Debugger view at AbstractNestablePropertyAccessor.getPropertyValue()

Debugger view at CachedIntrospectionResults.getPropertyDecriptor()

---

Edit: code changes

The generated SQL was updated to this:

Delete from SB_STREAM.TT_9321383 
WHERE (LASTUPDATESTAMP >= to_timestamp (:payload[ALC_startTime], 'YYYY-MM-DD HH24:MI:SS.FF') 
    and LASTUPDATESTAMP <= to_timestamp (:payload[ALC_endTime], 'YYYY-MM-DD HH24:MI:SS.FF')) 
AND ttkey = :payload[ID] 
AND (ttobjectname = :payload[ALC_object] AND ttschema = :payload[ALC_schema])

Answer 1

According to your first screenshot, the payload of your message is an ArrayList, which indeed does not have those ALC_startTime, ALC_endTime properties. There are just no those getters on the ArrayList!

To be able to read a content of your list, you need to configure an ExpressionEvaluatingSqlParameterSourceFactory on your JdbcMessageHandler. See docs for more info: https://docs.spring.io/spring-integration/docs/current/reference/html/jdbc.html#passing-parameters-by-using-spel-expressions.

Probably better to convert your list into a Map somehow or some POJO representation. With the list it is going to be hard for your to determine the target value by just index...

---

Edit: This is a batch update, so the array list of maps is correct.

The SPEl expressions in the SQL parameters are malformed. The correct form is :payload[ALC_startTime].