I would imagine we will have requests for a use-case where an UPDATE is executed after the SELECT (within the same TX).

For example:

SELECT * FROM ORDERS WHERE PROCESSED=0 AND PROCESSING=0

UPDATE ORDERS SET PROCESSING=1 WHERE ID IN (...[IDs from above]...)

More complex use-cases might be candidates for Spring Batch.

This use case describes the requirements I have.

I was dealing with the same problem in Mule, then in Spring Integration.

For the above scenarion (synchronous processing of "messages" incoming from database) it's all ok.

The problem begins with asynchronous processing. When e.g. there is one "dispathing thread" which only reads what's "not processed" from database (by means of "processing" column or some other updates) and sends it to some asynchronous channel.

I'm writing it not to change the requirements for inbound-jdbc-adapter but for completeness. Maybe it's worth mentioning in documentation or it's a candidate for another issue.

So in my case, there is a filter which removes duplicates (by returning a value of set.add() invocation). To remove the possibility of memory leaks, the filter is also a listener of "MessageProcessed" events which remove the messages from internal set. The problem is that each component which actually processes an event (e.g. setting PROCESSED to 1 in database in different transaction) must publish the event.

My solution is very pragmatic, not meant (in its present form) to be a general solution.

regards
Grzegorz Grzybek

First pass implementation completed. Supports polling for a list of maps or providing a RowMapper. Post poll an update can be executed in the same transactions in order to note processed rows. The update can execute either once per poll or once per polled row.

Would it be simple for the adaptor to do something like
select id from table and return a List<Long> values as the payload?

It would be fairly simple, the namespace could provide support for payload-type="Long" or something along those lines and then have it create an appropriate row mapper for some set of known payload types. I think this keeps things simpler than adding support for lots of payload types into the core adapter.

Are you thinking of some sort of lazy loading of the data further down the message flow?

yes the thought is to be able to do lazy loading further down in the integration and keeping the jdbc adaptor simple so when it polls the database it retrieves a simple list of primitive data types.
So are you saying the namespace already allows payload-type="Long" ?

For other cases, you have nicely provided the rowmapper anyhow. I do appreciate you are building an jdbc adaptor which is exactly what we are missing right now

Thanks