Grouping in hibernate - stupid question

Question

Hi all,

I'm in process of migrating an old "classic ASP" application to J2EE/Hibernate. I have a problem with the following type of SQL statements:

SELECT parent.id, parent.name, parent.column1, count(child.id) AS no_of_children
FROM parent
INNER JOIN child ON child.parent_id = parent.id
GROUP BY parent.id, parent.name, parent.column1

How do I express something like this in HQL? I tried to map the children as a collection (by using many-to-one) and get the number of children from the collection size, but then Hibernate has to load all "child" entities, for each parent separately.

This results in executing around 1000 DB queries instead of 1, with all resulting performance problems.

Is there any way around this?

Answer 1

Translation to HQL

FROM Parent GROUP BY Parent.id, Parent.name, Parent.column1

where Parent is an Entity previously modeled. The join clause is defined in the model.

As for getting the number of children, for each parent separately, try to remove any Lob fields in the Child model as they will bottleneck your application speed.

Hope to have helped

Answer 2

I would try something like the following:

select parent.id, parent.name, parent.column1, count(child) 
from Parent parent 
join parent.Children as child 
group by parent.id, parent.name, parent.column1