How to use JPA query to retrieve parent and last child entity

Question

I have 2 tables say Parent and Child and they are one-to-many relationships.

In the entity classes, the Parent class has a list of Child objects.

So if using JPA query, how can I write a query to retrieve all Parent(s) and their last child?

There may be a case that a Parent does not have a child.

If using sql, it would be like:

select * from parent p 
  left outer join child c on c.parent_id = p.id 
where c.child_id is null 
   or c.child_id = ( 
select child_id from ( 
select child_id  
  from child d 
 where d.parent_id = p.id order by child_age DESC, child_id DESC) 
where rownum<=1)

so if that works why don't you try putting that in a native query? `Query q = em.createNativeQuery (....);` — Scary Wombat, Jun 29 '18 at 02:38
Why you are writing manual query? Why not criteriabuilfer. Are you using spring jpa? — Gaurav Srivastav, Jun 29 '18 at 02:40
Because my corporation just does not allow developers to write native SQL. — Dicky Ho, Jun 29 '18 at 02:43

Gaurav Srivastav · Answer 1 · 2018-06-29T04:44:10.597

You can use CriteriaBuilder obtain from EntityManager, then obtain CriteriaQuery from it. I am assuming Parent Entity here as Teacher and Child entity as Phone. Below code is to fetch all the phones numbers by teacher firstName.

  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Phone> query = cb.createQuery(Phone.class);
  Root<Teacher> teacher = query.from(Teacher.class);
  Join<Teacher, Phone> phones = teacher.join("phones",JoinType.LEFT);
  query.select(phones).where(cb.equal(teacher.get("firstName"), "prasad"));

List<Phone> results = em.createQuery(query).getResultList();
for (Phone phone : results) {
    System.out.println("Number = " + phone.getNumber() + " "
        + "Type = " + phone.getType());
}

this clearly won't work, a left join is needed in cases where there is none in the other side 1....[0-*] — FiruzzZ, Jun 29 '18 at 03:40

score -1 · Answer 2 · answered Jun 29 '18 at 03:37

-1

solving this in one JPQL (or CriteriaQuery) would be very ugly, retrieving 2 objects instances like

SELECT p, lastChild FROM Parent p LEFT JOIN p.children c

u can't either subquery in select

SELECT p, (SELECT c FROM Child c where c.parent = p ORDER BY c.age DESC) FROM Parent p

the simplest solution would be: get all the parent, then look for the older child

answered Jun 29 '18 at 03:37

FiruzzZ

661
1
6
21

I would like to see your final resolution since you qualified my answer as "not useful" – FiruzzZ Jun 29 '18 at 10:55

How to use JPA query to retrieve parent and last child entity

2 Answers2