Introduction

In this article, we are going to see how the Hibernate WITH RECURSIVE query works and how we can use it to fetch hierarchical data structures.

Domain Model

Let’s consider we have the following post and post_comment tables:

The post_comment child table has a one-to-many table relationship with the parent post table via the post_id Foreign Key column in the post_comment table.

Additionally, the post_comment table has a self-referencing Foreign Key via the parent_id column, which can reference a record from the same post_comment table.

The Post entity is mapped as follows:

@Entity(name = "Post")
@Table(name = "post")
public class Post {
⠀
    @Id
    private Long id;
⠀
    @Column(length = 100)
    private String title;
}

And the PostComment child entity is mapped like this:

@Entity(name = "PostComment")
@Table(name = "post_comment")
public class PostComment {
⠀
    @Id
    @GeneratedValue(
        generator = "post_comment_seq", 
        strategy = GenerationType.SEQUENCE
    )
    @SequenceGenerator(
        name = "post_comment_seq", 
        allocationSize = 1
    )
    private Long id;
⠀
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "post_id")
    private Post post;
⠀
    @Column(name = "created_on")
    private LocalDateTime createdOn;
⠀
    @Column(length = 250)
    private String review;
⠀
    private int score;
⠀
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_id")
    private PostComment parent;
⠀
    @OneToMany(
        mappedBy = "parent",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    private List<PostComment> children = new ArrayList<>();
}

After mapping the entities, let’s add the following Post and PostComment entities:

Post post = new Post()
    .setId(1L)
    .setTitle("Post 1");

entityManager.persist(post);

entityManager.persist(
    new PostComment()
        .setPost(post)
        .setCreatedOn(
            LocalDateTime.of(2024, 6, 13, 12, 23, 5)
        )
        .setScore(1)
        .setReview("Comment 1")
        .addChild(
            new PostComment()
                .setPost(post)
                .setCreatedOn(
                    LocalDateTime.of(2024, 6, 14, 13, 23, 10)
                )
                .setScore(2)
                .setReview("Comment 1.1")
        )
        .addChild(
            new PostComment()
                .setPost(post)
                .setCreatedOn(
                    LocalDateTime.of(2024, 6, 14, 15, 45, 15)
                )
                .setScore(2)
                .setReview("Comment 1.2")
                .addChild(
                    new PostComment()
                        .setPost(post)
                        .setCreatedOn(
                            LocalDateTime.of(2024, 6, 15, 10, 15, 20)
                        )
                        .setScore(1)
                        .setReview("Comment 1.2.1")
                )
        )
);

Hibernate WITH RECURSIVE query

Our use case requires us to fetch an entire hierarchy of post_comment entities that descend from a provided parent post_comment table record.

As I explained in this article, we can use a WITH RECURSIVE native SQL query to fetch a hierarchy of table records, and this solution works fine with any version of Hibernate.

However, since Hibernate 6, we can also use JPQL query to fetch hierarchical data structures using the WITH clause, as demonstrated by the following query:

List<PostCommentRecord>  postComments = entityManager.createQuery("""
    WITH postCommentChildHierarchy AS (
      SELECT pc.children pc
      FROM PostComment pc
      WHERE pc.id = :commentId
⠀
      UNION ALL
⠀
      SELECT pc.children pc
      FROM PostComment pc
      JOIN postCommentChildHierarchy pch ON pc = pch.pc
      ORDER BY pc.id
    )
    SELECT new PostCommentRecord(
        pch.pc.id,
        pch.pc.createdOn,
        pch.pc.review,
        pch.pc.score,
        pch.pc.parent.id
    )
    FROM postCommentChildHierarchy pch
    """, PostCommentRecord.class)
.setParameter("commentId", 1L)
.getResultList();
⠀
assertEquals(3, postComments.size());
assertEquals("Comment 1.1", postComments.get(0).review);
assertEquals("Comment 1.2", postComments.get(1).review);
assertEquals("Comment 1.2.1", postComments.get(2).review);

The Hibernate WITH RECURSIVE query is contracted like this:

. The WITH clause takes an alias that we can reference further down in our SQL query.
. The first query inside the WITH clause is called the anchor member and defines the root records that will be added to our postCommentChildHierarchy virtual table.
. The second query inside the WITH clause is called the recursive member and will be repeated until it produces an empty result set. The records produced by executing the recursive member are going to be added to the postCommentChildHierarchy virtual table.
. The last query selects the records from the postCommentChildHierarchy virtual table and maps the records to the PostCommentRecord.

In order to use the simple class name of the PostCommentRecord in the constructor expression, we used the ClassImportIntegrator from the Hypersistence Utils project like this:

properties.put(
    JpaSettings.INTEGRATOR_PROVIDER,
    (IntegratorProvider) () -> Collections.singletonList(
        new ClassImportIntegrator(
            List.of(
                PostCommentRecord.class
            )
        )
    )
);

When running the WITH RECURSIVE query we created previously, Hibernate will execute the following SQL query:

WITH RECURSIVE postCommentChildHierarchy (pc_id) AS (
    SELECT c1_0.id
    FROM post_comment pc1_0
    JOIN post_comment c1_0 
        ON pc1_0.id = c1_0.parent_id
    WHERE pc1_0.id = 1
⠀
    UNION ALL
⠀
    (
    SELECT c2_0.id
    FROM post_comment pc2_0
    JOIN postCommentChildHierarchy pch1_0 
        ON pc2_0.id = pch1_0.pc_id
    JOIN post_comment c2_0 
        ON pc2_0.id = c2_0.parent_id
    ORDER BY pc2_0.id
    )
)
SELECT 
    pch2_0.pc_id, 
    pc4_0.created_on, 
    pc4_0.review, 
    pc4_0.score, 
    pc4_0.parent_id
FROM postCommentChildHierarchy pch2_0
JOIN post_comment pc4_0 
    ON pch2_0.pc_id = pc4_0.id

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Hibernate 6 provides a lot of improvements, such as Window Functions, Derived Tables, Common Table Expressions, or WITH RECURSIVE queries.

The Hibernate WITH RECURSIVE query is very convenient to traverse hierarchical table relations in an incremental fashion.

Insert details about how the information is going to be processed

DOWNLOAD NOW