OpenJPA 1.2.x select tree structure using JPQL

Question

I'm using OpenJPA 1.2.x (JPA1). The problem is that I can't get on with querying tree structure using JPQL.

Please, see my entity:

@NamedQueries(
         { 
           @NamedQuery(
             name="Department.getFullTree",
             query="SELECT dep FROM Department dep LEFT JOIN fetch dep.children"
           )
         }
        )
@Entity
public class Department {

    public static final Long ROOT_ID = 0L;

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="DEPARTMENT_SEQ")
    @SequenceGenerator(name="DEPARTMENT_SEQ", sequenceName="DEPARTMENT_SEQ", allocationSize=1)
    @Column(name="ID")
    private Long id;

    @Column(name="PARENT_ID")
    private Long parentId;

    @ManyToOne(targetEntity = Department.class, fetch = FetchType.EAGER)
    @JoinColumn(name = "PARENT_ID")
    private Department parent;

    @Column(name="LABEL")
    private String label;

    @OneToMany(mappedBy = "parent", 
               targetEntity = Department.class, 
               fetch=FetchType.LAZY, 
               cascade = {CascadeType.PERSIST, CascadeType.ALL})
    private List<Department> children;

And my stateless bean method:

public Department getFullTree(){
    em.createNamedQuery("Department.getFullTree").getResultList();
    Department root = em.find(Department.class, Department.ROOT_ID); 
    return root;
}

My aim is to get full department tree starting from root. I've tried this approach:

Is that true? I'm using DB2. And will use it in future. JPA query for getting the whole tree

This seems like doesn't work: http://www.tikalk.com/java/load-a-tree-with-jpa-and-hibernate

I've tried to repeat, but I get stackoverflow error while traversing tree (don't have more than 200 nodes at all). Debug output showed that root has itself as a child, so it's round-linked structure...

What do I have to try next?

UPD: Here is my traverse code:

public class TreeTagHelper {

    private static final Logger LOG = LoggerFactory.getLogger(TreeTagHelper.class);

    private Department root;
    private JspWriter out;

    public TreeTagHelper(Department root, JspWriter out){
        LOG.trace("#init");
        this.root = root;
        this.out = out;
    }

    public void printTree() throws Exception{
        LOG.trace("#printTree -> start");
        out.println("<ul id=\"tree-root\"> ");      
        for(Department dep : root.getChildren()){
            printNode(dep, out);
        }       
        closeUL(out);
        LOG.trace("#printTree -> end");
    }

    public static void printNode(Department dep, JspWriter out) throws Exception{
        LOG.trace("#printNode title[{}] children.size()[{}]",dep.getLabel(), (dep.getChildren() == null ? "NULL" : dep.getChildren().size()) );
        openLI(out);
        out.print("<span id=\"tree_node_"+dep.getId()+"\" class=\"ekp-tree-node\" onclick=\"ekpCommon.tree.toggleBullet(this)\">"+dep.getLabel()+"</span>");
        if(dep.getChildren()!=null){
            openUL(out);
            for(Department child : dep.getChildren()){
                printNode(child, out);
            }
            closeUL(out);
        }

        closeLI(out);
    }

    public static void openUL(JspWriter  out) throws Exception{
        out.println("<ul>");
    }

    public static void closeUL(JspWriter out) throws Exception{
        out.println("</ul>");       
    }

    public static void openLI(JspWriter out) throws Exception{
        out.println("<li>");
    }

    public static void closeLI(JspWriter out) throws Exception{
        out.println("</li>");       
    }

LOG.trace("#printNode title[{}] children.size()[{}]",dep.getLabel(), (dep.getChildren() == null ? "NULL" : dep.getChildren().size()) );

always outputs: "#printNode title[All departments] children.size()[19]" Seems like the root("All departments") has 19 children. It's true, I checked it in my DB. But each children is the root! So it's infinite structure...??? Root doesn't get children? It fetches itself?

I want to say that 1.0 and 1.2 has great differences. For example 1.0.x didn't support ORDER BY aggregate functions (COUNT, e.t.c.) in JPQL. I think we have to keep in mind actual version of implementation provider. — Capacytron, Nov 06 '10 at 12:40

score 1 · Accepted Answer · answered Nov 06 '10 at 13:31

I've tried to repeat, but I get stackoverflow error while traversing tree (don't have more than 200 nodes at all). Debug output showed that root has itself as a child, so it's round-linked structure...

Then your data are very likely wrong. Double check that the root has no parent.

score 0 · Answer 2 · answered Nov 06 '10 at 09:01

0

I think what you are doing is correct and should work. You are loading the complete tree structure into the persistence context and then obtain a reference to the root node. Perhaps there is something wrong with your tree traversing? This would be the only thing that could cause a StackOverflowError. Could you post your code?

answered Nov 06 '10 at 09:01

chkal

5,598
21
26

Here is simple helper that accepts Department as a root of tree and "prints" html view for the tree. When I'll overcome problem with tree, I'll move to some template engine. – Capacytron Nov 06 '10 at 12:42

score 0 · Answer 3 · answered Nov 06 '10 at 13:18

Please see my DB struccture:

ID    PARENT_ID    LABEL
0     0        All Departments
1000   0           Central office

That is why JPA didn't work :(

See the first row. It's the root of the tree. Seems like JPA read it as a child referencing to itself (PARENT_ID does exist in table, so JOIN can be performed).

I've change DB values:

ID    PARENT_ID    LABEL
0     -1           All Departments
1000   0           Central office

Now it works! Yeah! :)

OpenJPA 1.2.x select tree structure using JPQL

3 Answers3

Linked