// simplified
@Data
@Entity
@Table(name = "comments")
@EntityListeners(AuditingEntityListener.class)
public class Comment {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_id")
    private Comment parent;

    @OneToMany(mappedBy = "parent", orphanRemoval = true)
    private List<Comment> children = new ArrayList<>();

}

FetchType.EAGER

public Comment getCommentWithChildren(Long commentId) {
    return commentRepository.findById(commentId).orElse(null);
}

Derived Method

public List<Comment> findSubcomments(Long commentId) {
        Comment rootComment = commentRepository.findById(commentId).orElse(null);
        if (rootComment == null) {
            return null; // or throw an exception if preferred
        }
        fetchChildren(rootComment);
        return List.of(rootComment);
    }

    private void fetchChildren(Comment parent) {
        List<Comment> children = commentRepository.findByParentId(parent.getId());
        parent.setChildren(children);
        for (Comment child : children) {
            fetchChildren(child);
        }
    }

Common Table Expressions (CTEs)

Spring Data JPA does not support recursive queries out-of-the-box, but you can use native queries to achieve this. Below is an example of how you can perform a recursive query using a native query in Spring Data JPA.

@Query(value = "WITH RECURSIVE Subcomments AS (" +
               "    SELECT c.id, c.content, c.parent_id " +
               "    FROM Comment c " +
               "    WHERE c.id = :commentId " +
               "    UNION ALL " +
               "    SELECT c.id, c.content, c.parent_id " +
               "    FROM Comment c " +
               "    INNER JOIN Subcomments s ON c.parent_id = s.id" +
               ") " +
               "SELECT * FROM Subcomments", nativeQuery = true)
List<Comment> findSubcomments(@Param("commentId") Long commentId);

The fetched result is a flatten list of Comment.

Performance Considerations

CTEs

Pros:

• Efficiency: Fetching the entire hierarchy in a single query is efficient and reduces the number of database round-trips.
• Control: You have fine-grained control over how the data is processed and structured in the application layer.

Cons:

• Complexity: The reconstruction of the recursive structure in the application layer can be complex and error-prone.
• Memory Usage: Depending on the size of the dataset, holding the entire hierarchy in memory for reconstruction can be memory-intensive.
• Performance Overhead: The reconstruction process itself can introduce performance overhead, especially if the hierarchy is deep or has many nodes.

Performance Comparison

1. Native Query and CTEs with Reconstruction:

   • Pros:
     • Efficiently fetches the entire hierarchy in a single query.
     • Minimizes the number of database round-trips.
   • Cons:
     • Complexity in reconstructing the hierarchy in the application layer.
     • Potential memory usage concerns if the dataset is large.
     • Additional performance overhead due to the reconstruction process.

2. FetchType.EAGER:

   • Pros:
     • Simplifies data access by automatically fetching children.
     • Avoids the need for manual reconstruction.
   • Cons:
     • Performance overhead due to eager fetching, especially with large datasets.
     • Risk of the N+1 query problem.
     • Increased memory usage due to loading entire collections.

3. Derived Methods and Manually Assembling:

   • Pros:
     • Flexibility and simplicity in using JPA abstractions.
     • Allows for fine-grained control over data fetching and processing.
   • Cons:
     • Potential performance overhead due to multiple queries.
     • Complexity in manually assembling the hierarchical structure.
     • Risk of performance and consistency issues if not managed carefully.

Recommendations

• For Large, Complex Hierarchies: Use native queries with CTEs for efficient data fetching. Be prepared to handle the complexity of reconstructing the hierarchy in the application layer. This approach provides the best performance but requires careful implementation.
• For Small to Medium Hierarchies: Use FetchType.EAGER for convenience and simplicity. Monitor performance and memory usage to ensure it remains within acceptable limits.
• For Flexible, Maintainable Code: Use derived methods and manually assemble the hierarchical structure if the performance impact is manageable. This approach offers a good balance between flexibility and simplicity.

Conclusion

• Native Query and CTEs with Reconstruction: Best for performance but requires careful handling of the reconstruction phase.
• FetchType.EAGER: Convenient but can lead to performance and memory overhead.
• Derived Methods and Manual Assembly: Flexible and maintainable with acceptable performance for smaller datasets.

Ultimately, the choice depends on your specific use case, dataset size, and performance requirements. Always profile and benchmark your application to make an informed decision based on actual performance metrics.