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

user_6300655 0
Viewed 254

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.

spring-data-jpa jpa java
4 Answers

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

  1. retrieve the list of IDs present in your database:
   @Query("select t.id from Table t")
    List<String> findAllIds();
  1. 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());
user_146697450

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.

user_69529380

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.

user_127639540 
@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.

user_164273770
Related