CODEWITHSUNDEEP
jpacriteria-api
Dimitri Dewaele
Dimitri Dewaele

Reputation: 10689

How to exclude a collection with a JPA query

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();
}

Upvotes: 0

Views: 1570

Answers (1)

user3973283
user3973283

Reputation:

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?

Upvotes: 1

Related Questions