Reality-Loop

Learned Today: Joining with Hibernate

June 15, 2010 | 1 Minute Read
This post was originally posted on my old blog.

Hibernate (and NHibernate) is able to do a lot of different kind of joins.
Unfortunately it can not do outer joins on properties that are not mapped as associations.

My goal was to get the result of the following SQL-query with an outer join:
select e.Name, d.Status, count(*) from event e
left outer join delivery d on e.Id = d.EventId
group by e.Name, d.Status

I hoped to realize the above query with Hibernate in HQL. Actually you can do joins between entities where no association is mapped:

select new ReportDto(e.Name, d.Status, count(d))
from Delivery d, StatisticalEvent e where d.EventId = e.Id
group by d.EventId, e.Name, d.Status
... unfortunately this is an inner join.

You can do outer joins with Hibernate:
select new ReportDto(e.Name, d.Status, count(d))
from Delivery d right outer join d.Event e
group by e.Name, d.Status
... unfortunately you have to have mapped the association between the two entities, since you have to feed a path to the join expression.

(Note that I do not claim this is a proper usage scenario for Hibernate. Reporting queries like this are certainly an edge case for using Hibernate.)

© 2020 Jonas Bandi / design based on long-haul / powered by Jekyll