Hibernate Community • View topic

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

HQL Join Question

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

vermule

Post subject: HQL Join Question

Posted: Mon Mar 02, 2009 3:16 pm

Newbie

Joined: Mon Mar 02, 2009 3:06 pm
Posts: 5

I am trying to do a left outer join between two tables. I keep getting the below exception when trying to run it. I did find this posting http://forum.hibernate.org/viewtopic.php?p=2187152&sid=dd72de4c41b85bf79e93258e98b2bb49, but that is from 2004 and I would think that something has changed by now. Can somebody shed some light and let me know if this is still the case.

I am using Hibernate 3 and running an Oracle 9i database.

Exception:

Code:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 4, column 1 [select pi.code, pi.name, pi.address, pi.phone, 
   pi.loc, pi.currency, pp.general
from com.property.PropertyInfo pi left outer join PropertyProfile pp
on (pi.code = pp.code)
where pi.code = rpad(upper(:code),8)]

The original query looked like this:

Code:

select pi.code, pi.name, pi.address, pi.phone, 
   pi.loc, pi.currency, pp.general
from com.property.PropertyInfo pi, PropertyProfile pp
where pi.code = rpad(upper(:code),8)]
and pi.code = pp.code (+)

Top

pkleindl

Post subject:

Posted: Mon Mar 02, 2009 6:31 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

just write

Code:

from com.property.PropertyInfo pi left outer join pi.propertyProfile pp

without the on clause or where ... +

PropertyProfile just has to be mapped from PropertyInfo, as many-to-one or something like that

rating is welcome

Top

vermule

Post subject:

Posted: Tue Mar 03, 2009 11:28 am

Newbie

Joined: Mon Mar 02, 2009 3:06 pm
Posts: 5

Is there a way to do the join without mappings? Right now we do not support mappings as a standard internally.

Top

drlaz

Post subject: Did you do addSqlFunction on rpad?

Posted: Tue Mar 03, 2009 3:35 pm

Newbie

Joined: Tue Mar 03, 2009 2:58 pm
Posts: 10

I'm not sure it can be dropped into the WHERE clause without that.

Top

nordborg

Post subject:

Posted: Tue Mar 03, 2009 5:19 pm

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

If you want to do a 'left join' in HQL you'll need the mapping.

The only workaround I can think of is to subclass the Oracle dialect and register a custom SQL function that can translate a condition to a theta-style join. Eg.

Code:

select pi.code, ....
from com.property.PropertyInfo pi, PropertyProfile pp
where pi.code = rpad(upper(:code),8)]
and thetaleftjoin(pi.code, pp.code)

where the thetaleftjoin() function can be registered with the following code in the new dialect's constructor:

Code:

registerFunction("thetaleftjoin", new SQLFunctionTemplate( Hibernate.BOOLEAN, "?1 = ?2 (+)"));

It will of course only work with databases that support theta-style left joins.

Top

Page 1 of 1

[ 5 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum