How to use joins between unrelated entities

turbulence · **Joined:** Thu Oct 21, 2010 5:15 am **Posts:** 8

I have a case where there is ParentEntity and a ChildEntity. There is no inheritance defined between both child & parent, though the existence of child totally depends on parent. And parent can exist without child even. And also there is no containment of each other in each entity even.

In this case I wrote a hql named-query like:

select new BusinessDTO(parent.att1, parent.att2, child.attr1, child.attr2) from ParentEntity parent, ChildEntity child where parent.id=? and child.id = parent.id

but this query didn't solved my purpose, my business scenario wanted all above defined parentInfo as well as optional childInfo in the case there is an existing child.

Somehow I tried to use hibernate outer or any type of joins but it failed for me. I can't change my entities all I can do is to write a named-query which can work. Can anyone suggest a solution to this?

overmeulen · **Joined:** Fri Jan 28, 2011 11:44 am **Posts:** 117

Hi,

If the 2 entities are not linked through any kind of mapping then you cannot join them ...
All you can do is a cross join which is exactly what you did in the HQL query you showed below.

turbulence · **Joined:** Thu Oct 21, 2010 5:15 am **Posts:** 8

The only relationship is ChildEntity.id is foreign key to ParentEntity.id, rest there is no other relationship defined in Entity. So overmeulen you say that this is not possible through HQL.

overmeulen · **Joined:** Fri Jan 28, 2011 11:44 am **Posts:** 117

If you want to use a join in an HQL query you need to specify a OneToOne (or OneToMany ...) relationship between ParentEntity and ChildEntity ...

mmerder · **Posted:** Thu Mar 10, 2011 6:49 am

Actually if child.id is foreign key to parent.id it is a One-To-One-Relationship. Why don't you define it in your mapping?

turbulence · **Joined:** Thu Oct 21, 2010 5:15 am **Posts:** 8

Well, we have mappings defined as annotations on top of entities, and as I said we are in this phase of our project that we cannot change the entity. This is the reason why problem anyway rose.

mmerder · **Posted:** Thu Mar 10, 2011 12:02 pm

then you have to do a cross join as you already did.

turbulence · **Joined:** Thu Oct 21, 2010 5:15 am **Posts:** 8

Well for time now to have outer join similar solution I've changed it to:

select new BusinessDTO(parent.att1,
parent.att2,
(select child.attr1 from ChildEntity child where child.id = parent.id),
(select child.attr2 from ChildEntity child where child.id = parent.id))
from ParentEntity parent
where parent.id=?

This gives me all the results irrespective child exists or not. The only downside I see is that I've to query the ChildEntity twice to get 2 fields of it.

mmerder · **Posted:** Mon Mar 14, 2011 3:54 pm

Why not

Code:

select new BusinessDTO(parent.att1, parent.att2, child.attr1, child.attr2) from ParentEntity parent, ChildEntity child where parent.id=? and (child.id = parent.id or child.id is null)