CODEWITHSUNDEEP
javajpajpa-2.0
forhas
forhas

Reputation: 11991

JPA left/right join query

I have this structure from entities Aa, Bb, Cc:

-

public class Aa{
@OneToMany   
List<Bb> listBb;
}

public class Bb{
@OneToMany 
List<Bb> listCc;
}

I would like to create a JPA Criteria API query to pool Aa by an id of C:

public A getAaByCcId(long id) {...}

In native sql I would have try left join (twice). How do I do this using JPA?

Upvotes: 1

Views: 1794

Answers (2)

Tom Anderson
Tom Anderson

Reputation: 47173

Assuming that each entity has a reference to its parent entity, where Cc's reference to a Bb is called bb and Bb's reference to an Aa is called aa, then in JPQL, you can do:

select cc.bb.aa from Cc cc where cc.id = ?

The criteria version of this should be a simple translation.

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691675

You also do it with joins in JPQL:

select a from Aa a
inner join a.listBb b
inner join b.listCc c
where c.id = :cId

Note that inner joins can be used here, since you have a restriction on c.id = :cId, which can only be true if B and C exist. But you could use left joins as well.

EDIT:

Using a Criteria query, it would look like the following (not tested):

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Aa> criteria = builder.createQuery(Aa.class);
Root<Aa> a = criteria.from(Aa.class);
CollectionJoin<Aa, Bb> b = a.join(Aa_.listBb);
CollectionJoin<Bb, Cc> c = b.join(Bb_.listCc);
criteria.where(builder.equal(c.get(Cc_.id), cId));
return em.createQuery(criteria).getResultList();

Upvotes: 2

Related Questions