How to convert a JPQL with subquery to Criteria AP

Question

Have a simple object model made up of 5 entities:

1. Company
2. Organization
3. Address
4. Club
5. Group

A Company is associated with a single Organization. (Group and Club are also associated with a single Organization - these are unidirectional, meaning the Organization does not contain a reference to its owner). An Organization may have 0 or more Address(es).

A subquery can be used to access Company objects based on a specific zipcode, which is an attribute of an Address. Here is a JPQL query that can access those companies with a specific zipcode.

@Query("select p from Company p, Organization org where (p.organization = org.id) and exists ( select 1 from Address ad where zipcode = :zipcode and ad.organization = org.id)")

How can the same thing be done using the JPA Criteria API?

Answer 1

This will select companies with provided zipcode using inner join, is this what you wanted?

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Company> cq = cb.createQuery(Company.class);
Root<Organization> root = cq.from(Organization.class);
Join<Organization, Address> adr = root.join("addresses");
cq.select(root.get("company"));
cq.where(cb.equal(adr.get("zipcode"), zipcode));

You won't be able to access Company via Organization, if it doesn't have a property for it. If your Company have a reference to Organization, then you can make unidirectional mapping to bidirectional mapping adding following code to Organization class:

@OneToOne(mappedBy="organization") //Providing the name of property in Company 
private Company company;