This week's book giveaway is in the JDBC and Relational Databases forum.
We're giving away four copies of Murach's MySQL and have Joel Murach on-line!
See this thread for details.
Win a copy of Murach's MySQL this week in the JDBC and Relational Databases forum!
  • 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

EJB Query Help

 
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i havan Entity

i have 4 parent groups
and it grows as a tree as long as we add a new group to it.


My scenario is: I need an EJB Query which fetch all the child groups
under the specified root/parent Group
.

 
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
By "fetch" I assume you mean read all of the decedents from a root of the tree in a single database query?

Or do you just want them read in some fashion? Since your relationship is EAGER (default for a ManyToOne) they will all be read when you read the root, just possibly not a efficiently as you may like. One query will be executed for each Group (unless the Group is already in the cache).

You can use "LEFT JOIN FETCH" in JPQL to join fetch "n" levels in a single query, but "n" will be a fixed number.
Depending on your JPA provider you may also be able to use batch fetching, if you are using EclipseLink you can set @BatchFetch on the parentGroup relationship, then if you read a set of Group objects their parentGroups would be read in a single query instead of n queries, as would the parent's parent's etc.

Some databases support hierarchical queries (such as Oracle's CONNECT BY PRIOR syntax), you could define a hierarchical query to read in the entire hierarchy in a single query. You would need to use a native SQL query for this as this is not defined in JPQL, unless your JPA provider provides this extension. If you are using EclipseLink, hierarchical query support is provided using the ReadAllQuery and Expression API.
 
harilal ithikkat
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi James,
Thank you very much for your help.

Can you please get me an example query of the same
 
harilal ithikkat
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi James,
I used a recursive method for the same..

Is it a good approach???
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic