Sunday, January 17, 2016

JPA and Spring Data Self Join Example with Annotations

I had a table I needed to do a self join on.  We'll call the table "tree".  A tree record can have zero or one parent_tree record.  This is recursive until you find null for the parent_tree.  In the real world, most of the time, this example probably doesn't make sense to have tree depths where there is only ever only one child per depth.

Example "tree" table
treeId (primary key, int) parent_tree (int)
1                                       null
2                                       1
3                                       2

If I did the following SQL selects, I would get result:

  • select * from tree where treeId = 1 would return itself (treeId 1)
  • select * from tree where treeId = 2 would return itself (treeId 2), and one nested tree (with treeId 1)
  • select * from tree where treeId = 3 would return itself (treeId 3), one nested tree (with treeId of 2), and then another one nested tree inside tree (with treeId of 2) with treeId of 1

Below is sample Java code to illustrate how this is modeled and implemented, items of note include:

    • @OneToOne is used, since you can only have zero or one tree's inside of a tree, so on and so forth
    • @JoinColumn
      • name is set to parent_tree since parent_tree is the actual column name from the db
      • referencedColumnName is treeId, since it references the primary key column treeId
      • nullable is true, since you can have zero or one tree nested inside a tree
      • the parentTree variable is going to return a "Tree" instead of a Long (we want the object it's pointing to, not the integer value of the primary key)
    • An interface that extends CrudRepository to get all Spring out-of-the-box Crud methods, like findAll

import javax.persistence.*;

public class Tree {
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long treeId;
    // the column parentTree is going to return the object Tree, instead of the parentTree as a Long
    @OneToOne(fetch=FetchType.EAGER, optional = false)
    @JoinColumn(name="parent_tree", referencedColumnName="treeId", nullable = true)  
    private Tree parentTree;

    public String toString() {
        return "Tree [treeId=" + treeId + ", parentTree=" + parentTree + "]";


import com.cherryshoe.Tree;

public interface TreeRepository extends CrudRepository<Tree, Long> {
  // findAll is inherited from CrudRepository

I tested this on Windows 7, Java 1.8.0_66, and MySQL 5.7.10.

No comments:

Post a Comment

I appreciate your time in leaving a comment!