Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA Criteria API on ManyToOne optional relationship

I'm trying to do a simple query using JPA Criteria API on following structure

1) Employee

    public class Employee {

        @Id
        @Column(name = "ID", length = 64)
        private String id;

        @Column(name = "NAME", length = 512)
        private String name;

        @ManyToOne(optional = true)
        @JoinColumn(name = "ORG_ID", nullable = true)
        private InternalOrg organization;

    }

2) InternalOrg

    public class InternalOrg {

        @Id
        @Column(name = "ID", length = 64)
        private String id;

        @Column(name = "ORGANIZATION", length = 512)
        private String organization;

        @Column(name = "CODE", length = 64)
        private String code;

    }

3) Query

    EntityManager em = getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
    Root<Employee> emp = cq.from(Employee.class);

    cq.where(cb.or(emp.get(Employee_.organization).isNull(),
            cb.equal(emp.get(Employee_.organization).get(InternalOrg_.code), "1")));

    return em.createQuery(cq).getResultList();

As you can see "organization" attribute on Employee is optional. What I'm trying to do is a query using criteria API that returns all records where "employee.organization" is NULL or "employee.organization.code" is equal to a parameter. How do I proceed?

I did some tests and realized that if I change from this:

    cq.where(cb.or(emp.get(Employee_.organization).isNull(),
            cb.equal(emp.get(Employee_.organization).get(InternalOrg_.code), "1")));

To this:

 cq.where(cb.or(emp.get(Employee_.organization).isNull()));

It works but only returns records where organization is NULL.

If I change to this:

cq.where(cb.equal(emp.get(Employee_.organization).get(InternalOrg_.code), "1"));

Records where employee.organization is NULL are ignored.

How do I return employees which organization satisfies criteria AND employees where organization IS NULL?

Thanks in advance,

like image 962
Emilio Numazaki Avatar asked Aug 24 '17 03:08

Emilio Numazaki


1 Answers

finally found the solution.

The only way to create get desired result is to fetch (JoinType.LEFT) relationship earlier, here is the final criteria query:

    EntityManager em = getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
    Root<Employee> emp = cq.from(Employee.class);

    emp.fetch(Employee_.domain, JoinType.LEFT);

    cq.where(cb.or(emp.get(Employee_.organization).isNull(),
            cb.equal(emp.get(Employee_.organization).get(InternalOrg_.code), "1")));

    return em.createQuery(cq).getResultList();

Thank you for support!

like image 173
Emilio Numazaki Avatar answered Nov 07 '22 23:11

Emilio Numazaki