Dear All,
Iam unable to implement left outer join on specific condition using
hibernate,
For eg: Select new TestVO(tvo.name,tvo.custID) from com.xyz.TestVO as
tvo left outer join com.xyz.Test2VO as tvo2 on
(tvo.custID=tvo2.custGrpID) where tvo.shortName like 'qw%'

The error as follows:


12:00:58,562 ERROR [STDERR] net.sf.hibernate.QueryException: outer or
full join must be followed by path expression [select ne
.customerName)]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:166)
at net.sf.hibernate.hql.ClauseParser.token
(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token
(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse
(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile
(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile
(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery
(SessionFactoryImpl.java:293)
at net.sf.hibernate.impl.SessionImpl.getQueries
(SessionImpl.java:1493)
at net.sf.hibernate.impl.SessionImpl.find
(SessionImpl.java:1464)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.pcc.pcs.dao.customer.CustomerDAOImpl.searchCustomer
(CustomerDAOImpl.java:501)
at com.pcc.pcs.web.struts.customer.SearchCustomerAction.execute
(SearchCustomerAction.java:99)
at
org.apache.struts.action.RequestProcessor.processActionPerform
(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process
(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process
(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost
(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.mortbay.jetty.servlet.ServletHolder.handle
(ServletHolder.java:356)
at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch
(WebApplicationHandler.java:294)
at org.mortbay.jetty.servlet.ServletHandler.handle
(ServletHandler.java:567)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1723)
at org.mortbay.jetty.servlet.WebApplicationContext.handle
(WebApplicationContext.java:514)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1673)
at org.mortbay.http.HttpServer.service(HttpServer.java:879)
at org.jboss.jetty.Jetty.service(Jetty.java:459)
at org.mortbay.http.HttpConnection.service
(HttpConnection.java:783)
at org.mortbay.http.HttpConnection.handleNext
(HttpConnection.java:945)
at org.mortbay.http.HttpConnection.handle
(HttpConnection.java:800)
at org.mortbay.http.SocketListener.handleConnection
(SocketListener.java:201)
at org.mortbay.util.ThreadedServer.handle
(ThreadedServer.java:289)
at org.mortbay.util.ThreadPool$PoolThread.run
(ThreadPool.java:454)
12:00:58,562 INFO [STDOUT] Exception in CustomerDAOImpl :
searchCustomer()
12:00:58,562 INFO [STDOUT] Exception in SearchCustomerAction :
12:00:58,562 ERROR [STDERR] java.lang.NullPointerException
12:00:58,562 ERROR [STDERR] at
com.pcc.pcs.web.struts.customer.SearchCustomerAction.execute
(SearchCustomerAction.java:105)
12:00:58,562 ERROR [STDERR] at
org.apache.struts.action.RequestProcessor.processActionPerform
(RequestProcessor.java:484)
12:00:58,562 ERROR [STDERR] at
org.apache.struts.action.RequestProcessor.process
(RequestProcessor.java:274)


regards
Ravi Kumar

outer joins on arbitary join conditions are not possible yet in HQL.

Micheal, is there any work around for it?

use the createSQLQuery feature

Hi,

Iam getting an exception when Iam using the sql query as follows,

select {cvo.customeridvc,cvo.customernamevc,cvo.shortnamevc,cvo.creditstatusvc,cgvo.groupnamevc,evo.lastnamevc} from customer as {cvo}
left outer join customergroup as {cgvo} on cgvo.custgroupidvc=cgvo.custgroupidvc

The exception as follows,

ERROR [STDERR] net.sf.hibernate.QueryException: Alias [cvo.customeridvc,cvo.customernamevc,cvo.shortnamevc,cvo.creditstatusvc,cgvo.groupnamevc,evo] does not correspond to any of the supplied return aliases = {[cvo]} [select {cvo.customeridvc,cvo.customernamevc,cvo.shortnamevc,cvo.creditstatusvc,cgvo.groupnamevc,evo.lastnamevc} from customer as {cvo}left outer join customergroup as {cgvo} on (cvo.custgroupidvc=cgvo.custgroupidvc) order by lower(cvo.customernamev
at net.sf.hibernate.loader.SQLLoader.substituteBrackets(SQLLoader.java:131)
at net.sf.hibernate.loader.SQLLoader.renderStatement(SQLLoader.java:85)
at net.sf.hibernate.loader.SQLLoader.<init>(SQLLoader.java:71)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3622)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.pcc.pcs.dao.customer.CustomerDAOImpl.searchCustomer(CustomerDAOImpl.java:506)
at com.pcc.pcs.web.struts.customer.SearchCustomerAction.execute(SearchCustomerAction.java:99)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:356)
at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:294)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:567)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1723)
at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:514)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1673)
at org.mortbay.http.HttpServer.service(HttpServer.java:879)
at org.jboss.jetty.Jetty.service(Jetty.java:459)
at org.mortbay.http.HttpConnection.service(HttpConnection.java:783)
at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:945)
at org.mortbay.http.HttpConnection.handle(HttpConnection.java:800)
at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:201)
at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:289)
at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:454)

Pls provide me support,

Please read chapter 15 of the reference guide,
seems not to be valid

Is there any plans to implement LEFT OUTER JOINS fully? Kind of an important feature.

Yes there are plans to implement this at some point. If there is an association between the entities, why not just map it? Then you can have outer join, inner join, full joins.

I would map it, but it would make the mapped object too huge and possibly infinitely large since this particular mapping maps to another large object tree like this which could also map to another large tree and so on, so this is the breaking point (Z to X below).

X
X has children: Y
Y has children: Z
Z has children: X

Wouldn't it be fairly simple to implement OUTER JOIN just returning a null in the returned tuple, shouldn't be much different than doing a regular inner join.

ex of usage:
List myList = sess.find("from X x LEFT OUTER JOIN Y y ON x.id = y.xid");
for(int i = 0; i < myList.size(); i++){
Object[] row = myList.get(i);
X x = row[0];
Y y = row[1];
if(y == null){
// then join didn't happen for this tuple
} else {
// join did happen
}
}

Thats exactly why there are concepts such as proxies and lazy-loading

And as for your suggestion, its not the join style that is an issue (HQL already supports all the join styles) but the use of an ON condition to specify arbitrary associations.

Is it possible to do proxy or lazy loading in a 3 tier environment where the session is closed before you would ever load the lazy items?

No, prefetch the graph you need or lock()/update() your detached objects to a new Session.

Hi Michael,

If hibernae is not supporting left outer join on arbitary join conditions, what is need to go 4 hibernate,Iam struggling lot to implement left out join with hibernate.You have given me workaround with the help of native SQL.If I have to work with native SQL, what is use of hibernate? in this reagrd.

Thanks in advance

if you break your object graph for such reason (not loading all the graph because of n tiers), don't map it, use a flat domain model (no graph) and work with JDBC.

Hibernate offers lazy loading and proxies, i find it hard to believe that it is expensive to load the part of the graph you need (you'll do it in JDBC...).
You can work on implementing a solution to use it in n tiers (even if everybody won't recommend it here).

Now if there is ONE specific use case in your app where you need it, you can also retrieve jdbc connection from hibernate session, and for this case only work with JDBC. That is my advice.

ORM is ORM it and is not THE solution for every apps, just a powerfull one for OO apps.

If your material architecture is in conflict with your domain model and/or the volume of data, think about modifying it (it's open source) or use another thing.

Hi Anthony..

I didn't get you, Hpe ur answer doesn't match with my requirement.