How to know the missing items from Spring Data JPA's findAllById method in an efficient way?

Consider this code snippet below:

List<String> usersList = Arrays.asList("john", "jack", "jill", "xxxx", "yyyy");
List<User> userEntities = userRepo.findAllById(usersList);

User class is a simple Entity object annotated with @Entity and has an @Id field which is of String datatype.

Assume that in db I have rows corresponding to "john", "jack" and "jill". Even though I passed 5 items in usersList(along with "xxxx" and "yyyy"), findAllById method would only return 3 items/entities corresponding to "john","jack",and "jill".

Now after the call to findAllById method, what's the best, easy and efficient(better than O(n^2) perhaps) way to find out the missing items which findAllById method did not return?(In this case, it would be "xxxx" and "yyyy").

Using Java Sets

You could use a set as the source of filtering:

Set<String> usersSet = new HashSet<>(Arrays.asList("john", "jack", "jill", "xxxx", "yyyy"));

And now you could create a predicate to filter those not present:

Set<String> foundIds = userRepo.findAllById(usersSet)
     .stream()
     .map(User::getId)
     .collect(Collectors.toSet());

I assume the filter should be O(n) to go over the entire results.

Or you could change your repository to return a set of users ideally using a form of distinct clause:

Set<String> foundIds = userRepo.findDistinctById(usersSet)
       .stream()
       .map(User::getId)
       .collect(Collectors.toSet());;

And then you can just apply a set operator:

usersSet.removeAll(foundIds);

And now usersSet contains the users not found in your result.

And a set has a O(1) complexity to find an item. So, I assume this should be O(sizeOf(userSet)) to remove them all.

Alternatively, you could iterate over the foundIds and gradually remove items from the userSet. Then you could short-circuit the loop algorithm in the event you realize that there are no more userSet items to remove (i.e. the set is empty).

Filtering Directly from Database

Now to avoid all this, you can probably define a native query and run it in your JPA repository to retrieve only users from your list which didn't exist in the database. The query would be somewhat as follows that I did in PostgreSQL:

WITH my_users AS(
   SELECT 'john' AS id UNION SELECT 'jack' UNION SELECT 'jill'
) 
SELECT id FROM my_users mu 
WHERE NOT EXISTS(SELECT 1 FROM users u WHERE u.id = mu.id);

Spring Data: JDBC Example

Since the query is dynamic (i.e. the filtering set could be of different sizes every time), we need to build the query dynamically. And I don't believe JPA has a way to do this, but a native query might do the trick.

You could either pack a JdbcTemplate query directly into your repository or use JPA native queries manually.

@Repository
public class UserRepository {
    
    private final JdbcTemplate jdbcTemplate;

    public UserRepository(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}

    public Set<String> getUserIdNotFound(Set<String> userIds) {
        StringBuilder sql = new StringBuilder();
        for(String userId : userIds) {
            if(sql.length() > 0) {
                sql.append(" UNION ");
            }
            sql.append("SELECT ? AS id");
        }

        String query = String.format("WITH my_users AS (%sql)", sql) +
                "SELECT id FROM my_users mu WHERE NOT EXISTS(SELECT 1 FROM users u WHERE u.id = mu.id)";

        List<String> myUsers = jdbcTemplate.queryForList(query, userIds.toArray(), String.class);

        return new HashSet<>(myUsers);
    }

}

Then we just do:

Set<String> usersIds = Set.of("john", "jack", "jill", "xxxx", "yyyy");
Set<String> notFoundIds = userRepo.getUserIdNotFound(usersIds);

There is probably a way to do it with JPA native queries. Let me see if I can do one of those and put it in the answer later on.

You can write your own algorithm that finds missing users. For example:

List<String> missing = new ArrayList<>(usersList);

        for (User user : userEntities){
            String userId = user.getId();
            missing.remove(userId);
        }

In the result you will have a list of user-ids that are missing:

"xxxx" and "yyyy"

You can just add a method to your repo:

findByIdNotIn(Collection<String> ids) and Spring will make the query:

See here:

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods

Note (from the docs):

>> In and NotIn also take any subclass of Collection as aparameter as well as arrays or varargs.