CODEWITHSUNDEEP
javajpaopenjpa
yottabrain
yottabrain

Reputation: 2615

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

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:

Update:

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

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

Upvotes: 10

Views: 1409

Answers (1)

Ejaz Ahmed
Ejaz Ahmed

Reputation: 339

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

Upvotes: 1

Related Questions