JPA Criteria API "IN" Predicate not working

Question

I need to write the following with JPA Criteria API:

p.id IS NULL  AND   nv.organizationalstat IN ('EMPLOYEE', 'FELLOW')`

My code is:

 List<String> corePredicateInList = Arrays.asList("EMPLOYEE", "FELLOW");
 In<String> corePredicateIn = cb.in(nvRoot.get("organizationalstat"));
 corePredicateInList.forEach(p -> corePredicateIn.value(p)); // Setting opts into IN
 Predicate p1CorePredicate = cb.and( 
                                cb.isNull(plans.get("id")), 
                                cb.in(nvRoot.get("organizationalstat"), corePredicateIn)
                            );

The runtime error is

antlr.NoViableAltException: unexpected token: in
...
where ( ( ( ( ( ( generatedAlias0.id is null ) 
  and ( generatedAlias1.organizationalstat in (:param0, :param1) in () ) ) 
  or ( generatedAlias0.id is not null ) )

It looks like there's a double IN somewhere. There are no syntax errors in the code.

I can't do cb.in(List<String>) directly, that's wrong syntax. I have to go through in.value() as indicated in this thread.

Eklavya · Accepted Answer · 2020-06-26T18:38:31.803

1

Using CriteriaBuilder.In

Predicate p1CorePredicate = cb.and(cb.isNull(plans.get("id")),corePredicateIn);

Or using Expression.In

Predicate p1CorePredicate = cb.and(cb.isNull(plans.get("id")), 
                         nvRoot.get("organizationalstat").in(corePredicateInList));

edited Jun 26 '20 at 18:38

answered Jun 26 '20 at 18:18

Eklavya

17,618
4
28
57

Both options working. I prefer #2 because it lets me use my `List` directly, without any other expressions. So I ended up using `root.get("..").in(stringList)`. – gene b. Jun 26 '20 at 19:17
I totally agree with you, I added first one for showing how to solve you current problem. And just let you know for in query using subquery result `CriteriaBuilder.In` must need. – Eklavya Jun 26 '20 at 19:22

score 1 · Answer 2 · edited Jun 26 '20 at 18:54

1

A solution that worked for me is to do root.in(List<String>) (not cb.in), as follows:

 List<String> corePredicateInList = Arrays.asList("EMPLOYEE", "FELLOW");
 Predicate p1CorePredicate = cb.and( 
                                cb.isNull(plans.get("id")), 
                                nvRoot.get("organizationalstat").in(corePredicateInList)
                            );

edited Jun 26 '20 at 18:54

Kmarlon Pereira

59
2
10

answered Jun 26 '20 at 18:25

gene b.

10,512
21
115
227

JPA Criteria API "IN" Predicate not working

2 Answers2