aspose file tools*
The moose likes Object Relational Mapping and the fly likes Recursive fetch join not recursively fetching all children Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Recursive fetch join not recursively fetching all children" Watch "Recursive fetch join not recursively fetching all children" New topic
Author

Recursive fetch join not recursively fetching all children

Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
I have a directory type structure represented as entities, i.e. think Directory entity and File entity.

The Directory entity has a collection of File entities and a collection of Directory entities.

I want to get the root directory and 'pre load' all directories and there files.

I am trying:


The query works it just does not pre load everything. I get all of the root directories and the root files, but when I start going into the sub directories and get the files queries are made. I.E. it seems like the recursion is not working.

I have also tried just a JOIN FETCH (which I think does an inner join fetch) with no luck.

Any ideas?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1772
    
  14

Sorry - answered the wrong question so I've deleted my post. Duh!


No more Blub for me, thank you, Vicar.
Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
Messed around with inner vs outer joins with no luck. I also noticed that it seems like the recursion goes 1 level deep and then stops. Is there a property somewhere that defines how deep to go in recursive join like this?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1772
    
  14

Not sure how this ORM stuff works so this may not be any help to you, but are you looking for something like Oracle's CONNECT BY syntax for hierarchical queries?
Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
I am not at all sure if that is something hibernate does under the covers. But I do know that hibernate does indeed support a recursive fetch. From hibernate docs;


A fetch join does not usually need to assign an alias, because the associated objects should not be used in the where clause (or any other clause). The associated objects are also not returned directly in the query results. Instead, they may be accessed via the parent object. The only reason you might need an alias is if you are recursively join fetching a further collection:

from Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens child
left join fetch child.kittens



I am not sure why it is only grabbing some of the children.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
JPA does not allow nested join fetches, nor allow an alias on a join fetch, so this is probably JPA provider specific.

In EclipseLink, you can specify a query hint to perform nested join fetches.

You can't make it recursive in JPQL though, you could go only at best n levels.
In EclipseLink you could use @JoinFetch or @BatchFetch on the mapping to make the querying recursive.

See,
http://java-persistence-performance.blogspot.com/2010/08/batch-fetching-optimizing-object-graph.html


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1772
    
  14

What database are you on? Would it be easier to define a native SQL query to use here, based on your database's implementation of hierarchical queries? That might turn a "hard" Java problem into an easy SQL problem.
Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
I am using hibernate and as stated before hibernate does support this type of query. Any hibernate users out there have any success with this? I don't think this is a 'hard' java problem I just must be missing something.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1772
    
  14

Theodore David Williams wrote:I am using hibernate and as stated before hibernate does support this type of query. Any hibernate users out there have any success with this? I don't think this is a 'hard' java problem I just must be missing something.

A number of databases can do this easily in a single SQL statement, while other people seem to have found it hard to do this with Hibernate/JPA.
Good luck, though.
Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
Here was the subtle problem:



Call to single result seemed to indicate to hibernate to not bring back all the joins into memory.



Even though only one dir comes back (the distinct keyword in hibernate indicates to populate all children but only give back the root dir) a call to get result list works wonders.

All entities that were lazy loaded were brought back into memory in one select statement.

Went from ~30 seconds populating my tree server side to ~1 second.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Recursive fetch join not recursively fetching all children