Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Loading hierarchical data using a single SQL query

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,
>> janneefef wrote:
>>> I have a table full of hierarchical data, as in
>>> 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?
>> -----
>> James Sutherland
>>  EclipseLink ,
>> TopLink
>> Wiki: EclipseLink ,
>> TopLink
>> Forums: TopLink ,
>> EclipseLink
>> Book: Java Persistence
>> Blog: Java Persistence
>> Performance
>> --
>> View this message in context:
>> Sent from the EclipseLink - Users mailing list archive at
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx

----- James Sutherland
 EclipseLink ,
Wiki: EclipseLink , TopLink 
Forums: TopLink , EclipseLink 
Book: Java Persistence 
Blog: Java Persistence
View this message in context:
Sent from the EclipseLink - Users mailing list archive at

Back to the top