[eclipselink-users] recursive parent-children relationship cached in a s

["Jason Kinzer" <jmkinzer@xxxxxxxxx>]

Hi,

I have a  self-referential/recursive parent-children relationship (i.e. hierarchy) that I'm fumbling around with and would greatly appreciate suggestions.

My naive attempt (snippet at the bottom) works merrily, but performance is not all I'd hoped for. The entire hierarchy is guaranteed to get traversed, is very heavily used, and is read-only - so what I'd like to accomplish is to eagerly read in the entire hierarchy in one fell swoop [to get it fully cached]. I have tried to using batching on the 'children' collection or inner join on them and walking the hierarchy manually in code, but this generates far too many fetches - in principle only *one* fetch is required here.

Most fortunately I'm on Oracle and Eclipselink has connect-by support. This gets me *almost* exactly what I want (a single fetch). And there was much rejoicing:

                    ReadAllQuery hquery = new ReadAllQuery();
                    ExpressionBuilder eb = new ExpressionBuilder();
                    _expression_ startWith = eb.get("id").equal(root.getId()).and(eb.get("sceId").equal(root.getSceId()));
                    _expression_ connectBy = eb.get("children");
                    Vector order = new Vector(); order.addElement(eb.get("id"));
                    hquery.setHierarchicalQueryClause(startWith, connectBy, order);
                    hquery.setReferenceClass(Node.class);
                    session.executeQuery(hquery);

Unfortunately while this caches all the nodes, it does not cache the "children" collection. So, if my perform my "walk" over the hierarchy it still fetches for each node to build the "children" collection. Much sadness.

Now, with any other sort of query where the collection is not joined/batched, I can understand this: it can't t know who belongs in the collection so even though the members are present it will have to build it anyway.  However, in a connect-by query like this it *does* know the children.

So basically, my feeling is that when I give it "_expression_ connectBy = eb.get("children")" this should be a hint to EclipseLink to go ahead and populate the collection that denotes. So I'd like to know which one of the following hold:
1. It should in fact populate the collection and I'm screwing something up
2. There's some fundamentaly reason it can't correctly work the way I want
3. This is a feature enhancement

Thanks in advance for any feedback,
Jason

---------------

public abstract class Node {
   ...
    protected ValueHolderInterface parent = null; // stores Node
    protected ValueHolderInterface children = null; // stores SortedSet<Node>
  ....
}

// parent descriptor
        OneToOneMapping parentMapping = new OneToOneMapping();
        parentMapping.setAttributeName("parent");
        parentMapping.setReferenceClass(Node.class);
        parentMapping.addForeignKeyFieldName("MST_LINK_NODE.SCENARIO_ID", "MST_LINK_NODE.SCENARIO_ID");
        parentMapping.addForeignKeyFieldName("MST_LINK_NODE.PARENT_ID", "MST_LINK_NODE.ID");
        
// children descriptor
        OneToManyMapping childMapping = new OneToManyMapping();
        childMapping.setAttributeName("children");
        childMapping.setReferenceClass(Node.class);
        childMapping.useSortedSetClass(TreeSet.class, new Comparator<Node>() {  //IndirectSet.class
            public int compare(Node arg0, Node arg1) {
                return arg0.getId().compareTo(arg1.getId());
            }
        });
        childMapping.addTargetForeignKeyFieldName("MST_LINK_NODE.SCENARIO_ID", "MST_LINK_NODE.SCENARIO_ID");
        childMapping.addTargetForeignKeyFieldName("MST_LINK_NODE.PARENT_ID", "MST_LINK_NODE.ID");