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

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

Back to the top