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:

  • Tree.java
    • @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)
  • TreeRepository.java
    • An interface that extends CrudRepository to get all Spring out-of-the-box Crud methods, like findAll

Tree.java

import javax.persistence.*;

@Entity
@Table(name="TREE")
public class Tree {
    @Id
    @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;

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

}

TreeRepository.java

import org.springframework.data.repository.CrudRepository;
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.

1 comment:

I appreciate your time in leaving a comment!