Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Left join using on keyword (Hibernate)

 
Ganesh Shirsat
Ranch Hand
Posts: 33
Google Web Toolkit Java Spring
  • 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.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac 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 Java Spring
  • 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
Sheriff
Posts: 17278
6
IntelliJ IDE Mac 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
 
Charan kumar sarvepalli
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 Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic