JPA criteria API translates the 'in' expression to multiple 'OR'

Question

JPA criteria API with 'in' expression translates to multiple 'OR' instead of 'in'

e.g.

My primary model

public class Person {
    ...

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    private Department department;

    ...
}

many to one relationship

public class Department {
    @Id
    @Column(name="department_id")
    private Integer departmentId;

    @OneToMany(mappedBy="department")
    private List<Person> person;

    ...
}

defined these two models in persistence.xml (Note: It doesn't specify any db dialect)

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
    version="2.1">

    <persistence-unit name="primary" transaction-type="JTA">

        <jta-data-source>java:openejb/Resource/jdbc/myDS</jta-data-source>

        <class>com.xyz.Person</class>
        <class>com.xyz.Department</class>
        ...

        <shared-cache-mode>ENABLE_SELECTIVE</shared-cache-mode>

        <properties>
            <property name="openjpa.Log" value="${open.jpa.log}" />
            <property name="openjpa.ConnectionFactoryProperties" value="printParameters=true" />
            <property name="openjpa.RuntimeUnenhancedClasses" value="unsupported" />
        </properties>
    </persistence-unit>

</persistence>

Following is the code with Criteria API to build the query:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Person> criteriaQuery = cb.createQuery(Person.class);
Root<Person> personRoot = criteriaQuery.from(Person.class);

criteriaQuery.select(personRoot);

List<Predicate> predicateList = new ArrayList<>();
predicateList.add(personRoot.get(Person_.department).get(Department_.departmentId).in(Arrays.asList(1, 2, 3)));
// Using list because I actually need to add multiple conditions

criteriaQuery.where(predicateList.toArray(new Predicate[0]));

TypedQuery<Person> searchQuery = em.createQuery(criteriaQuery);

searchQuery.getResultList();

This prints the query in the following format:

SELECT t0.person_id, ... 
FROM person t0 
WHERE ((t0.department_id = ? OR t0.department_id = ? OR t0.department_id = ?) AND t0.department_id IS NOT NULL)

but what I supposed get is

SELECT t0.person_id, ... 
FROM person t0 
WHERE t0.department_id IN (?, ?, ?)

Env:

Tomee 7.0.3
Embedded OpenJPA
Mariadb

Update:

I have also tried to add mariadb dialect as follows, but it doesn't help

<property name="openjpa.jdbc.DBDictionary" value="mariadb" />

maybe backend (dialect) has not 'in' ???. What real database is used? — Jacek Cz, Aug 17 '17 at 11:49
Try passing a `java.sql.Array` to the `in` - with SQL `"... in(?)"`. — Joop Eggen, Aug 17 '17 at 12:24
Try specifying the "(1,2,3)" as a PARAMETER, and provide a List as the parameter value at runtime. — Neil Stockton, Aug 17 '17 at 12:42
.in accepts either Object or Collection... Predicate in(Object... values); or Predicate in(Collection> values); — yottabrain, Aug 17 '17 at 13:09
`.in` accepts `Expression`. A `ParameterExpression` is an `Expression`. — Neil Stockton, Aug 17 '17 at 13:21
In SQL an expression: `x IN (1, 2, 3)` is an equivalent of `x=1 or x=2 or x=3`, it gives exactly the same result. Why do you worry about it ? I personally wouln`t care of it. — krokodilko, Aug 17 '17 at 16:30
yes functionally both are same, however there is some performance differences. https://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause — yottabrain, Aug 18 '17 at 05:51
I think you need to use Predicate parentPredicate = parentExpression.in(predicateList); Then apply the where clause and last but not least something to order or group by, else you will have a ton of data. I can provide you a fully implemented example if you need but i think with the first line of code you can handle it — Augusto, Aug 24 '17 at 19:37

score 1 · Answer 1 · answered Oct 10 '17 at 18:51

The solution would be to use expression for example :

List<Long> departmentIdsList = new ArrayList();
departmentIdsList.add(1L);
departmentIdsList.add(2L);
departmentIdsList.add(3L);
Expression<Long> exp = personRoot.get("departmentId");//"departmentId" field name to be equated
predicateList.add(exp.in(departmentIdsList));

the above snippet should evaluate as in clause

JPA criteria API translates the 'in' expression to multiple 'OR'

1 Answers1