I am trying to convert a SQL statement to HQL and I have been having some problems. I have read the online docs but I can't quite figure it out.

Here is my SQL query

SELECT a.*,b.*
FROM schema.table1 a RIGHT OUTER JOIN schmea.table2 b
ON ((a.col1 = b.col1 ) AND ( a.col2 = b.col2 ) )
WHERE b.col3 = 'blah'
ORDER BY a.col1 ASC,
a.col2 ASC

any idea on how to do this in HQL.

Thanks for the help.

Hibernate version:
3.0.5

FROM schema.table1 a RIGHT OUTER JOIN schmea.table1.table2 b
WHERE b.col3 = 'blah'
ORDER BY a.col1 ASC,
a.col2 ASC

thanks but schema.table1 does not contain an association to table2. I need to do the join in HQL only. There is nothing in my mapping file(and I can't add anything there either) to associate table2 to table1. I think I need to be able to specify the SQL on criteria in some HQL notation

When two tables does not have any relationship between them and you still need to query the data.

You may consider using Theta-Style joins.

For example

String sqlString ="from table1 t1, table2 t2 where t1.col1=t2.col";

Iterator i = session.createQuery(sqlString).list.iterator();

while(i.hasNext())
{
Object[] pair = (Object[]) i.next();
Table1 table1 = (Table1) pair[0];
Table2 table2 = (Table2) pair[1];
}

Hope it helps you.

I have been able to get that to work but I really need the outer join. I'm starting to get the feeling this may not be possible to do in Hibernate. I guess I need to start looking at a way to pass a SQL query to hibernate and have it map the resultset to my domain objects.

Common David, The posting that i have made works.
I had implemented in my project.

YOU SHOULD TRY IT, BEFORE YOU GIVE UP.

mahikty, I believe it works. I have several of those in my code already. The thing is it is not an outer join it is an inner join. The outer join states that all rows from either the left or the right will be returned and if a matching row meeting the join criteria is in the other table it will be selected. If the criteria is not matched it will be null.

Oh ok, I know about outer-joins.
I overlooked the word outer-join in your post.
Any way good luck.

If i find anything about it, I will post it.

hi david,

which hibernate version you are using ? can u do a bulk update also ?

thanks n rgds
vikram

I'm using 3.0.5

yes I can do bulk updates.

I have looked through the "Hibernate In Action" book and on page 268 it states that outer-joining two tables that don't have a mapped association is not possible.

The book is mostly talking about Hibernate 2.1 so I hope it was added in Hibernate 3. If not I hope I can write the join as a sql query and at least have hibernate map the result set to my domain objects.

hi david ,
thanks for the promt and informative response ..

If you have time I would like you to ponder on an issue i am facing..

I was using hibernate 3.0.1, Oracle 10g and was implementing the joins with (+) notation ... but there was no provision of bulk update/delete in hibernate 3.0.1 .... and also the parser was 'org.hibernate.hql.classic.ClassicQueryTranslatorFactory' as mentioned in the hibernate docs

To use bulk update/delete, I did an upgrade of hibernate to 3.0.5 .. and the new parser 'org.hibernate.hql.ast.ASTQueryTranslatorFactory' ... now i was able to do bulk update/delete but not the outer join functionality using (+) notation ...... i also tried removing (+) notation and using
select c1 from className1 c1 left join className2 c2 on c1.id = c2.id ...... it gave error saying that "ON" is not recognised....

thanks
vikram

Hi,

Can someone from the Hibernate team please confirm that it is still not possible even in v3.0 to outer-join two tables that don't have a mapped association.

Or if it does, can someone provide an example.

Thanks, Rishabh

while this is an inner join, you could try:


which gives you left join or:


which gives you right join

in hibernate 3.1 you can only add additional filtering condition to a join:
from Table1 t1 join t1.t2elements t2 with ( <additional condition )

still you cannot left/right join two tables that are not associated by a mapping (I'm also lacking this functionality a lot!). There was an issue open about this in JIRA. I do not know the current status of this request.