Spring JpaRepository findBy…In(Collection) returns

Question

I have a query method in my JpaRepository

    Page<Course> findDistinctCourseByAttrsInAllIgnoreCase(Set<String> a, Pageable page);

to find Course objects by their instance variable Set<String> attrs. Given a Set a with "foo" and "bar", I want to find Courses whose attrs contain BOTH "foo" and "bar", i.e. an intersection of Courses with "foo" and those with "bar". This method above returns a union.

Is there a way to do this with JpaRepository queries or do I have to make multiple calls and find the intersection myself?

Answer 1

Something like this should make it:

@Query("SELECT c FROM Course c JOIN Attribute a WHERE LOWER(a.name) IN (:attributes) GROUP BY c HAVING COUNT(c) = :size")
public Page<Course> findByAllAttributes(@Param("attributes") Set<String> attributes, @Param("size") Integer size, Pageable page);

and you call it like this:

Page<Course> page = findByAllAttributes(set.stream()
      .map(String::toLowerCase)
      .collect(Collectors.toSet(), 
   set.size(), page);

Answer 2

In the unlikely case that you know the number of as up front you could combine multiple constraints with And:

...AttrsInAndAttrsIn...

But even if the precondition holds that would be very ugly.

So the next best option is probably a Specification and a factoryMethod constructing the Specification from a Set<String> or from varargs.

Your repository needs to extend JpaSpecificationExecutor. You would call it like this

Page<Course> findAll(matchesAll(attrs), pageable) 

And the factory method would look something like this:

Specification<Course> matchesAll(Set<String> attrs) {
    return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
        // construct Predicate by combining calls to builder.isMember
        // https://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaBuilder.html#isMember(E,%20javax.persistence.criteria.Expression)

    }
}