使用 PostgreSQL 和 Spring Data JPA 的分層數據-java教程-PHP中文網

種樹的人，
種下希望。
種一棵樹露西‧拉科姆（Lucy Larcom）？

簡介

在這篇文章中，我將向您展示一些用於管理表示為樹資料結構的分層資料的選項。當您需要實現以下內容時，這是自然的方法：

檔案系統路徑
組織架構圖
論壇評論
更現代的主題：RAG 應用程式的小到大檢索

如果您已經知道圖是什麼，那麼樹基本上就是一個沒有任何循環的圖。您可以像這樣直觀地代表一個人。

Hierarchical data with PostgreSQL and Spring Data JPA

在關聯式資料庫中儲存樹有多種選擇。在下面的部分中，我將向您展示其中三個：

鄰接表
物化路徑
巢狀集

這篇文章將分成兩個部分。在第一個中，介紹了替代方案，您將了解如何載入和儲存資料 - 基礎知識。排除這一點，在第二部分中，重點更多地放在它們的比較和權衡上，例如，我想看看數據量增加時會發生什麼以及適當的索引策略是什麼。

如果您有興趣查看，您將在下面的部分中看到的所有程式碼都可以在這裡找到。

正在運行的用例將是員工及其經理的用例，每個用例的ID將與您在上面顯示的樹視覺化中看到的完全相同。

當地環境

我正在使用最近發布的 Postgres 17 和 Testcontainers。這為我提供了可重複的設定。例如，我們可以使用初始化 SQL 腳本自動建立包含必要表的 Postgres 資料庫，並填入一些測試資料。

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

登入後複製

讓我們開始看看第一種方法。

1.鄰接表模型

這是管理分層資料的第一個解決方案，因此我們可以預期它仍然廣泛存在於程式碼庫中，因此您有時可能會遇到它。我們的想法是，我們將經理的 ID（或更一般地說，父 ID）儲存在同一行中。一旦我們查看表結構，很快就會清楚。

模式

鄰接清單選項對應的表格如下所示：

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

登入後複製

除了上述之外，為了確保資料完整性，我們還應該編寫約束檢查，至少確保以下幾點：

每個節點都有一個父節點
沒有循環

產生測試數據

特別是對於本系列的第 2 部分，我們需要一種方法來產生填充架構所需的盡可能多的資料。為了清楚起見，讓我們先一步一步地進行，然後再遞歸地進行。

步步

我們首先簡單地在層級結構中明確插入三個層級的員工。

您可能已經了解 Postgres 中的 CTE - 它們是在主查詢上下文中執行的輔助命名查詢。下面，你可以看到我是如何在之前的關卡的基礎上建立每個關卡的。

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

登入後複製

讓我們驗證到目前為止它是否按預期工作，並為此目的進行計數以查看已插入了多少個元素。您可以將其與我在本文開頭展示的樹視覺化中的節點數進行比較。

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

登入後複製

看起來還不錯！三個級別，總共有 15 個節點。

是時候轉向遞歸方法了。

遞迴

編寫遞歸查詢遵循標準程序。我們定義一個基本步驟和一個遞歸步驟，然後使用 union all 將它們「連接」到彼此。在運行時，Postgres 將遵循這個配方並產生我們所有的結果。看看吧。

with root as (
  insert into 
    employees(manager_id, name)
      select 
        null, 
        'root' || md5(random()::text) 
      from  
        generate_series(1, 1) g
      returning 
        employees.id
  ),
  first_level as (
    insert into 
      employees(manager_id, name)
        select 
          root.id, 
          'first_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          root
        returning 
          employees.id
  ),
  second_level as (
    insert into 
      employees(manager_id, name)
        select 
          first_level.id, 
          'second_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          first_level
        returning 
          employees.id
  )
insert into 
  employees(manager_id, name)
select 
  second_level.id, 
  'third_level' || md5(random()::text) 
from 
  generate_series(1, 2) g, 
  second_level;

登入後複製

運行後，我們再統計一下，看看是否插入了相同數量的元素。

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

登入後複製

酷！我們正在做生意。我們現在可以用我們想要的任意數量的等級和元素填充模式，從而完全控制插入的體積。不用擔心，如果現在遞歸查詢看起來仍然有點困難，我們稍後會在編寫查詢來檢索資料時重新審視它們。

