Can anybody please tell me the syntax for writing joins(both inner and outer) in Hibernate query language and also highlight its difference with Oracle joins? I want to join multiple tables.

Thanks & Regards,
Raul

You can usually create joins by using the foreign key relations or lazy collections like:
from TableA ta inner join ta.ForegnKeyToTableB tb where tb.Guid = ta.Guid and tb.Name = 'hello'

assuming you have properly defined your mappings it means that table a has foreign key to table b...If you have lazy collections defined in the class b (table b) like "TableAList" than you can write it like other way around:

from TableB tb inner join tb.TableAList ta where ta.Guid = tb.Guid

You can go further and join the third table through the table b:

from TableA ta
inner join ta.ForegnKeyToTableB tb where tb.Id = ....
inner join tb.ForeignKeyToTableC tc where tb.Guid = tc.Guid

or if only need table a and c:
from TableA ta
inner join ta.ForegnKeyToTableB.ForeignKeyToTableC tc where tc.Guid = ta.Guid

and so on...it is completely object oriented,,,

cheers
markov

Is there any way to do an HQL (inner or left) join across classes that are not directly associated through the hibernate mapping file?

In SQL I can do the following inner join:



Is there any way to do the same in HQL without specifying "<one-to-many>" or "<many-to-one>" associations in the mapping files?

Hi,

Thanks for the reply.
Can you plz illustrate an outer join also.

Thankx,
Raul

i see no reason not to define associations if your tables are related with the foreign keys. agree?

for the outer join you may use: left join and right join (i am not sure if you need to put "fetch" at the end). the best way is to experiment with the simple tables and queries to see the results...

Hi all,
I have the same problem, I need to run an HQL query between two POJOs and they don't have any kind of relationship. This query is only for report purpose, so I think that I mustn't add a relationship between these classes.
In fact, the query is trying to compare values from the same POJO but in different states, i.e., two Audit class with differents dates.

I need to do something like this:


select pa2.score1 - pa.score1,
pa2.score2 - pa.score2,
pa2.score3 - pa.score3
from Audit as pa left outer join Audit as pa2
where pa.index.id=1008
and pa2.index.id=-1
and pa.page.id=pa2.page.id

In SQL is easy to do quieries like that, but in HQL, can I do this?

Anybody could help with this issue?

thanks!

+1

My understanding is that essentially HQL is for querying objects, so unless the relationship is defined at an object (and mapping) level you can't explore the relationships between objects in HQL.

You may be able to do what you're trying to do using a subquery if your RDBMS supports it. I could be wrong of course...

Cheers,

Symon.

thanks for your response, but maybe there's a misunderstanding:
[quote]unless the relationship is defined at an object (and mapping) level you can't explore the relationships between objects in HQL[/quote]
You can create a HQL query and joining two mapped objects that doesn't have any relationship between them. In these case I'm not trying to browse the association hierarchy, I just want to get the information that I need using HQL.
The problem is that I wasn't able to do an outer join between two non-associated objects, I had no problems with inner joins in HQL.

Thanks much.
Cheers.

Apologies, I didn't read the post carefully enough. :)

Cheers,

Symon.