JPA Criteria API - how to select like "field not in"

Question

I have the following situation.

public class TestExecution {

    private Collection<TestExecutionTag> testExecutionTags;

}


public class TestExecutionTag {


    private Tag tag;

}


public class Tag {

    private String name;

}

What I now want to do is to perform following query using standard JPA Criteria API, something like:

Select test executions (TestExecution) which don't have associated any of the tags (Tag) with name field value in provided list.

I would describe it as

Select testExecution WHERE testExecution.testExecutionTag.tag.name NOT IN (<ArrayList of String values>).

Is something like this possible?

EDIT: I'm sorry I didn't specify it correctly, since I thought it's irrelevant. The whole goal was actually to select test executions with specific tags but from them exclude those which contain other tags.

Have you checked http://stackoverflow.com/questions/5115422/not-in-constraint-using-jpa-criteria? — Priyesh, Apr 28 '14 at 11:30
Thanks for reply! Actually I did, I tried exactly that. Still not working :-( Would it be helpful if I posted the generated JPQL query? Unfortunately I think it wouldn't be helpful to past the whole code since it's very complex and complicated. My goal: select those test executions that have certain tags but exclude those with some other, kind like a +tag1 +tag2 -mustNotBePresentTag. I have Predicate with the desired tags and the exact query that you linked concatenated with builder.and(). Do you see any mistake? — jholusa, Apr 29 '14 at 06:25

score 2 · Accepted Answer · edited May 23 '17 at 10:32

I finally got the answer but thanks a lot to @Priyesh comment and this link: "Not in" constraint using JPA criteria

After edit I realized that it the goal is little different (see the edited post).

So Subquery had to be used, see: JPQL, How to NOT select something

Just for anybody, here is the Criteria API Query that worked for me, but it's basically just rewritten JPQL query above.

Predicate wantedToBePresentTags = cb.lower(rTag.<String>get("name")).in(cb.parameter(List.class, "tagList"));

Subquery sq = criteriaQuery.subquery(TestExecution.class);
Root sqRoot = sq.from(TestExecution.class);
Join<TestExecution, Tag> sqTag = sqRoot.joinCollection("testExecutionTags").join("tag");
sq.select(sqRoot.get("id"));
sq.where(cb.lower(sqTag.<String>get("name")).in(cb.parameter(List.class, "excludedTagList")));

Predicate excludedTags = cb.not(rExec.get("id").in(sq));

...
criteriaQuery.where(cb.and(wantedToBePresentTags, excludedTags));

Hope that this helps somebody! :-)

JPA Criteria API - how to select like "field not in"

1 Answers1

Linked