Re: [eclipselink-users] Loading hierarchical data using a single SQL que

[James Sutherland <jamesssss@xxxxxxxxx>]

There is no hierarchical query support in the JPA standard, nor is it
standard across databases.

You can execute a hierarchical query using native SQL, or using an
EclipseLink ReadAllQuery, there is currently no JPQL support for
hierarchical queries in EclipseLink, you can log an enhancement request if
you wish.

A hierarchical query will get you all of the data, but this will not help
map the relationships, as in general just having all of the data is not
enough.

EclipseLink does offer batch fetching that allows trees like this to be
efficiently read in, including their relationships.  You can use the
@BatchFetch annotation for this.  It will read each level in a single query,
so will have good performance.  In would be possible to create a specialized
type of batch fetching for this specific type of relationship using a
hierarchical query, but that is something EclipseLink does not currently
support, you could log an enhancement for this if you wish, but the
performance should not be significantly different than using batch fetching
with 1 query per level.

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



janneefef wrote:
> 
> I have a table full of hierarchical data, as in
> 
> TABLE PERSON (
> ID NUMBER(10)
> PARENT_ID NUMBER(10));
> 
> class Person {
>  long id;
>  Person parent;
>  Collection<Person> children;
> }
> 
> Example is simplified and the data amount is actually not this trivial.
> 
> I want to fetch this data effectively. I am using Oracle as DB and can
> get the data using a hierarchical CONNECT BY PRIOR query. But I can't
> figure out how to do this using EclipseLink and standard JPA. I can
> execute the CONNECT BY PRIOR query by using JPA NativeQuery, but it
> seems Person.children cannot be populated by EclipseLink without
> executing additional queries one per each children collection?
> 
> In practise I am actually fetching ALL of the tables data and hence
> could do with just a simple findAll() query (forgetting the connect by
> prior completely) to fetch all the data for the table. Still the same
> problem persist, populating the Person.children() collections causes
> additional hits to database.
> 
> I know theres some custom classes in EclipseLink to work with
> hierarchical queries (setHierarchicalQueryClause(startWith, connectBy,
> orderSibling)) but I wish to use just standard JPA.
> 
> Is it possible to fetch this hierarchical data using a single SQL
> statement, with EclipseLink and no custom JPA extensions?
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
Blog:  http://java-persistence-performance.blogspot.com/ Java Persistence
Performance 
-- 
View this message in context: http://old.nabble.com/Loading-hierarchical-data-using-a-single-SQL-query-tp30328410p30349545.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.