Announcement Announcement Module
No announcement yet.
Can a JDBC outbound-channel-adapter update multiple rows in a database? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Can a JDBC outbound-channel-adapter update multiple rows in a database?

    We have a JDBC outbound-channel-adapter configured thus:
    HTML Code:
       query="UPDATE USERS SET FIRSTNAME = :payload[firstname], SURNAME = :payload[surname], DOB = :payload[dateOfBirth] WHERE ID = :payload[id]"
    In this configuration, the payload contains a single User object flattened down to a Map using object-to-map-transformer:
    HTML Code:
    This is simple proof-of-concept code to help us understand Spring Integration and its role in our project. Our real use case involves updating many users in a single database hit.

    I tried to feed object-to-map-transformer with a Collection of Users but it returns an empty payload. Even feeding the transformer with a simple Collection of Strings results in an empty payload.

    How can we convert a Collection of Users into something used by jdbc:outbound-channel-adapter to update multiple rows in the target database?

  • #2
    My initial thought is that you want to add a splitter prior to the transformer. The splitter will take a Collection payload and then send out N individual Messages where N is the size of the Collection. Therefore, the rest of the flow (including your outbound-channel-adapter) will be the same as in the single User case.

    Does that help?


    • #3
      That was quick

      I wondered about that but then considered the network and database performance hit of N individual updates instead of a single update for all rows.


      • #4
        I understand that concern. I guess you ideally want to do a batch update? Another option, while not using a Spring Integration adapter, is still pretty straightforward. You could go ahead and send the Collection<User> Message payload to a Service Activator and just have some simple code there that uses Spring's JdbcTemplate directly, calling the batchUpdate() method.

        Of course, if your use-case is more involved, you could connect Spring Integration and Spring Batch together, keeping Spring Integration focused on coarse-grained events/messaging ("batch of Users arrived" -> launch batch job).


        • #5
          I hadn't thought about it like that - batch update is exactly what we're doing.

          We have several legacy systems, each with their own database. There's a common set of data (mostly user data) that must be kept in sync across these legacy systems. Batch update fits perfectly.

          I'll investigate the two solutions that you suggest. They're both much better than abusing the JSBC outbound-channel adapter for a batch update