File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Hibernate LEFT JOIN FETCH WITH syntax Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate LEFT JOIN FETCH WITH syntax " Watch "Hibernate LEFT JOIN FETCH WITH syntax " New topic
Author

Hibernate LEFT JOIN FETCH WITH syntax

Sharon whipple
Ranch Hand

Joined: Jul 31, 2003
Posts: 294

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

Joined: Mar 20, 2007
Posts: 317
Not really sure, but did you try using "AS" in the alias?

Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Sharon whipple
Ranch Hand

Joined: Jul 31, 2003
Posts: 294
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

Joined: Apr 14, 2004
Posts: 10336

"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

Joined: Jul 31, 2003
Posts: 294
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

Joined: Jul 31, 2003
Posts: 294
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

Joined: Jun 28, 2008
Posts: 210
I think the following should easily work..


Rahul Babbar
Sandeep Vaid
Ranch Hand

Joined: Feb 27, 2006
Posts: 390
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 ?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate LEFT JOIN FETCH WITH syntax