[eclipselink-users] Loading hierarchical data using a single SQL query

[janne postilista <jannepostilistat@xxxxxxxxx>]

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?