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

[janne postilista <jannepostilistat@xxxxxxxxx>]

The native query was the solution I was trying to use. I would rather
not use vendor-specific JPA extensions if possible.

My plan was to do the native CONNECT BY PRIOR query, iterate through
the resulting JPA entities, and populate the children-collections
manually (since I know each entity's parent).

I want to have the Person.children collection JPA managed since most
use cases benefit from the automation.

But (IIRC, I dont have the code running anymore right now, could test
it more), when I tried to populate the children collection,
person.getChildren() triggers a new SQL query that fetches the
children. And I could not figure out how to populate the collection
without causing eclipselink to execute a new query.

Should I be able to populate the person.children collections without
causing (unnecessary, in this native query case) database hits?


On Wed, Dec 1, 2010 at 4:43 PM, James Sutherland <jamesssss@xxxxxxxxx> wrote:
>
> 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.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>