A practical guide to implementing logical delete with Spring Data JPA, designed for clarity and real-world use.

The Full code is available on GitHub.

Fire

Photo by Manyu Varma on Unspash

Introduction

Data deletion can be permanent or logical. Logical deletion marks data as deleted without physically removing it from the database, making it useful for auditing and historical data retention. Hibernate Envers supports deletion auditing but can introduce complexity and performance overhead. (See this and some other use cases of Hibernate Envers Here).

In practice, Logical deletion involves adding columns to mark an entity as deleted. We’ll use deleted_at, deleted_by, and deleted_reason columns to store when an entity was deleted, who deleted it, and the reason for deletion.

Since most entities might require this information along with versioning and identification, we’ll create a base entity class that all entities can extend:

@Getter
@Setter
@MappedSuperclass
public abstract class AbstractEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Version
    @Column(nullable = false, columnDefinition = "integer DEFAULT 0")
    private Integer databaseVersion;

    @Column
    private LocalDateTime deletedAt;

    @Column
    private String deletedBy;

    @Column
    private String deletedReason;
}

Basic Implementation

The standard basic implementation is to use an update query to set the deleted columns:

public void deleteAuthorBasic(Integer id, String reason) {
    authorRepository.findById(id).ifPresent(author -> {
        author.setDeletedReason(reason);
        author.setDeletedAt(LocalDateTime.now());
        author.setDeletedBy("get current user from Security Context");
        authorRepository.save(author);
    });
}

This implementation is not ideal because:

1) Extra Database Call: Fetches the entity before updating, causing two round-trips to the database.

2) Bypassing JPA Lifecycle: The standard JPA delete method isn’t used, which can lead to issues with cascading and Standard JPA lifecycle events. (@PreRemove, @PostRemove will not be triggered)

3) Concurrency Issues: No version control on deletion, which could lead to race conditions.

4) Security Concerns: DeletedBy is set manually, which could be error-prone and insecure.

Let’s see how we can improve this implementation in the next section.

Effective Implementation with @SQLDelete and @SQLRestriction

A more efficient approach uses Hibernate’s @SQLDelete and @SQLRestriction annotations. Those annotations are used to override the default delete behavior and to exclude logically deleted entities from queries:

@Entity
@Table(name = "authors")
@SQLDelete(
    sql = "UPDATE authors SET deleted_at = CURRENT_TIMESTAMP, deleted_by = :current_user, " +
          "deleted_reason = :deleted_reason WHERE id = ? AND database_version = ?")
@SQLRestriction("deleted_at IS NULL")
public class Author extends AbstractEntity {
    // fields
}

As you can see, the delete query is an update with two parameters, the current user and the reason for deletion. The id and the database_version are used in the where clause and Hibernate will automatically set the values.

We can use a custom Hibernate Statement Inspector to replace the placeholder vars (current_user and deleted_reason) dynamically.

First of all, we need to register it in the application.yaml file:

spring:
  jpa:
    properties:
      hibernate:
        session_factory:
          statement_inspector: gae.piaz.logical.delete.config.CustomStatementInspector

The CustomStatementInspector is nothing else than a Spring Component that implements the provided interface:

@Component
public class CustomStatementInspector implements StatementInspector {

    private static final ThreadLocal<String> deleteReason = ThreadLocal.withInitial(() -> "none");

    @Override
    public String inspect(String sql) {
        if (sql.contains(":current_user")) {
            sql = sql.replace(":current_user", "'" + getCurrentUserId() + "'");
        }
        if (sql.contains(":deleted_reason")) {
            sql = sql.replace(":deleted_reason", "'" + deleteReason.get() + "'");
        }
        return sql;
    }

    private String getCurrentUserId() {
        return UUID.randomUUID().toString();  // Replace with Spring Security context if applicable
    }

    public void setDeleteReason(String reason) {
        deleteReason.set(reason);
    }

    public void clear() {
        deleteReason.remove();
    }
}

This component has a ThreadLocal variable to store the reason for deletion and a method to set it.

Hibernate calls the inspect method before executing any query, so we can replace the placeholders of the @SQLDelete with the actual values.

Implementing the logical delete in the service layer is now straightforward:

public void deleteAuthorEffective(Integer id, String reason) {
    customStatementInspector.setDeleteReason(reason);
    authorRepository.deleteById(id);
    // customStatementInspector.clear();
}

With Virtual Threads, there should be no need to manually clean up the ThreadLocal variable, but it’s always a good practice to do so. Check the previous article to set up a custom Spring interceptor to clean up the ThreadLocal variable: Event Notification Pattern.

Conclusion

Implementing logical delete with JPA in Spring Boot can be straightforward and efficient with the right approach. By using Hibernate’s @SQLDelete and @SQLRestriction annotations, we can achieve logical deletion without the drawbacks of traditional methods.

The Hibernate StatementInspector ensures dynamic parameter replacement, making the process seamless and secure.

The full code is available on GitHub.

Feel free to contact me for any questions or critiques.