Spring JpaRepository findBy...In(Collection) returns union not intersection

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

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)

    }
}