How to exclude a collection with a JPA query

Question

I'm creating a database query with JPA and need to exclude a list from the result. How should I do this?

public List<PersonEntity> getPersonsWithoutNotWanted(List<Long> notWantedId ) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<PersonEntity> cq = cb.createQuery(PersonEntity.class);

    Root<PersonEntity> root = cq.from(PersonEntity.class);

    // How to add a Predicate or something else to remove all notWantedId's

    TypedQuery<RequestableEntity> query = em.createQuery(cq);
    return query.getResultList();
}

I can not figure it out with my collection: List notWantedId — Dimitri Dewaele, Sep 25 '14 at 07:25

score 1 · Answer 1 · edited May 23 '17 at 12:13

Just make use of IN, with something like

cb.in(predExprForId, notWantedIds).not()

where predExprForId is the predicate for the id field, and use this predicate as the where clause. No details are possible beyond that since you provide no classes

But make sure that your collection is not too large. Your database implementation will have a limit on the number of parameters allowed in a single query so there is a practical limit to the size of notWantedIds.

See this post for a related discussion

JPA How to get the number of parameter markers used in Criteria API?

How to exclude a collection with a JPA query

1 Answers1