使用JPA (TreeView)在同一个表中无限层次子父关系

Question

我正在尝试创建一个"类别和子类别"实体，我试着做一些研究，但我找不到一个好的解决方案，我需要一个灯，我怎么能建模这个实体，并有下面的结果!能够以这种树视图格式检索数据。

{
"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: [

]
}
]
}
]
}
]

}

Answer 1

如果我们仔细设计我们的模型，我们可以在单个数据库表(spring data jpa中的实体)中构造这种递归树状结构，并通过两次数据库调用获取整个树直到叶子节点。

在开始表设计/实体建模之前，让我们总结一些事实

1. 如果是子类别，则每个类别都应该有父类别，否则该类别为根类别

2. 每一个子类必须有一个根类

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;
}
}

样本数据集

-- 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);

生成响应

[
{
"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": []
}
]
}
]

根据你们的样本回复，我故意跳过了parent，因为在正文中添加父元素会不必要地增加回复的大小。

如果您确实需要在所有子类别中使用parent键，那么您必须引入另一个包含id&name的父类，并复制父类id&将name放到POJO中，并将其设置为相应的子类别。