現在，讓我們繼續看一下可用於將表格對應到 Java 類別的 Hibernate 實體。

create temporary sequence employees_id_seq;
insert into employees (id, manager_id, name)
with recursive t(id, parent_id, level, name) AS
(
  select 
    nextval('employees_id_seq')::bigint,
    null::bigint, 
    1, 
    'root' from generate_series(1,1) g

    union all

    select 
      nextval('employees_id_seq')::bigint, 
      t.id, 
      level+1, 
      'level' || level || '-' || md5(random()::text) 
    from 
      t, 
      generate_series(1,2) g
    where 
      level < 4
)
select 
  id, 
  parent_id, 
  name 
from 
  t;
drop sequence employees_id_seq;

登入後複製

沒什麼特別的，只是經理和員工之間的一對多關係。你看到了這一切的到來。我們開始查詢吧。

後裔

所有經理的下屬

為了檢索 ID 所引用的特定經理的下屬的所有員工，我們將再次編寫一個遞歸查詢。您將再次看到基本步驟和與基本步驟連結的遞歸步驟。然後 Postgres 將重複此操作並檢索查詢的所有相關行。我們以 ID = 2 的員工為例。這是一種視覺表示，希望可以更容易理解我剛才描述的內容。我沒有包含所有結果，僅包含前幾個結果。

Hierarchical data with PostgreSQL and Spring Data JPA

這是用於查詢後代的 JPQL 查詢：

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

登入後複製

在諸如上述查詢之類的查詢中，為了使它們更清晰並避免需要寫入我們將寫入結果的記錄的完全限定名稱，我們可以使用 hypersistence-utils 庫編寫一個 ClassImportIntegratorProvider：

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

登入後複製

查看產生的查詢

它可以運作，但讓我們更深入地了解 Hibernate 產生的內容。了解幕後發生的事情總是好的，否則我們可能會因每個用戶請求而導致效率低下，這會增加。

我們必須使用以下設定啟動 Spring Boot 應用程式：

with root as (
  insert into 
    employees(manager_id, name)
      select 
        null, 
        'root' || md5(random()::text) 
      from  
        generate_series(1, 1) g
      returning 
        employees.id
  ),
  first_level as (
    insert into 
      employees(manager_id, name)
        select 
          root.id, 
          'first_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          root
        returning 
          employees.id
  ),
  second_level as (
    insert into 
      employees(manager_id, name)
        select 
          first_level.id, 
          'second_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          first_level
        returning 
          employees.id
  )
insert into 
  employees(manager_id, name)
select 
  second_level.id, 
  'third_level' || md5(random()::text) 
from 
  generate_series(1, 2) g, 
  second_level;

登入後複製

好啦，我們來看看吧。這是 Hibernate 產生的後代的查詢。

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

登入後複製

嗯 - 看起來比預期的要複雜一些！讓我們看看是否可以稍微簡化一下，記住我之前向您展示的有關基本步驟以及與基本步驟相關的遞歸步驟的圖片。我們不需要做更多的事情。看看你對以下的看法。

create temporary sequence employees_id_seq;
insert into employees (id, manager_id, name)
with recursive t(id, parent_id, level, name) AS
(
  select 
    nextval('employees_id_seq')::bigint,
    null::bigint, 
    1, 
    'root' from generate_series(1,1) g

    union all

    select 
      nextval('employees_id_seq')::bigint, 
      t.id, 
      level+1, 
      'level' || level || '-' || md5(random()::text) 
    from 
      t, 
      generate_series(1,2) g
    where 
      level < 4
)
select 
  id, 
  parent_id, 
  name 
from 
  t;
drop sequence employees_id_seq;

登入後複製

好多了！我們刪除了一些不必要的連接。這預計會使查詢速度更快，因為要做的工作更少。

最終結果

最後一步，讓我們清理查詢並將 Hibernate 新增的表格名稱替換為更易於人類閱讀的名稱。

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

登入後複製

好吧，是時候看看我們如何「爬上」樹了。

祖先

鏈條上的所有經理

我們先試著寫下取得 ID = 14 的員工的經理的概念步驟。

Hierarchical data with PostgreSQL and Spring Data JPA

看起來非常像後代的，只是基礎步驟和遞歸步驟之間的連結是另一種方式。

我們可以寫如下所示的 JPQL 查詢：

