wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Left join using on keyword (Hibernate) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Left join using on keyword (Hibernate)" Watch "Left join using on keyword (Hibernate)" New topic
Author

Left join using on keyword (Hibernate)

Ganesh Shirsat
Ranch Hand

Joined: Jan 10, 2010
Posts: 33

hi friends,


this is sql query

select tmrhdr.tmr_id,tmr_NAME,tmrlog.id,tmrlog.tmr_type from tmr_hdr tmrhdr
left join tmr_log tmrlog on tmrhdr.tmr_id = tmrlog.tmr_id and tmrlog.tmr_type = 'TimeSheet'
where tmr_user ='golden'


i want to write the same query in HQL. i try to write query.

select tmrhdr,tmrhdr.tmrId,tmrhdr.tmrName,tmrlog.id,tmrlog.tmrType from TmrHdr as tmrhdr
left join TmrLog as tmrlog where tmrhdr.tmrId = tmrlog.tmrHdr.tmrId and tmrlog.tmrType = 'TimeSheet'
where tmrhdr.user ='golden'

but result of HQL and sql query is different.

HQL query not take the all record of TmrHdr table . instead it will fire the query like inner join.

please help me.

how to right the left join in Hibernate.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

It is doing inner join because you are using theta style inner joins in your where clause.

Remove

"tmrhdr.tmrId = tmrlog.tmrHdr.tmrId and "

from your Where clause.

Mark

Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Ganesh Shirsat
Ranch Hand

Joined: Jan 10, 2010
Posts: 33

hi friends,

according to your suggestion i am trying this solution

select tmrhdr,tmrhdr.tmrId,tmrhdr.tmrName,tmrlog.id,tmrlog.tmrType from TmrHdr as tmrhdr
left join TmrLog as tmrlog where tmrlog.tmrType = 'TimeSheet' and
tmrhdr.user ='golden'


but still i won't give the left join like my sql query.

could please help to solve this problem.

Thnaks,
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

OK try this

SELECT t
FROM TmrHdr t LEFT JOIN FETCH TrmLog log
WHERE t.tmrType='TimeSheet'
AND t.user='Golden'

That is 100% a Left join. If it doesn't work, then you might have to post your mappings for the two classes.

Mark
Charan kumar sarvepalli
Ranch Hand

Joined: Dec 14, 2008
Posts: 40
Hi Ganesh,

Use "WITH" keyword instead of "ON".

ON keyword is not supported by hibernate, in newer versions they may provide.


-- Charan
Ganesh Shirsat
Ranch Hand

Joined: Jan 10, 2010
Posts: 33

thanks
 
GeeCON Prague 2014
 
subject: Left join using on keyword (Hibernate)