Databases
StackOverflow2013
Postsdbo
POOpenJPA 1.2.x select tree structure using JPQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOpenJPA 1.2.x select tree structure using JPQL
    primarykey
    data
    text
    <p>I'm using OpenJPA 1.2.x (JPA1). The problem is that I can't get on with querying tree structure using JPQL.</p> <p>Please, see my entity:</p> <pre><code>@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&lt;Department&gt; children; </code></pre> <p>And my stateless bean method:</p> <pre><code>public Department getFullTree(){ em.createNamedQuery("Department.getFullTree").getResultList(); Department root = em.find(Department.class, Department.ROOT_ID); return root; } </code></pre> <p>My aim is to get full department tree starting from root. I've tried this approach:</p> <p>Is that true? I'm using DB2. And will use it in future. <a href="https://stackoverflow.com/questions/2598588/jpa-query-for-getting-the-whole-tree">JPA query for getting the whole tree</a></p> <p>This seems like doesn't work: <a href="http://www.tikalk.com/java/load-a-tree-with-jpa-and-hibernate" rel="nofollow noreferrer">http://www.tikalk.com/java/load-a-tree-with-jpa-and-hibernate</a></p> <p>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...</p> <p>What do I have to try next?</p> <p><strong>UPD: Here is my traverse code:</strong></p> <pre><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 -&gt; start"); out.println("&lt;ul id=\"tree-root\"&gt; "); for(Department dep : root.getChildren()){ printNode(dep, out); } closeUL(out); LOG.trace("#printTree -&gt; 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("&lt;span id=\"tree_node_"+dep.getId()+"\" class=\"ekp-tree-node\" onclick=\"ekpCommon.tree.toggleBullet(this)\"&gt;"+dep.getLabel()+"&lt;/span&gt;"); 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("&lt;ul&gt;"); } public static void closeUL(JspWriter out) throws Exception{ out.println("&lt;/ul&gt;"); } public static void openLI(JspWriter out) throws Exception{ out.println("&lt;li&gt;"); } public static void closeLI(JspWriter out) throws Exception{ out.println("&lt;/li&gt;"); } </code></pre> <p>LOG.trace("#printNode title[{}] children.size()[{}]",dep.getLabel(), (dep.getChildren() == null ? "NULL" : dep.getChildren().size()) );</p> <p>always outputs: <strong>"#printNode title[All departments] children.size()[19]"</strong> 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?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USSergey
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThink you mean OpenJPA 1.2.x. That is JPA1.0
      singulars
      1. POOpenJPA 1.2.x select tree structure using JPQL
      1. USDataNucleus
    2. COI 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.
      singulars
      1. POOpenJPA 1.2.x select tree structure using JPQL
      1. USSergey
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload