• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Left join using on keyword (Hibernate)

 
Ranch Hand
Posts: 33
Google Web Toolkit Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ganesh Shirsat
Ranch Hand
Posts: 33
Google Web Toolkit Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ganesh,

Use "WITH" keyword instead of "ON".

ON keyword is not supported by hibernate, in newer versions they may provide.
 
Ganesh Shirsat
Ranch Hand
Posts: 33
Google Web Toolkit Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks
 
I wasn't selected to go to mars. This tiny ad got in ahead of me:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic