title: How to store tree structure via JPA?
body: <p>I would like to store a tree structure via JPA. The model consists of two entity classes: <code>Tree</code> and <code>Vertex</code>. Class <code>Vertex</code> basically contains just the vertex' name, class <code>Tree</code> a mapping named <code>parents</code> which stores the parent vertex for each child vertex (<code>child -&gt; parent</code>).</p>
  
  <p>This structure gives me simple and quick access to the information whether a given child vertex has a parent vertex and to the actual parent if any.</p>
  
  <p>I use the following annotation in order to specify how JPA (EclipseLink in my case) should store the relationship:</p>
  
  <pre><code>@MapKeyClass(Vertex.class)
  @MapKeyJoinColumn(name = "child_id", nullable = false)
  @OneToMany(targetEntity = Vertex.class, cascade = CascadeType.ALL)
  @JoinTable(name = "bug_492_tree_parents", inverseJoinColumns = { @JoinColumn(name = "parent_id") })
  private final Map&lt;Vertex, Vertex&gt; parents = Maps.newHashMap();
  </code></pre>
  
  <p>Unfortunately, using the above mapping, I cannot store two child vertices with the same parent, like <code>V = {parent, child1, child2}</code> and <code>E = {{child1, parent}, {child2, parent}}</code> (thus, <code>child1 -&gt; parent</code> and <code>child2 -&gt; parent</code>) because I get an integrity constraint violation:</p>
  
  <pre><code>Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '3-4' for key 'PRIMARY'
  Error Code: 1062
  Call: INSERT INTO bug_492_tree_parents (parent_id, tree_id, child_id) VALUES (?, ?, ?)
  </code></pre>
  
  <p>This is caused by how JPA / EclipseLink specifies the primary key in the join table:</p>
  
  <p><img src="https://i.stack.imgur.com/Ptk95.png" alt="Incorrect primary key definition"></p>
  
  <p>Unfortunately, EclipseLink choses composite key <code>(tree_id,parent_id)</code> as primary key. Using this primary key, it is not possible to store any tree that contains two different child vertices with the same parent vertex as in the above simple example.</p>
  
  <p>Only after manually fixing the primary key (in the MySQL database schema) to <code>(tree_id,child_id)</code> I am able to store that tree:</p>
  
  <p><img src="https://i.stack.imgur.com/SXvXL.png" alt="Fixed primary key"></p>
  
  <p>I tried many different mappings; however, I always run into some (other) issues.</p>
  
  <p>Finally, my question: How should or could I store that simple tree structure via JPA? Do I have to chose a different mapping? Do I have to change my model?</p>