JPA criteria subquery with common customerId field

Question

How do I write the following MySQL query using JPA Criteria?

select * from deptors where customerId in 
   (select customerId from address where zipcode="12345-6789")
and gender="male";

I have read http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Criteria#Subquery and many stackoverflow threads including JPA 2.0, Criteria API, Subqueries, In Expressions. But I am just not getting it. Thanks for helping.

For the `in` usage, also have a look at this answer: http://stackoverflow.com/a/9325205/870122 — perissf, Oct 09 '12 at 08:30

score 0 · Answer 1 · answered Oct 09 '12 at 07:40

Something like:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Deptors> cq = cb.createQuery(Deptors.class);
Root<Deptors> c = cq.from(Deptors.class);

Subquery<Address> subquery = cq.subquery(Address.class);
Root<Address> a = subquery.from(Address.class);

However I would rather recommend building this query with JPQL and/or externalize it as a NamedQuery into your Entity.

Sebastian

JPA criteria subquery with common customerId field

1 Answers1