• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Recursive fetch join not recursively fetching all children

 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry - answered the wrong question so I've deleted my post. Duh!
 
Theodore David Williams
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic