Tree Entity with JPA and spring-boot

In this article we are going to model a hierarchical relationship in the same database table using JPA with spring-boot and spring-data. For test all we will be use test-container.

There several cases for using tree structures. To say some cases where i used was in topics, categories, menu items and properties keys. For our example we will use the case with menu items.

I created a github repository for have a reference to the source code.

The technology stack is as follows:

gradle for build, lombok for prevent boilerplate code, spring and spring-boot for DI, postgre, JPA, hibernate, spring-data, data-api, ent-core, flyway for database interaction, docker for integration-test with junit-jupiter and test-containers. The API for the hierarchical structure is defined in the github repository data-api which can be imported over the public maven repository.

Let start and create the tree entity class.

package io.github.astrapi69.treentity.jpa.entity;

import javax.persistence.Entity;
import javax.persistence.Table;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.SuperBuilder;
import io.github.astrapi69.entity.treeable.TreeUUIDEntity;

@Entity
@Table(name = MenuItems.TABLE_NAME)
@Getter
@Setter
@ToString
@NoArgsConstructor
@SuperBuilder
public class MenuItems extends TreeUUIDEntity<String, MenuItems>
{
   public static final String TABLE_NAME = "menu_items";
   /** Serial Version UID */
   private static final long serialVersionUID = 1L;
}

We see here the most of lombok annotations that keeps the boilerplate code away, but what is more important is we derive all functionality from the base entity class TreeUUIDEntity. The base entity class TreeUUIDEntity is generic and holds fields like the value, parent node, depth and if it is a node or a leaf. The TreeUUIDEntity is part of the github repository ent-core. Lets have a look at it:

package io.github.astrapi69.entity.treeable;

import javax.persistence.Column;
import javax.persistence.FetchType;
import javax.persistence.ForeignKey;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.MappedSuperclass;

import lombok.AccessLevel;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.FieldDefaults;
import lombok.experimental.SuperBuilder;
import io.github.astrapi69.data.treeable.Treeable;
import io.github.astrapi69.entity.uniqueable.UUIDEntity;

@MappedSuperclass
@Getter
@Setter
@NoArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@SuperBuilder
public class TreeUUIDEntity<T, TR extends Treeable<T, TR>> extends UUIDEntity
   implements
      Treeable<T, TR>
{

   /** The depth of this node. For the root depth would be 0. */
   @Column(name = "depth")
   int depth;

   /** A flag that indicates if this tree entity is a node or a leaf */
   @Column(name = "node")
   boolean node;

   /** The parent tree entity that references to the parent. */
   @ManyToOne(fetch = FetchType.EAGER)
   @JoinColumn(name = "parent_id", foreignKey = @ForeignKey(name = "fk_treeable_parent_id"))
   TR parent;

   /** The value of this tree entity */
   @Column(name = "value", columnDefinition = "TEXT")
   T value;

}

So the tree functionality is derived from TreeUUIDEntity into the concrete entity class MenuItems. As next lets create the corresponding spring-data repository:

package io.github.astrapi69.treentity.jpa.repository;

import java.util.List;
import java.util.Optional;
import java.util.UUID;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import io.github.astrapi69.treentity.jpa.entity.MenuItems;

public interface MenuItemsRepository extends JpaRepository<MenuItems, UUID>
{

   List<MenuItems> findByValue(String value);

   @Transactional
   @Query("select entity from MenuItems entity where entity.depth=:depth "
      + " and entity.value=:value") List<MenuItems> findByDepthAndValue(@Param("depth") int depth,
      @Param("value") String value);

   @Transactional
   @Query("select entity from MenuItems entity where entity.depth=:depth "
      + " and entity.value=:value " + " and entity.parent=:parent")
   List<MenuItems> findByDepthAndValueAndParent(@Param("depth") int depth,
      @Param("value") String value, @Param("parent") MenuItems parent);

   @Transactional
   @Query("select entity from MenuItems entity where entity.value=:value "
      + " and entity.parent is null") Optional<MenuItems> findRootByValue(@Param("value") String value);

   @Query("select entity from MenuItems entity where entity.depth=:depth "
      + " and entity.value=:value " + " and entity.parent=:parent " +
      "and entity.node=:node")
   Optional<MenuItems> findByDepthAndValueAndParentAndNode(@Param("depth") int depth,
      @Param("value") String value, @Param("parent") MenuItems parent, @Param("node") boolean node);

   @Query(value = "WITH RECURSIVE ancestors(id, parent_id, value, level) AS ("
      + " SELECT pkp.id, pkp.parent_id, pkp.value, 1 AS level "
      + " FROM menu_items pkp " 
      + " WHERE pkp.id = :treeId " 
      + " UNION ALL "
      + " SELECT parent.id, parent.parent_id, parent.value, child.level + 1 AS level "
      + " FROM menu_items parent " + " JOIN ancestors child "
      + " ON parent.id = child.parent_id " + " )"
      + "SELECT * from ancestors ORDER BY level DESC", nativeQuery = true)
   List<MenuItems> findAncestors(@Param("treeId") UUID treeId);

