DB Insert if not present - Spring Integration

Question

We have a int-jms:message-driven-channel-adapter --> a transformer --> a filter --> another transformer --> int-jdbc:outbound-channel-adapter (to insert in table_1) (considering --> as channels)

I want to change this flow to insert into 2 tables instead of 1 but for table_2 I want to insert only if data corresponding to some fields in the message is already not present in the table i.e. insert if not present.

One thing I figured is that I will now need a pub-sub-channel with ignore-failures=false to split the flow into 2 tables.

My question is that what component should I use to select the data to check if data exists in table_2? I first thought inbound-channel-adapter is the right choice but couldn't figure out how to slide it between 2 components i.e. say a transformer and outbound-channel-adapter.

Some things I can think of are: 1. Use a filter, passing it jdbcTemplate so that the filter itself can fire a JDBC query to accept if record doesn't exist. 2. Use a outbound-channel-adapter and the insert query should have the check for data existence also, something like insert-if. I am not sure if Oracle has something like this. I am researching.

Please point me to an example or documentation or tell me the best way.

Thanks

Artem Bilan Artem Bilan · Accepted Answer · 2014-10-11T14:25:55

Actually you can use

<chain>
    <header-enricher>
        <header name="original" expression="payload"/>
    </header-enricher>
    <int-jdbc:outbound-gateway query="SELECT count(*) from TABLE where ..."/>
    <filter expression="payload == 0"/>
    <transformer expression="headers.original"/>
</chain>

From other side <filter> with JdbcTemplate direct usage is good choice too.

Re. insert-if. It can work too if you have a unique constraint on the table. In this case an Exception will be thrown. And if you have <publish-subscribe-channel ignore-failures=false>, it would work too.

DB Insert if not present - Spring Integration

1 Answers