Hibernate HQL: Converting a SQL Statement to HQL (with subselects/joins)

Question

Hello Hibernate Experts,

i would very much appreciate your help in transforming the following SQL statement to a valid HQL Statement. I tried for hours but was not successfull:

SELECT * FROM master as m left outer join (select * from child as c where c.id = (select max(d.id) from child as d where d.MasterFk = c.MasterFk) )as b ON m.id = b.MasterFk;


public class Master {
 private Long id;
 private Collection childs;
 ...
}

public Class Child {
  private Long id;
  private Master master;
}

In the Hibernate Mapping File I map the Master to the Child via a Standard SET (-Mapping) and the relation back from Child to Master is a Many-To-One Association. (This is working.)

==> The objective is to Query the Master with only the latest Child Record (=Only ONE Record!) correctly initialized as the only element in the SET of childs...

Whatever I tried, I failed miserably.

Thank you very much!

Answer 1

Why convert - use the native SQL and the Hibernate Transformers.

Answer 2

I'm not sure you can do that. The problem I see is not the query, but the initialization. If you don't return all the objects for the Set, then I'm not sure Hibernate can initialize the Set.

In Hibernate logic, if an object (here, the Set) is provided (not null, no proxy) in the object graph, then it is supposed to be complete. A Set containing only one element, and thus unable to load the others as it isn't a proxy, is out of Hibernate logic. In Hibernate logic, a Set is either null, a proxy, or fully loaded (except advanced cases for huge collections, where they are loaded by batches...).

I understand your need though. Beware, you need to make sure that the codes that will receive this object graph will all understand that the Set is not really one!

I suggest two alternatives:

1. Create the object in Java

An alternative is to query for the data you want, then fill in your objects in your specific Java code.

The query could be something much simpler, like:

    SELECT m.id, max(c.id) 
    FROM master as m 
    left join m.childs as c 
    group by m.id

The java code would instanciate what you want from that List<Object[]> result.

Or, you could give Master an appropriate constructor, and use new (returning List<Master>):

    SELECT new Master(m.id, max(c.id)) 
    FROM master as m 
    left join m.childs as c 
    group by m.id

2. Add a member

You could also have a member latestChild on the Master. The mapping could be specified to load it exactly as you need.

I suggest a formula, which has the advantage to be read-only. Beware that formulas take SQL, not HQL. Insert the SQL that correspond to your subrequest.