[FIXED] Java Predicates In clause: illegal argument exception?

Issue

I am trying to create a java predicate that gets all entities that match a certain Id:

 public Predicate createPredicate(List<Integer> personIds){
 
 Predicate newPredicate = null;
 if(!personIds.isEmpty()){
      CriteriaBuilder.In<Integer> inClause = builder.in(from.get(PERSON_ID));
      for (Integer personId : personIds) {
        inClause.value(personId);
      }
      newPredicate = builder.in(inClause);
    }

    return newPredicate;
  }

However when I try to get the count results from this (see below) I get an illegal argument exception.

What is the issue?

private Long getResultsCount(Predicate predicate) throws ATSException {

    CriteriaBuilder countBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> countQuery = countBuilder.createQuery(Long.class);
    Root<Person> countFrom = countQuery.from(Person.class);
    countQuery = countQuery.select(countBuilder.count(countFrom));
   
    countQuery.where(predicate);

    return entityManager.createQuery(countQuery).getSingleResult();
  }

Error:

   java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: in near line 1, column 177 [select count(generatedAlias0) from com.model.Person as generatedAlias0 where ( generatedAlias0.status=:param0 ) and ( ( generatedAlias0.personId in () in () ) and ( ( generatedAlias0.personId in () in () ) and ( generatedAlias0.personId in () in () ) ) )]\r\n\tat org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1374)\r\n\tat 

Solution

The generated query contains the following 3 times:

( generatedAlias0.personId in () in () )

My guess is that this is the reason:

newPredicate = builder.in(inClause);

inClause is already translated to "in (...)", so you're asking the criteria builder to include an IN inside an IN.

Does your code work if you remove that line?

And it's probably easier to use the Expression.in method. Since from.get(PERSON_ID) returns an Expression, this should work:

public Predicate createPredicate(List<Integer> personIds){
    return personIds.isEmpty()
            ? null
            : from.get(PERSON_ID).in(personIds);
}

Answered By - Rob Spoor
Answer Checked By - Marie Seifert (JavaFixing Admin)