@Entity
@Table(name = "employees")
@Getter
@Setter
public class Employee {
    @Id
    private Long id;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "manager_id")
    private Employee manager;

    @OneToMany(
            mappedBy = "parent",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<Employee> employees = new ArrayList<>();
}

登入後複製

就是這樣！我查看了產生的 SQL 查詢，但找不到任何可以刪除的額外命令。是時候繼續方法 2了。

2. 物化路徑

ltree 是一個 Postgres 擴展，我們可以使用分層樹結構作為物化路徑（從樹的頂部開始）。例如，我們將這樣記錄葉節點 8 的路徑：1.2.4.8。它附帶了幾個有用的功能。我們可以將它用作表格列：

return entityManager.createQuery("""
 with employeeRoot as (
  select
    employee.employees employee
  from
    Employee employee
  where
    employee.id = :employeeId

  union all

  select
    employee.employees employee
  from
    Employee employee
  join
    employeeRoot root ON employee = root.employee
  order by
    employee.id
  )
  select 
    new Employee(
     root.employee.id
   )
  from 
  employeeRoot root
 """, Employee.class
)
 .setParameter("employeeId", employeeId)
 .getResultList();

登入後複製

為了用測試資料填入上表，我採取的方法基本上是使用以下 SQL 命令從用於您之前看到的鄰接列表的表中遷移產生的資料。這又是一個遞歸查詢，每一步都會將元素收集到累加器中。

public class ClassImportIntegratorProvider implements IntegratorProvider {
    @Override
    public List<Integrator> getIntegrators() {
        return List.of(
                new ClassImportIntegrator(
                        singletonList(
                                Employee.class
                        )
                )
        );
    }
}

登入後複製

這是上述指令產生的條目。

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

登入後複製

我們可以繼續寫 Hibernate 實體。為了對應 ltree 類型的列，我實作了 UserType。然後我可以使用 @Type(LTreeType.class) 來映射路徑欄位：

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

登入後複製

我們已準備好編寫一些查詢。在本機 SQL 中，它看起來如下所示：

with root as (
  insert into 
    employees(manager_id, name)
      select 
        null, 
        'root' || md5(random()::text) 
      from  
        generate_series(1, 1) g
      returning 
        employees.id
  ),
  first_level as (
    insert into 
      employees(manager_id, name)
        select 
          root.id, 
          'first_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          root
        returning 
          employees.id
  ),
  second_level as (
    insert into 
      employees(manager_id, name)
        select 
          first_level.id, 
          'second_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          first_level
        returning 
          employees.id
  )
insert into 
  employees(manager_id, name)
select 
  second_level.id, 
  'third_level' || md5(random()::text) 
from 
  generate_series(1, 2) g, 
  second_level;

登入後複製

但是讓我們用 JPQL 來寫查詢。為此，我們必須先編寫自訂的 StandardSQLFunction。這將允許我們定義 Postgres 本機運算子的替換。

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

登入後複製

然後我們必須將其註冊為 FunctionContributor，如下所示：

create temporary sequence employees_id_seq;
insert into employees (id, manager_id, name)
with recursive t(id, parent_id, level, name) AS
(
  select 
    nextval('employees_id_seq')::bigint,
    null::bigint, 
    1, 
    'root' from generate_series(1,1) g

    union all

    select 
      nextval('employees_id_seq')::bigint, 
      t.id, 
      level+1, 
      'level' || level || '-' || md5(random()::text) 
    from 
      t, 
      generate_series(1,2) g
    where 
      level < 4
)
select 
  id, 
  parent_id, 
  name 
from 
  t;
drop sequence employees_id_seq;

登入後複製

最後一步是在 META-INF/services 資料夾中建立一個名為 org.hibernate.boot.model.FunctionContributor 的資源文件，我們將在其中新增一行包含上面類別的完全限定名稱。

好吧，酷！我們終於可以編寫以下查詢：

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

登入後複製

例如，我們可以這樣呼叫該方法來檢索所有包含 ID = 2 的路徑：

@Entity
@Table(name = "employees")
@Getter
@Setter
public class Employee {
    @Id
    private Long id;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "manager_id")
    private Employee manager;

    @OneToMany(
            mappedBy = "parent",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<Employee> employees = new ArrayList<>();
}

登入後複製

