JPA Criteria Query with IN operator for Set<?> with Spring DATA-JPA

Question

In my application, I have the following mapping between two entities :

@Entity
public class Applicant {
     private Integer id;
     ....
     private Set<Document> documents;

     ... Getters and Setters ...

     @OneToMany(fetch = FetchType.LAZY, mappedBy = "applicant", cascade = CascadeType.ALL)
     public Set<Document> getDocuments() {
     return documents;
     }

    public Applicant setDocuments(Set<Document> documents) {
        this.documents = documents;
        return this;
    }
}

And Document :

public class Document {

    private Long id;
    private Applicant applicant;

    ... Getters and Setters ...

    @ManyToOne
    public Applicant getApplicant() {
        return applicant;
    }

    public Document setApplicant(Applicant applicant) {
        this.applicant = applicant;
        return this;
    }
}

I want to use the Spring Data Specification (org.springframework.data.jpa.domain) to filter some applicant in my ApplicantRepository with the findAll(Spec spec) method.

But, my problem is I want to create a specification witch take in parameters a Set and build a specification to filter the applicant who are not linked to one (not all) of this document.

I've tried different things but none of them work... I don't know if I am forgetting something. The first one was to use the criteriaBuilder and value method...

public static Specification<Applicant> applicantHasDoc(final Set<Document> documents) {
        return new Specification<Applicant>() {
            @Override
            public Predicate toPredicate(Root<Applicant> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                /*
                    Problem during parsing the query :
                    select *
                    from
                        applicant applicant0_ cross join document documents1_
                    where
                        applicant0_.id=documents1_.applicant
                        and (. in (? , ?))
                */
                Expression<Set<Document>> documentExpression = root.get(Applicant_.documents);
                return cb.in(documentExpression).value(documents);
        };
    }

That's returning an GrammarSQL exception... you can see the SQL query (simplified on the applicant fields) in the code.

The second solution was to use metamodel and In directly on the ROOT of applicant :

public static Specification<Applicant> applicantHasDoc(final Set<Document> documents) {
        return new Specification<Applicant>() {
            @Override
            public Predicate toPredicate(Root<Applicant> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                /*        
                    Error with this type of criteria : Parameter value [com.myapp.entity.Document@1b275eae] did not match expected type [java.util.Set (n/a)]
                */
                    return root.get(Applicant_.documents).in(documents);
            }
        };
    }

I have add in the code the result of each solution... and none of them work.

The main purpose of this Specification is to be used with others like that :

List<Applicant> applicants = findAll(where(applicantHasDoc(documents).and(otherSpec(tags)).and(anotherSpec(mobilities), page);

So I can only work inside a Spring Data JPA Specification.

Other information : I'm using H2 Database.

Thanks for your help.

Kevin Davin Kevin Davin · Accepted Answer · 2014-10-03T05:58:49

I find the right way to do that, and all my attempt was bad because I was thinking "like object" and not in SQL... but CriteriaQuery is a "object wrapper" to build SQL query.

So, I wrote the query I want in SQL and I found the solution :

What I want in SQL was :

select * 
from applicant applicant0_ inner join document documents1_ on applicant0_.id=documents1_.applicant where documents1_.id in (? , ?)

So my predicate seems to be the following :

   public static Specification<Applicant> applicantHasDoc(final Set<Document> documents) {
        return new Specification<Applicant>() {
            @Override
            public Predicate toPredicate(Root<Applicant> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                SetJoin<Applicant, Document> documentApplicantJoin = root.join(Applicant_.documents);
                return documentApplicantJoin.in(documents);


            }
        };
    }

JPA Criteria Query with IN operator for Set<?> with Spring DATA-JPA

1 Answers