Announcement Announcement Module
No announcement yet.
Database Query Paging? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database Query Paging?

    I have a requirement to read all rows from a database and send each row through as a message. Unfortunately this is a large database. I'm running out of heap when I try to execute using jdbc:inbound-channel-adapter to read it all in. I can probably chunk up and run separate queries to break down the size, but I would prefer to do this with paging.

    I see that the jdbc:inbound-channel-adapter has a max-rows-per-poll parameter, but can't see anywhere if this is for paging and if so how it will work.

    If the max-rows-per-poll does not implement paging, is there a native mechanism for it?

  • #2
    Have you considered using Spring Batch for that part of your use-case? You could easily use an ItemWriter that sends a Message to a channel. But for that initial loading step (the ItemReader side0, Spring Batch has components that can handle the job.


    • #3
      This sits in the middle of a much larger Spring Integration project. In the past, I have looked at merging Spring Integration and Spring Batch, but didn't pursue it.

      I assume then that the max-rows-per-poll is not for paging then...


      • #4

        About light of "max-rows-per-poll", please, read this upcomming issued GitHub Pull Request:

        If the max-rows-per-poll does not implement paging
        In two words: it is paging by nature. You're writing some query with order and the value of 'max-rows-per-poll' are applied on ResultSet to build the List as payload for outbound Message.
        Than you should provide some update SQL-expression to avoid retrive the same rows on next poll.

        Is it what are you looking for?

        Artem Bilan

        P.S. Also you can write some query with paging parameters: Oracle sample
        SELECT *
        	FROM (
        	       SELECT a.*, rownum rnum
        	       FROM ( /* PUT YOUR MAIN QUERY */) a
        	       WHERE rownum <= :UPPERBUOND
        	WHERE rnum >= :LOWERBOUND


        • #5
          That could work, but I'm actually querying against a view over some tables in a database that I can't update. The application I'm writing is an integration layer between an application we are buying and our systems. We don't want to update their tables directly.


          • #6
            We don't want to update their tables directly
            Let me asume that this tables are updated by exernal system, so you don't have any guarantee, that you may retrive the same row on the next poll.
            Well, here it will be enough to try to use DB vendor specific paging query.
            On the downstream message-flow you should place <splitter> on queue or executor channel to have ability process each row as Message.

            Or you should ask us about support for surrogate pattern JDBC-Splitter ;-)
            Someday I'll publicate my implementation about it on GitHub.