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" />

Answer 1

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