   @Query(value = "WITH RECURSIVE children(id, parent_id, value) AS ("
      + " SELECT pkp.id, pkp.parent_id, pkp.value, 1 AS level "
      + " FROM menu_items pkp " 
      + " WHERE pkp.id=:treeId " 
      + " UNION ALL "
      + " SELECT parent.id, parent.parent_id, parent.value, child.level + 1 AS level "
      + " FROM menu_items parent " + " JOIN children child "
      + " ON child.id = parent.parent_id) "
      + " SELECT * FROM children "
      , nativeQuery = true)
   List<MenuItems> getAllChildrenWithParent(@Param("treeId") UUID treeId);

   @Query(value = "select * from menu_items pkp where pkp.parent_id =:parent", nativeQuery = true)
   List<MenuItems> getChildren(@Param("parent") UUID parent);
}

Here we have some finder algorithms for get the ancestors, the direct children and get all children recursive of a MenuItems entity.

Lets start write some integration tests for the spring-data repository class with test-containers. Precondition for use test-container is that you have installed docker on your system. As next step we create an abstract class that holds a test container with our postgres database.

package io.github.astrapi69.treentity.integration;

import java.time.Duration;
import java.util.Map;
import java.util.stream.Stream;

import lombok.NonNull;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.context.ApplicationContextInitializer;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.core.env.ConfigurableEnvironment;
import org.springframework.core.env.MapPropertySource;
import org.springframework.test.context.ContextConfiguration;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.lifecycle.Startables;
import org.testcontainers.shaded.com.google.common.collect.ImmutableMap;

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ContextConfiguration(initializers = AbstractIntegrationTest.Initializer.class)
public class AbstractIntegrationTest
{

   /**
    * see 'https://hub.docker.com/_/postgres?tab=tags&page=1&name=12.5'
    */
   private static final String IMAGE_VERSION = "postgres:12.5";
   @Autowired
   protected TestEntityManager entityManager;

   static class Initializer
      implements
         ApplicationContextInitializer<ConfigurableApplicationContext>
   {

      static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>(IMAGE_VERSION)
         .withDatabaseName("treentity").withUsername("postgres").withPassword("postgres")
         .withStartupTimeout(Duration.ofSeconds(600));

      private static void startContainers()
      {
         Startables.deepStart(Stream.of(postgres)).join();
         // we can add further containers
         // here like rabbitmq or other databases
      }

      private static @NonNull Map<String, Object> createConnectionConfiguration()
      {
         return ImmutableMap.of("spring.datasource.url", postgres.getJdbcUrl(),
            "spring.datasource.username", postgres.getUsername(), "spring.datasource.password",
            postgres.getPassword());
      }


      @Override
      public void initialize(@NonNull ConfigurableApplicationContext applicationContext)
      {
         startContainers();
         ConfigurableEnvironment environment = applicationContext.getEnvironment();
         MapPropertySource testcontainers = new MapPropertySource("testcontainers",
            createConnectionConfiguration());
         environment.getPropertySources().addFirst(testcontainers);
      }

   }
}

The class AbstractIntegrationTest defines a postgres container where the database is initialized from the flyway migration script. Our unit test for the repository derives from the class AbstractIntegrationTest and looks as follows:

package io.github.astrapi69.treentity.jpa.repository;

import io.github.astrapi69.treentity.integration.AbstractIntegrationTest;
import io.github.astrapi69.treentity.jpa.entity.MenuItems;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

class MenuItemsRepositoryTest extends AbstractIntegrationTest
{

   @Autowired
   MenuItemsRepository repository;

}

Lets write a integration test for the findAncestors method from the MenuItemsRepository:

@Test
public void whenFindAncestors()
{
   String value;
   value = "New";
   MenuItems root = MenuItems.builder().parent(null).depth(0).node(true)
      .value(value).build();

   value = "JPA";
   MenuItems newJpa = MenuItems.builder().parent(root).value(value)
      .node(true).depth(1).build();

   MenuItems savedRoot = repository.save(root);
   MenuItems savedNewJpa = repository.save(newJpa);

   List<MenuItems> newJpaList = repository.findByValue(value);
   assertNotNull(newJpaList);
   assertEquals(1, newJpaList.size());

   MenuItems firstMenuItem = newJpaList.get(0);
   assertEquals(savedNewJpa, firstMenuItem);
   MenuItems parent = firstMenuItem.getParent();
   assertEquals(savedRoot, parent);

   value = "Project";
   MenuItems newProject = MenuItems.builder().parent(root).value(value)
      .node(true).depth(1).build();
   MenuItems savedNewProject = repository.save(newProject);

   List<MenuItems> ancestors = repository.findAncestors(newProject.getId());
   assertNotNull(ancestors);
   ancestors.remove(savedNewProject);
   assertEquals(1, ancestors.size());
   assertEquals(savedRoot, ancestors.get(0));
}

In the repository are more integration tests for the MenuItemsService. Have a look at the example github repository for the integration tests from the MenuItemsService.

All source code from the example github repository is deployed under the MIT-License. So you can copy or modify and use it in private and in commercial projects or products.

Thanks for reading.