I am trying to create a "Category and Subcategories" Entity, i tried to do some research but i couldn't find a good solution, i need a light on how can i model this Entity and have the bellow result! Be able to retrieve the data in this tree view format.

   {
"id": 1,
"name": "Account 1",
"children": [
    {
        "id": 2,
        "name": "Account 1.1",
        "parent": {
            "id": 1,
            "name": "Account 1"
        }
    },
    {
        "id": 3,
        "name": "Account 1.2",
        "parent": {
            "id": 1,
            "name": "Account 1"
        },
        children: [
            {
                "id": 4,
                "name": "Account 1.2.1",
                "children": [
                    {
                        "id": 5,
                        "name": "Account 1.2.1.1",
                        "parent": {
                            "id": 4,
                            "name": "Account 1.2.1"
                        }
                    },
                    {
                        "id": 6,
                        "name": "Account 1.2.1.2",
                        "parent": {
                            "id": 4,
                            "name": "Account 1.2.1"
                        },
                        children: [
                        
                        ]
                    }
                ]
            }
        ]
    }
]

}

CodePudding user response：

We can construct this recursive tree like structure in a single database table ( Entity, in spring data jpa ) and fetch the whole tree upto leaf nodes with two database call, if we design our model carefully.

Before start table design / Entity modeling lets boil down some facts

1. Every Category should have a parent category if it is a subcategory, otherwise the category is the root category

2. Every Category which is subcategory must have a root category.

Category.java

@Entity
public class Category {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long categoryId;

    @Column(nullable = false)
    public String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_category_id")
    @JsonIgnore
    public Category parentCategory;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "root_category_id")
    @JsonIgnore
    public Category rootCategory;
    
    @Transient
    public List<Category> childrens = new ArrayList<Category>();
}

CategoryRepository.java

@Repository
public interface CategoryRepository extends JpaRepository<Category, Long> {
    
    @Query("SELECT category FROM Category category "
              " WHERE category.parentCategory.categoryId IS NULL")
    public List<Category> findAllRoots();

    @Query("SELECT category FROM Category category"
              " WHERE category.rootCategory.categoryId IN :rootIds ")
    public List<Category> findAllSubCategoriesInRoot(@Param("rootIds") List<Long> rootIds);
}

CategoryController.java

@RestController
public class CategoryController {
    
    @Autowired
    public CategoryRepository categoryRepository;

    @GetMapping("/categories")
    @Transactional(readOnly = true)
    public List<Category> getCategories() {
        List<Category> rootCategories = categoryRepository.findAllRoots(); // first db call

        // Now Find all the subcategories
        List<Long> rootCategoryIds = rootCategories.stream().map(Category::getCategoryId).collect(Collectors.toList());
        List<Category> subCategories = categoryRepository.findAllSubCategoriesInRoot(rootCategoryIds); // second db call

        subCategories.forEach(subCategory -> {
            subCategory.getParentCategory().getChildrens().add(subCategory); // no further db call, because everyone inside the root is in the persistence context.
        });

        return rootCategories;
    }
}

Sample dataset

-- root
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (1, 'A', null, null);

-- first level
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (2, 'B', 1, 1);
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (3, 'C', 1, 1);

-- second level
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (4, 'D', 2, 1);
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (5, 'E', 3, 1);
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (6, 'F', 3, 1);

-- another root
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (7, 'P', null, null);

-- first level of another root
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (8, 'Q', 7, 7);
INSERT INTO category (category_id, name, parent_category_id, root_category_id) VALUES (9, 'R', 7, 7);

Generated response

[
    {
        "categoryId": 1,
        "name": "A",
        "childrens": [
            {
                "categoryId": 2,
                "name": "B",
                "childrens": [
                    {
                        "categoryId": 4,
                        "name": "D",
                        "childrens": []
                    }
                ]
            },
            {
                "categoryId": 3,
                "name": "C",
                "childrens": [
                    {
                        "categoryId": 5,
                        "name": "E",
                        "childrens": []
                    },
                    {
                        "categoryId": 6,
                        "name": "F",
                        "childrens": []
                    }
                ]
            }
        ]
    },
    {
        "categoryId": 7,
        "name": "P",
        "childrens": [
            {
                "categoryId": 8,
                "name": "Q",
                "childrens": []
            },
            {
                "categoryId": 9,
                "name": "R",
                "childrens": []
            }
        ]
    }
]

I intentionally skipped the parent as per your sample response because adding the parent in the body will unnecessarily increase the response size.

If you really need that parent key in all the sub categories, then you have to introduce another POJO (not entity) containing the id & name of the parent category and copy the parent category id & name into that POJO and set it to the corresponding sub category.