CRUD Operations on Deeply Nested Comments: Scalable Spring Boot and Spring Data approach

CRUD Operations on Deeply Nested Comments: Scalable Spring Boot and Spring Data approach

In this post, I present CRUD operations on deeply nested comments, stored in a relational database via Spring Boot and Spring Data. The approach is scalable but uses only JPQL and very few pessimistic locks.

This post continues my previous post on the subject. In that post I describe the data structures, schema, data transfer object, indexing algorithm, filtering algorithms; also in that post, I review the literature and analyze complexity. Here, I show the actual CRUD operations. The approach is scalable, JPQL-only, and uses very few pessimistic locks. Spring Initializr code can be found here. Let's recall the requirements for this task.

The Requirements

We build a CRM system for a mid-size online education center. The system is implemented with Spring Boot 2, H2(development), and (for now) Ms-SQL(production) technology. Among many functionalities, the system must be able to create, read, update, and delete nested comments from/to a relational database. The functional requirements for the comment functionality are: 

  1. The most common operations are to find all descendants of a particular comment and to find all root comments,
  2. All new and edited comments are moderated before being published and saved into the database,
  3. If a comment is deleted, all its child comments are deleted as well,
  4. The system should support full-text search over comments' content.

We assume the following about the comments:

  1. The comments are text strings no more than a certain length (no pictures or video fragments for now),
  2. Every comment has 0 or 1 parent,
  3. Most of the comments are questions/answers/hints on assignments and classes, so there going to be a lot more reads than writes,
  4. Ones saved, the comments are rarely updated and even more rarely, if ever, deleted,
  5. The older the comment gets, the lesser it is commented; the total number of child comments of a particular comment rarely exceeds a certain (large) number; the number depends on the depth of the comment.
  6. For every root comment, there are a lot more sub root comments. This can be achieved, for example, if only users of a certain role or reputation can create root comments.
  7. The majority of the comments are just few levels deep. Much deeper comments are rare.

The following are the implementation details for the comment functionality:

  1. the functionalities need to be implemented with JPQL only and no native queries,
  2. there should be as few pessimistic locks as possible.

These requirements are not easy to satisfy simultaneously.

The Challenges

To implement these requirements, we face the following challenges:

  1. The data structures must be compatible with the relational database (rows and columns), yet implement a deeply nested tree (this challenge is addressed in Part 1).
  2. There should be efficient indexes to quickly find all children of a particular comment, yet no dialect-specific database features should be used (this challenge is addressed in Part 1).
  3. If all comments are stored in a single table, all root comments can not be found quickly without advanced dialect-specific database features. Yet, if all root comments are stored in a separate table, we have to use transactions (and pessimistic locks).

Notice, that if we use a JPQL-only approach, then transactions are inevitable since we have to process at least some data on the server side. To make as few transactions as possible, we need to separate methods, where transactions are absolutely necessary, from methods that can work without transactions. Let's see how to use our requirements, data structures, and algorithms to achieve this.

spring data jpa spring boot 2.2 hierarchical data jpql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Spring Boot 2.3.2 available now

On behalf of the team and everyone who has contributed, I’m happy to announce that Spring Boot 2.3.2 has been released and is now available from repo.spring.io and Maven Central. This release includes 88 bug fixes, enhancements, documentation improvements, and dependency upgrades. Thanks to all those who have contributed with issue reports and pull requests.

Data Auditing With Spring Data R2dbc

For most of Spring developers, I think you are familiar with the simple auditing features in Spring Data project, but in the past years, it only works with the blocking APIs. The long-awaited Reactive AuditorAware support will be available in the new Spring Data release train.

Spring Boot with PostgreSQL and Docker Compose | RESTful CRUD API Example | Spring Data JPA Auditing

In this video, You will learn how to create a Spring Boot + JPA/Hibernate + PostgreSQL RESTful CRUD API Example with a PostgreSQL database to run inside a Docker container and how to auditing, tracking and logging user activity across the application project by using JPA Auditing

Spring Boot With Spring Data JPA

Welcome to the Spring Boot with Spring Data JPA tutorial! In this tutorial, we are going to see how Spring Data JPA provides complete abstraction over the DAO layer. We don’t need to write the implementation for the DAO layer anymore; Spring Data auto-generates the implementation DAO implementations.

Applications Of Data Science On 3D Imagery Data

The agenda of the talk included an introduction to 3D data, its applications and case studies, 3D data alignment and more.