使用JPA（TreeView）在同一個表中無限的分層子父關系

我正在嘗試創建一個“類別和子類別”物體，我試圖做一些研究，但我找不到一個好的解決方案，我需要了解如何為這個物體建模并得到以下結果！能夠以這種樹視圖格式檢索資料。

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

}

uj5u.com熱心網友回復：

如果我們仔細設計我們的模型，我們可以在單個資料庫表（Entity，in spring data jpa）中構建這種遞回樹狀結構，并通過兩個資料庫呼叫將整個樹獲取到葉節點。

在開始表設計/物體建模之前，讓我們總結一些事實

1. 每個類別如果是子類別，則應該有一個父類別，否則該類別是根類別

2. 作為子類別的每個類別都必須有一個根類別。

類別.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);
}

類別控制器.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的POJO（不是物體）并將父類別id＆復制name到該POJO中并將其設定為相應的子類別。

標籤：爪哇 sql 春天 jpa

上一篇：如何在sed中用定義數量的不同字符替換一個字符？

下一篇：從Postgres的日期范圍中獲取星期幾