Generating a child tree structure using OpenJPA

[Derek West]

I have a flat table structure...

ORGANIZATION_ID
...
PARENT_ORGANIZATION_ID
...
that I am trying to turn into a tree structure depending on the OrgID that I pass inn... For instance, if I have a tree if this is the data I have in the table...


ORGANIZATION_ID | Name | PARENT_ORGANIZATION_ID
--------------------------------------------------
1 | A | 0 -Indicates root
2 | B | 1
3 | C | 2
4 | D | 2
5 | E | 4
6 | F | 1
7 | G | 1
8 | H | 7
9 | J | 8
10 | K | 9
The following tree structure would be created...

A- Root Element
|\ \
B F G
|\ |
C D H
| |
E J
|
K
You would be creating trees and so forth with this information... I have OpenJPA as my ORM tool of choice. I am trying to figure out someway to generate a list of objects of all children based on the node that is passed in... So If I pass in A, I will get that entire tree structure. If I pass in B, I would just get the Root, C, D, and E.
If I pass in K I get an empty collections. If I pass in J I have a collection with one element in it... Which would be K... Now, I am contemplating and researching how I would do this... One of the ways that I found online suggests writing a named query with a left join on the OrgId and the parentID (Which I am not entirely certain would work in my context)...

@NamedQuery(name="getOrganizationChildListTree", query = "SELECT DISTINCT o from Organization left join fetch o.organizationId where o.parentOrganizationId = :rootID")

in conjunction with setting up my Entity Bean in such a way that it would allow for this....

@SuppressWarnings("unused")
    @ManyToOne
    @JoinColumn(name="PARENT_ORGANIZATION_ID")
    private Organization parent;

    @OneToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE},
            fetch = FetchType.LAZY,
            mappedBy= "parent")
    @OrderBy("ORGANIZATION_NAME")
    private List<Organization> childOrgsTree = new ArrayList<Organization>();

I would also need to call this correctly in my DAO Session Bean... Which I am not entirely certain what to do with...

From the examples I have seen so far, I would probably end up doing something like this...

em.createNamedQuery("getOrganizationChildListTree").setParameters("rootID", rootID).getResultList();
Organization root = em.find(Organization.class, rootID); 
return root;

As of right now, none of this is working and I get a big awesome stack Overflow exception (among other things)... Has anyone built a tree structure of similar make and type? Would this get all child nodes exhaustively? Any help would be greatly appreciated.

(Moderator edits to add code and tt tags to improve readability)

[Derek West]

Bump...

Anybody have any ideas at all?

[Junilu Lacar]

My first, gut-wrench reaction was ... EJB? Yuck Do you have good reasons to use EJB?

I've dealt with this type of relationship before but I used Spring and POJOs. Basically, the DAO will get individual nodes, which I have Spring DAOSupport read from the DB and turn into a Map for me. Then I iterate through the Map elements and build up the Tree. I remember having to do a build and collect. Collect because as you iterate through the map, you can run across child nodes whose parent nodes have not been processed yet so you have to go through the whole list once to create as many trees as you can, possibly ending up with a few trees with temporarily orphaned "root" nodes. Then you go through all the "root" nodes of these small trees and reunite them with their parents, which should be somewhere in one of the other trees. The only "root" node where this reuniting process fails is the actual root node of the final single-view tree.

Hope this helps

[Junilu Lacar]

Derek West wrote:Bump...

Anybody have any ideas at all?

Please read about our EaseUp (<-- click the link) protocol


... and Welcome to the Ranch!

[Derek West]

Junilu Lacar wrote:My first, gut-wrench reaction was ... EJB? Yuck Do you have good reasons to use EJB?

I've dealt with this type of relationship before but I used Spring and POJOs. Basically, the DAO will get individual nodes, which I have Spring DAOSupport read from the DB and turn into a Map for me. Then I iterate through the Map elements and build up the Tree. I remember having to do a build and collect. Collect because as you iterate through the map, you can run across child nodes whose parent nodes have not been processed yet so you have to go through the whole list once to create as many trees as you can, possibly ending up with a few trees with temporarily orphaned "root" nodes. Then you go through all the "root" nodes of these small trees and reunite them with their parents, which should be somewhere in one of the other trees. The only "root" node where this reuniting process fails is the actual root node of the final single-view tree.

Hope this helps

Ill probably end up doing something like that. I was just hoping that there was some kind of methodology in JPA that could help with that.

[Junilu Lacar]

Well, a search for JPA tree turns up some possible articles to read.

[Derek West]

Bump...
After doing some significant research, I found out that this problem is an Adjacency-List model to JPA entity problem. It appears as though I will kind of be breaking new ground here...
But generally speaking one link that I looked at, recomended that I write a query that will load the entire tree into the persistence environment with one query and then traverse the tree looking for a particular nodes children all its children.

I am a little confused about how I would traverse nodes recursively once they are in the persistence context. BTW... this was by far the most complete tutorial on doing something like this out there, so for those of you struggling with similar problems, here is the link...Cool link
I guess my first question would be what do you think of the structure that he puts into his code to represent the children?

@OneToMany
    @JoinColumn(name = "parent_id")
    @OrderColumn
    private List<Node> children = new LinkedList<Node>();

I guess that would accurately represent children... But why map it to the parent_id column?
Now I am really confused about how to recurse through all nodes in the Persistence context and find there children...
I guess where I would start was I would find all direct children of this current node... And when I say find, I mean find, IE... EntityManager.Find(class, ID)...
I would then go down to the child and then look for its children... My question is, how would I determine the end of the recursion? When It has no more children?
How can I determine that in the Persistence context? Normally I would just run a query that would tell me if this node has any children. Do you guys have any recomendations?
Thanks again for any help or suggestions that you could give.