JPA channel or gateway paged data support using max-number-of-results

[menno.j]

The JPA Channel Input Adapter and JPA Retrieving Outbound Gateway have a max-number-of-results settings.

Is it possible to use this in order to ensure the data is sent onto the channel in a paged fashion? I couldn't see a way in the documentation to configure an adaptor or gateway so it sends the data in this way.

I have use cases where I am retrieving entities by entity-class, and there can be occasions when the sheer number of entities in the datasource would cause memory issues if all retrieved and put on the channel in one message. In those cases I'd like to have for example a 1000 entities put on the channel at a time. Is that possible? For example if I was just using a Spring Data Repository I'd just call the findAll method with a Pageable.

Cheers,
Menno

[ghillert]

Hi Menno,

Thanks for your question! As you noted, we provide the "max-number-of-results" attribute. It is used to set "javax.persistence.Query#setMaxResults". Unfortunately, we currently (As of 2.2.0.RELEASE) do not expose the "setFirstResult" property. Thus presently it would be tricky to implement pagination. While we provide a pluggable "JpaOperations" interface (With the DefaultJpaOperations implementation) in order to allow customized behavior, this would not help your situation too much as we would need to dynamically pass in the "setFirstResult" property.

I have created the following Jira to add this feature to the JPA Outbound Gateway.

https://jira.springsource.org/browse/INT-2881

My thinking is to provide the "setFirstResult" parameter either as a SpEL expression (provided through namespace configuration, e.g. first-result-expression) or by providing a respective Message Header. Would this satisfy your requirement?

Thanks!

Cheers,

Gunnar

[menno.j]

My thinking is to provide the "setFirstResult" parameter either as a SpEL expression (provided through namespace configuration, e.g. first-result-expression) or by providing a respective Message Header. Would this satisfy your requirement?

Hi Gunnar,

I could see how it could work with a Retrieving Outbound Gateway, but how would you see this working with a JPA Channel Input Adapter configured to retrieve entities using the entity-class attribute. How would the setFirstResult be incremented, seeing as it's the start of the flow? And would the increment happen on a subsequent poll, or would you be able to configure the adapter to put all pages onto the channel on one poll?

Personally for my use case I'd like to be able to specify a page size, eg the number of entities per page, and have the adapter or gateway put the entities onto the channel, but just to do it in several messages corresponding to the page size. In this way I could just use configuration to restrict the size of the messages sent. Makes sense?

Cheers,
Menno

[ghillert]

Hi Menno,

I think I understand the intend for the JPA Inbound Channel Adapter. However, I am not so sure in regards to its practicality.

Generally the adapters (Particularly the inbound adapters) are stateless (You can start/stop them but not much else). Thus, even if we keep the state of the page, what shall happen once we don't retrieve any further records? In that case, would we need to know the total number of records and then reset the page attribute eventually? Furthermore, wouldn't you have the requirement to mark any retrieved records as being 'in process'/'processed'? Or do you have a table that grows continuously and thus you page through the table forever, in which case you may need to also persist the page number?

If you could maybe explain your use-case a little further, that would be quite helpful.

Thanks!

Cheers,

Gunnar

[menno.j]

Hi Gunnar,

The main problem I have with the current JPA Retrieving Outbound Gateway and Inbound Channel Adapter is that I can have a several hundred thousand entities in the table. I want to avoid putting these all onto the channel in one go. It would cause problems with components down stream. Also it might cause a memory problem.

Ideally the jpa gateway or adapter would retrieve and send the entities on the channel in several pages of a specified size.

I could work around this issue in the case of data the application owns, because I could design for using max-number-of-results combined with delete-after-poll set to true. But in certain use cases this is data in client systems that I'm integrating with, and where I have only read access to the data so I cant use delete-after-poll set to true.

I hope that makes my use case clearer.

Thanks for your help,
Menno

[ghillert]

Hi Menno,

Sorry for the delay - I had to think about this a bit. As I stated earlier, adding some pagination support makes in my opinion sense for the Outbound Gateway but I am still hesitant in regards to adding pagination support to the JPA Inbound Channel Adapter. Right now the Inbound Channel Adapter is basically stateless and adding that sort of paging support would require us to maintain state.

Also exhibiting some batch concerns, this would create also overlap with the Spring Batch project. In that regard, have you looked at the Spring Batch-provided JpaPagingItemReader [1] + [2]? It looks like that would be the perfect match to your requirement.

Cheers,

Gunnar

[1] http://static.springsource.org/sprin...ingItemReaders
[2] http://static.springsource.org/sprin...temReader.html