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

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

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.


janneefef wrote:
> I have a table full of hierarchical data, as in
> 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 ,
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