Hierarchical Data Fetch in Spring and Hibernate

Question

I have 2 tables Account and Group both contain data in a hierarchy.

Example - (Just for reference I am using PostgresSQL)

Group

|------|----------|-------------------|
|  id  |   name   |  parent_group_id  |
|------|----------|-------------------|
|  1   |  Group1  |  null             |
|  2   |  Group2  |  1                |
|  3   |  Group3  |  2                |
|  4   |  Group4  |  1                |
|------|----------|-------------------|

Account
|----|----------|----------|
| id | name     | group_id |
|----|----------|----------|
| 1  | Account1 | 1        |
| 2  | Account2 | 1        |
| 3  | Account3 | 2        |
| 4  | Account4 | 3        |
| 4  | Account5 | 4        |
-----|----------|-----------

This account and group hierarchy can be many levels deep. I want to fetch all groups and accounts in an efficient way using Spring and Hibernate.

I want the output to be like -

{"name":"Group1","groups":[{"name":"Group4","groups":[],"accounts":[{"name":"Account5"}]},{"name":"Group2","groups":[{"name":"Group3","groups":[],"accounts":[{"name":"Account4"}]}],"accounts":[{"name":"Account3"}]}],"accounts":[{"name":"Account2"},{"name":"Account1"}]}

I have checked some articles but they are not recursive (means group inside a group and so on).

Looks like `JPA` doesn't support fetching recursive data. Please check this answer https://stackoverflow.com/a/3639538/3503019. — Akash, May 22 '20 at 20:53

Christian Beikov · Answer 1 · 2020-06-07T13:32:38.700

This is the perfect use case for Blaze-Persistence.

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. To model CTEs or recursive CTEs, which is what you need here, you first need to introduce a CTE entity that models the result type of the CTE.

@CTE
@Entity
public class GroupCTE {
  @Id Integer id;
}

A query for this could look like the following

List<Group> groups = criteriaBuilderFactory.create(entityManager, Group.class)
  .withRecursive(GroupCTE.class)
    .from(Group.class, "g1")
    .bind("id").select("g1.id")
    .where("g1.parent").isNull()
  .unionAll()
    .from(Group.class, "g2")
    .innerJoinOn(GroupCTE.class, "cte")
      .on("cte.id").eqExpression("g2.parent.id")
    .end()
    .bind("id").select("g2.id")
  .end()
  .from(Group.class, "g")
  .fetch("accounts", "groups")
  .where("g.id").in()
    .from(GroupCTE.class, "c")
    .select("c.id")
  .end()
  .getResultList();

This renders to SQL looking like the following

WITH RECURSIVE GroupCTE(id) AS (
    SELECT g1.id
    FROM Group g1
    WHERE g1.parent_group_id IS NULL
  UNION ALL
    SELECT g2.id
    FROM Group g2
    INNER JOIN GroupCTE cte ON g2.parent_group_id = cte.id
)
SELECT *
FROM Group g
LEFT JOIN Account a ON a.group_id = g.id
LEFT JOIN Group gsub ON gsub.parent_group_id = g.id
WHERE g.id IN (
  SELECT c.id
  FROM GroupCTE c
)

You can find out more about recursive CTEs in the documentation: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#recursive-ctes

I tried this but I am getting error at ```Parameter value [g2.parent.id] did not match expected type [java.lang.Integer (n/a)] ``` I have all id as Long in java and in postgres as bigint — Rajat Gupta, Jun 07 '20 at 08:47

Hierarchical Data Fetch in Spring and Hibernate

1 Answers1