How to provide variable parameters for custom quarries in spring data jpa and not use for loop

Question

I have two databases and I have to search for a list of products based on a range(from and to) from 1st DB but the problem is the number of ranges is unknown.

currently, from 1st DB I am getting a list of range(from and to). then using for loop to query the 2nd DB.

    //1st getting list of ranges
List<Ranges> ranges = this.productRepository.getRangeFromAndTo();

//query to get ranges
@Query(value = "SELECT * FROM PRODUCT_RANGE WHERE  productId = ?1 AND goodToUse = ?2 AND CHECKOUT is null", nativeQuery=true)
List<Range> getRangeFromAndTo(String productId, String goodToUse);

//using for loop to query 2nd db
ranges.forEach(range ->{ productService.countProducts(range.getRangeFrom(),fnnRange.getRangeTo());});

//this is the query
@Query(value="select Count(*) from RESOURCE where RESOURCE_VALUE between ?1 and ?2 and upper(RESOURCE_STATUS)=upper('available')", nativeQuery=true)
Long countByresourceValueBetween(String fnnRangeFrom, String fnnRangeTo);

I want to query the 2nd DB at one go to reduce the time.

so you want to execute both methods parallel ? – Ryuzaki L Jul 18 '19 at 06:27 — Ryuzaki L, Jul 18 '19 at 06:27

score 0 · Answer 1 · answered Jul 18 '19 at 06:39

Because you don't know how many ranges you have in advance, the best aproach I think is to send all the queries to the second database in parallel, wrapping every call in a CompletableFuture, for example, and then combine all of them into one future to get the final result. This way you only have to wait for the slower query instead of waiting for the sum of all queries.

Here is an example of how convert a list of completablefutures into a completablefuture of a list. List<Future> to Future<List> sequence

When the query completes if you wat the sum of all elements you can use

result.stream().mapToLong(it->it).sum();

How to provide variable parameters for custom quarries in spring data jpa and not use for loop

1 Answers1