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

[James Sutherland <jamesssss@xxxxxxxxx>]

getChildren() should not trigger the query.  For a LAZY OneToMany EclipseLink
will put a lazy collection (IndirectList) in the field.  The lazy collection
will instantiate and execute the query when accessed.  You can replace the
collection entirely without triggering it as long as you don't access the
collection.  You could also cast the collection to an IndirectList and set
its delegate directly.  Keep in mind this is low level stuff and will
interfere with change tracking, but should be ok for read-only.


janneefef wrote:
> 
> 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
>>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 


-----
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-tp30328410p30406081.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.