Postgres 提供了一系列用來處理 ltree 的函式。您可以在官方文件頁面中找到它們。此外，還有一個有用的備忘單。

為我們的模式添加約束以確保資料一致性非常重要 - 這是我在這個主題上找到的一個很好的資源。

3. 嵌套集

最容易理解的是使用顯示直覺的圖像。在樹的每個節點上，除了其 ID 之外，我們還有一個額外的「左」列和一個「右」列。規則是所有孩子的左值和右值都位於父母的左值和右值之間。

Hierarchical data with PostgreSQL and Spring Data JPA

這是代表上面樹的表格結構。

return entityManager.createQuery("""
 with employeeRoot as (
  select
    employee.employees employee
  from
    Employee employee
  where
    employee.id = :employeeId

  union all

  select
    employee.employees employee
  from
    Employee employee
  join
    employeeRoot root ON employee = root.employee
  order by
    employee.id
  )
  select 
    new Employee(
     root.employee.id
   )
  from 
  employeeRoot root
 """, Employee.class
)
 .setParameter("employeeId", employeeId)
 .getResultList();

登入後複製

為了填滿該表，我已將 Joe Celko 的「聰明人的 SQL」書中的腳本轉換為 Postgres 語法。這是：

public class ClassImportIntegratorProvider implements IntegratorProvider {
    @Override
    public List<Integrator> getIntegrators() {
        return List.of(
                new ClassImportIntegrator(
                        singletonList(
                                Employee.class
                        )
                )
        );
    }
}

登入後複製

好的，我準備好做一些查詢了。這是找回祖先的方法。

@DynamicPropertySource
static void registerPgProperties(DynamicPropertyRegistry registry) {
    registry.add("spring.jpa.show_sql", () -> true);
}

登入後複製

對於後代，我們首先必須檢索左和右，然後我們可以使用以下查詢。

with recursive employeeRoot (employee_id) as 
(
select 
  e1_0.id
from 
  employees eal1_0
join 
  employees e1_0 on eal1_0.id = e1_0.manager_id
where eal1_0.id=?

union all

(
select 
  e2_0.id
from 
  employees eal2_0
join 
  employeeRoot root1_0 on eal2_0.id = root1_0.employee_id
join 
  employees e2_0 on eal2_0.id = e2_0.manager_id
order by 
  eal2_0.id
)
)
select 
  root2_0.employee_id
from 
  employeeRoot root2_0

登入後複製

就是這樣！您已經了解如何透過所有三種方法在樹上向上或向下移動。我希望您喜歡這次旅程並發現它很有用。

Postgres 與文檔/圖形資料庫

我們在上面的範例中使用的資料庫是PostgreSQL。它不是唯一的選擇，例如，您可能想知道為什麼不選擇像 MongoDB 這樣的文件資料庫，或者像 Neo4j 這樣的圖形資料庫，因為它們實際上是在考慮到這種類型的工作負載的情況下建立的。

很可能，您已經在 Postgres 中利用事務保證的關係模型中獲得了真實資料來源。在這種情況下，您應該先檢查 Postgres 本身處理輔助使用案例的情況，以便將所有內容放在一個地方。這樣，您將避免啟動和維護/升級新的單獨的專用資料儲存所需的成本和操作複雜性增加，以及需要熟悉它。

結論

有幾個有趣的選項可用於在資料庫應用程式中對分層資料進行建模。在這篇文章中，我向您展示了三種方法。請繼續關注第 2 部分，我們將比較它們，並看看更大數據量會發生什麼。

參考

https://dev.to/yugabyte/learn-how-to-write-sql-recursive-cte-in-5-steps-3n88
https://vladmihalcea.com/hibernate-with-recursive-query/
https://vladmihalcea.com/dto-projection-jpa-query/
https://tudborg.com/posts/2022-02-04-postgres-hierarchical-data-with-ltree/
https://aregall.tech/hibernate-6-custom-functions#heading-implementing-a-custom-function
https://www.amazon.co.uk/Joe-Celkos-SQL-Smarties-Programming/dp/0128007613
https://madecurious.com/curiosities/trees-in-postgresql/
https://schinckel.net/2014/11/27/postgres-tree-shootout-part-2:-adjacency-list-using-ctes/

以上是使用 PostgreSQL 和 Spring Data JPA 的分層數據的詳細內容。更多資訊請關注PHP中文網其他相關文章！