Hi,

I have check the doc and I dont see any way to do this simple HQL. I have 2 table and I want to do a join between then based on 2 keys. This seems quite trivial, I and dont see anyway to do this:




This HQL statement should sound like that :

"I want all cashMovement and all rrdfLifDetail where rrifLifDetail.cashMvtRequestUid = cashMovement.requestUid. And because it is an Left Outer join, give me a NULL rrifLifDetail if you find no rrifLifDetail according to rrifLifDetail.cashMvtRequestUid = cashMovement.requestUid."

How can I write this Statement? Must I use Native SQL to do this?

Thanks.

Etienne.

Resulting HQL may be very different depend on your mapping. For instance, is these two entity have a link in mapping or not?

Not absolutely sure about this, but you should specify what 'rrifLifDetail' means in the objects relation...

It should work with something like:

select cashMovement.something, rrifLifDetail.something

from CashMovement cashMovement

left outer join cashMovement.rrifLifDetail rrifLifDetail

where rrifLifDetail.cashMvtRequestUid = cashMovement.requestUid


If you are about to retrieve entire objects, you should forget about select, something like this:

from CashMovement cashMovement

left outer join cashMovement.rrifLifDetail rrifLifDetail

where rrifLifDetail.cashMvtRequestUid = cashMovement.requestUid

Hope it helps.
Bye.

Thanks both,

I didn't linked in the xml mapping CashMovement to RrifLifDetail (sorry for that name) in a one-to-many relationship. I could do this, but it would not help me in the future. I need to be able to do some HQL join (and left join) between unlinked tables. Per exemple, I work with 2 databases (same database server) with 2 differents domain models (for each database), and linking everything between the 2 domain model would be too verbose.

To explain a bit the relationship this is how the 2 table are linked:
cashMovement.requestUid is the primary key of cashMovement and
rrifLifDetail.cashMvtRequestUid is a foreign key pointing to cashMovement.requestUid.

The orginal SQL look like this (the statement was simplified, removing the where clause, other left join and the order by...) :






Thanks for helping!

Etienne.

Hibernate doesn't support outer joins on dynamic associations (not presented in mapping). The only option - native queries.



Quite the contrary, actially. Hibernate can't do many important things without presence of this information.

To be able to accomplish left outer join between 2 table, does anyone know if it is possible to include the links inside the mapping without adding the propreties in the pojo? I work with a very big and complexe legacy system on AS400/DB2, I would prefere to minimize the size of the pojos by not adding all relations (class one-to-one, set one-to-many, class many-to-one) in the pojo.

Look at Hibernate 3.1 - it is introduced new 'with' keyword:

Thanks a lot! This will help me!

I'll work on that.

Regards,

Etienne.

(no more credit to give you, but thanks anyway!)