Apache Camel JDBC Polling and Idempotency -

i'm building simple camel route should continuously poll table , push data activemq. each poll should pull data not pulled. best way can think of keep track of last processed sequence id , select items sequence id greater previous one.

is there standard way this?

it's possible, in multi user database, rows lower sequence id committed after rows higher id (certainly in oracle & sqlserver, suspect in dbms transaction support). in case keeping track of last processed id result in rows never processed.

the simplest solution problem, if have control on schema , thing processing table, add sort of ‘processed’ column table , update column (as @arnaud suggests).

if isn’t option there 3 other mechanism have considered solving issue:

1. add on-insert trigger table insert record in companion table references table , includes processed column. change query join 2 tables, , mark row processed updating companion table in same way above.
2. create companion table , insert rows in here mark row in main table processed. poll query need rows don’t exist in companion table.
3. keep track of missing sequence ids. explicitly on each poll.

i opted 2, easier implement in dbms independent fashion, , required insert mark row processed.