Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate LEFT JOIN FETCH WITH syntax

 
Sharon whipple
Ranch Hand
Posts: 294
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Hi ranchers
I am unable to find samples of Left join fetch with the "with" clause,

I have class Cat with Kittens list:



What I would like to do is get all Cats with kitten’s weight 50 pound or up,
Cat who don’t have kittens answering the criteria will be also retrieved, and they will have kitten list = null
So I tried:



But I got:
unexpected token: WITH

What is the correct synax?
Thank you!
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not really sure, but did you try using "AS" in the alias?

 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sridhar Santhanakrishnan wrote:Not really sure, but did you try using "AS" in the alias?



That won't matter. Both styles are valid syntax.

WITH is not an HQL keyword. Have you tried having?
 
Sharon whipple
Ranch Hand
Posts: 294
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Indeed Cat c and Cat as c is the same,

Paul Sturrock wrote:
That won't matter. Both styles are valid syntax.

WITH is not an HQL keyword. Have you tried having?


Having produces:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query;
nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
(It needs Group By)
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"It needs Group By"

The same logic applies in HQL as in SQL. Using having requires a group by clause. Have a look at the example in the documentation (linked above).
 
Sharon whipple
Ranch Hand
Posts: 294
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:
The same logic applies in HQL as in SQL. Using having requires a group by clause. Have a look at the example in the documentation (linked above).


Thank you Paul
Why Group results when I can Left Join them? the Group by clause will change the query plan.
 
Sharon whipple
Ranch Hand
Posts: 294
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got a native sql solution:



I will try to run it using session.createSQLQuery,
If anyone have HQL version, i will be glad to hear.

Thank you
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the following should easily work..

 
Sandeep Vaid
Ranch Hand
Posts: 392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is a huge difference in using "left join" and "left join fetch".

1. "Left join" --->


Instead of a List of Items, this query returns a List of Object[] arrays. At index 0 is the Item, and at index 1 is the Bid. A particular Item may appear multiple times, once for each associated Bid.
These duplicate items are duplicate in-memory references, not duplicate instances!


2. "left join fetch" ---> This is also known as eager dynamic fetching.


When executed, it returns a list of Item instances, with their bids collections fully initialized. This is quite different if you compare it to the ordered pairs returned by the queries in the previous section!

The corresponding generated SQL will be:

An additional WITH clause wouldn’t make sense here. You can’t restrict the Bid instances: All the collections must be fully initialized.


Personally I don’t agree with author and hibernate team that WITH clause doesn’t make any sense here… Why I am bound to get all the collections? What if I want only those bids whose
Amount >100 ?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic