Spring JPA Query to get Sub-List of provided IDs not in Table

Question

Is there a way using a Spring JPA Repository Query to get a sub-list of the IDs that were not present in our table given a list of IDs?

Something like this:

@Query(value = "Some query returning a sublist of orderIds not in TABLE")
List<String> orderIdsNotInTable(@Param("orderIds") List<String> orderIds);

I found a link here but I cant think of how to make that a JPA Query.

EDIT: The goal here is to save on running memory so if there are thousands of ids and many calls happening at once I would like it to be handled without creating a second copy of all the ids potentially.

have you considered using a native query or you want to stay DB-agnostic? — vladtkachuk, Aug 12 '22 at 11:11
@vladtkachuk I think DB agnostice because this query will have to work on to different DB systems — Nick Pavini, Aug 12 '22 at 17:08
@Olivier could be a frw thousand but potentially multiple api calls happening at once each with that many — Nick Pavini, Aug 15 '22 at 05:34

score 0 · Answer 1 · answered Aug 05 '22 at 11:09

0

As from what your questions asks, my solution would be:

retrieve the list of IDs present in your database:

   @Query("select t.id from Table t")
    List<String> findAllIds();

Loop through the list of IDs you have and look up if the list of IDs from the database table does not contain your id.

 List<String> idsNotContained= orderIds.stream()
    .filter(!findAllIds()::contains)
    .collect(Collectors.toList());

answered Aug 05 '22 at 11:09

Pawan.Java

671
1
5
21

I would ideally like everything to be done in thenquery as to save memory within the api. – Nick Pavini Aug 05 '22 at 11:45
You need to understand one thing that the db will return either only a list of values present in the database or an empty list based on your argument. For comparison pruposes, you shall implement a logic (subroutine) at query level that would return the values whose ID has not matched your ID column in the database. – Pawan.Java Aug 05 '22 at 11:54
so you dont think what they did in the link i provided is possible with jpa? – Nick Pavini Aug 05 '22 at 13:30
In the link you have provided they are adding the missing value somehow on the database `with v (id) as ( values (4),(5),(6) )`, he wants the value 5 that doesn't exists in the database, and if you don't provide somehow the value you need in the database it is not feasible – Pp88 Aug 09 '22 at 15:54
@NickPavini How will a list of String IDs would consume memory in your API. Does your list contains 1 M records? In this case bring subsections of the list from the select until you reach end of list and then do your comparison for each sub sections. – Pawan.Java Aug 10 '22 at 07:09
Perform a query first to get the count of total records in your select. Then divide by the number of records you want your list to fetch per each query. Then you query by first rownum and last rownum until count value. Do your comparison as above. This will be possible to achieve by Native JPA query as well as save memory. See [ROWNUM](https://stackoverflow.com/questions/4552769/sql-rownum-how-to-return-rows-between-a-specific-range) – Pawan.Java Aug 10 '22 at 07:21
@Pawan.Java `ROWNUM` is specific to Oracle. – Olivier Aug 11 '22 at 07:02

score 0 · Answer 2 · answered Aug 13 '22 at 14:55

Building on @Pawan.Java solution, I would look for the ids and then apply the filtering.

List<String> findByIdIn(List<String> ids);

The list which is returned will contain the ids which exist, it is then just a matter of removing those ids from the original list.

original.stream().filter(i -> 
  !existingIds.contains(i)).collect(Collectors.toList());

If there is a large number of ids being passed in, then you might want to consider splitting them into parallel batches.

score 0 · Answer 3 · answered Aug 14 '22 at 08:26

0

If your list is not too big, then an easy and efficient solution is to retrieve the IDs from the list that are in the table:

select t.id from Table t where t.id in (id1, id2, ...)

Then a simple comparison between initial and returned lists will give you the IDs that are not in the table.

answered Aug 14 '22 at 08:26

Olivier

13,283
1
8
24

Your answer is good but write in Java to provide a complete one. – Pawan.Java Aug 16 '22 at 08:19

score -3 · Answer 4 · answered Aug 05 '22 at 10:46

-3

@Query(value = "SELECT t.id FROM TABLE t WHERE t.id NOT IN :orderIds")
List<String> orderIdsNotInTable(@Param("orderIds") List<String> orderIds);

I don't know if I understood you correctly but can you try the solution above.

answered Aug 05 '22 at 10:46

Candoğuş Yılmaz

44
5

This query will output all the IDs that are not present in the argument passed. From my perspective, the question is looking for all the IDs that have not returned an object from the database and hence output those. – Pawan.Java Aug 05 '22 at 10:59
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 08 '22 at 15:43
That returns rows that don't match order ids. He want's to get ids that are missing - ids that returns "no" row – Frido Aug 12 '22 at 08:42

Spring JPA Query to get Sub-List of provided IDs not in Table

4 Answers4