Introduction

Each database application is unique. While most of the time, deleting a record is the best approach, there are times when the application requirements demand that database records should never be physically deleted.

So who uses this technique?

For instance, StackOverflow does it for all Posts (e.g. Questions and Answers). The StackOverflow Posts table has a ClosedDate column which acts as a soft delete mechanism since it hides an Answer for all users who have less than 10k reputation.

If you’re using Oracle, you can take advantage of its Flashback capabilities, so you don’t need to change your application code to offer such a functionality. Another option is to use the SQL Server Temporal Table feature.

However, not all relational database systems support Flashback queries, or they allow you to recover a certain record without having to restore from a database backup. In this case, Hibernate allows you to simplify the implementation of soft deletes, and this article is going to explain the best way to implement the logical deletion mechanism.

If your entities are using the optimistic locking @Version property, then check out this article instead to see how you have to map your entities to include the version property as well.

Domain Model

Considering we have the following tables in your database:

As you can see in the diagram above, the post, post_details, post_comment, and tag tables contain a deleted column which dictates the visibility of a given row. What’s interesting about this database table model is that it covers all three database relationship types:

• one-to-one
• one-to-many
• many-to-many

Therefore, we will discuss the mapping of all these entities as well as their relationships, so stay tuned!

Tag entity

Let’s start with Tag entity mapping since it lacks any entity relationship:

@Entity(name = "Tag")
@Table(name = "tag")
@SQLDelete(sql = """
    UPDATE tag
    SET deleted = true
    WHERE id = ?
	""")
@Loader(namedQuery = "findTagById")
@NamedQuery(name = "findTagById", query = """
    SELECT t
    FROM Tag t
    WHERE
    	t.id = ?1 AND
    	t.deleted = false
	""")
@Where(clause = "deleted = false")
public class Tag extends BaseEntity {

    @Id
    private String id;

    //Getters and setters omitted for brevity
}

The deleted column is defined the BaseEntity class which looks as follows:

@MappedSuperclass
public abstract class BaseEntity {

    private boolean deleted;
}

The @SqlDelete annotation allows you to override the default DELETE statement executed by Hibernate, so we substitute an UPDATE statement instead. Therefore, removing an entity will end up updating the deleted column to true.

The @Loader annotation allows us to customize the SELECT query used to load an entity by its identifier. Hence, we want to filter every record whose deleted column is set to true.

The @Where clause is used for entity queries, and we want to provide it so that Hibernate can append the deleted column filtering condition to hide deleted rows.

While prior to Hibernate 5.2, it was sufficient to provide the @Where clause annotation, in Hibernate 5.2, it’s important to provide a custom @Loader as well so that the direct fetching works as well.

So, considering we have four Tag entities:

doInJPA( entityManager -> {
    Tag javaTag = new Tag();
    javaTag.setId("Java");
    entityManager.persist(javaTag);

    Tag jpaTag = new Tag();
    jpaTag.setId("JPA");
    entityManager.persist(jpaTag);

    Tag hibernateTag = new Tag();
    hibernateTag.setId("Hibernate");
    entityManager.persist(hibernateTag);

    Tag miscTag = new Tag();
    miscTag.setId("Misc");
    entityManager.persist(miscTag);
} );

When removing the Misc Tag:

doInJPA( entityManager -> {
    Tag miscTag = entityManager.getReference(Tag.class, "Misc");
    entityManager.remove(miscTag);
} );

Hibernate will execute the following SQL statement:

UPDATE tag 
SET deleted = true 
WHERE id = 'Misc'

Brilliant!

So now, if we want to load the entity, we get null instead:

doInJPA( entityManager -> {
    assertNull(entityManager.find(Tag.class, "Misc"));
} );

This is because Hibernate executed the following SQL statement:

SELECT 
    t.id as id1_4_, 
    t.deleted as deleted2_4_ 
FROM 
    tag t 
WHERE 
    ( t.deleted = 0 ) AND 
    t.id = ? AND 
    t.deleted = 0

Although the deleted clause is appended twice because we declared both the @Where clause and the @Loader, most RDBMS will eliminate duplicate filters during query parsing. If we only provide the @Where clause, there will be no duplicate delete clause, but then the deleted rows will be visible when being fetched directly.

Also, when running an entity query against all Tag entities, we can see that we can see just three Tags now:

doInJPA( entityManager -> {
    List<Tag> tags = entityManager.createQuery(
        "select t from Tag t", Tag.class)
    .getResultList();

    assertEquals(3, tags.size());
} );

This is because Hibernate manages to append the deleted clause filter when executing the SQL query:

SELECT 
    t.id as id1_4_, 
    t.deleted as deleted2_4_ 
FROM tag t 
WHERE ( t.deleted = 0 )

PostDetails entity

Just like Tag, PostDetails follows the same mapping considerations:

@Entity(name = "PostDetails")
@Table(name = "post_details")
@SQLDelete(sql = """
    UPDATE post_details
    SET deleted = true
    WHERE id = ?
	""")
@Loader(namedQuery = "findPostDetailsById")
@NamedQuery(name = "findPostDetailsById", query = """
    SELECT pd
    FROM PostDetails pd
    WHERE
    	pd.id = ?1 AND
    	pd.deleted = false
	""")
@Where(clause = "deleted = false")
public class PostDetails extends BaseEntity {

    @Id
    private Long id;

    @Column(name = "created_on")
    private Date createdOn;

    @Column(name = "created_by")
    private String createdBy;

    public PostDetails() {
        createdOn = new Date();
    }

    @OneToOne(fetch = FetchType.LAZY)
    @MapsId
    private Post post;

    //Getters and setters omitted for brevity
}

Even if it features a @OneToOne association to a Post, there is no need to filter this relationship since a child entity cannot exist without its parent.

PostComment entity

The same logic applies to PostComment:

@Entity(name = "PostComment")
@Table(name = "post_comment")
@SQLDelete(sql = """
    UPDATE post_comment
    SET deleted = true
    WHERE id = ?
	""")
@Loader(namedQuery = "findPostCommentById")
@NamedQuery(name = "findPostCommentById", query = """
    SELECT pc
    from PostComment pc
    WHERE
    	pc.id = ?1 AND
    	pc.deleted = false
	""")
@Where(clause = "deleted = false")
public class PostComment extends BaseEntity {

    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Post post;

    private String review;

    //Getters and setters omitted for brevity
}

Even if it features a @ManyToOne association to a Post, there is no need to filter this relationship since a child entity cannot exist without its parent.

Post entity

The Post entity being the root of our entity aggergate, it has relationships to PostDetails, PostComment, and Tag:

@Entity(name = "Post")
@Table(name = "post")
@SQLDelete(sql = """
    UPDATE post
    SET deleted = true
    WHERE id = ?
	""")
@Loader(namedQuery = "findPostById")
@NamedQuery(name = "findPostById", query = """
    SELECT p
    FROM Post p
    WHERE
    	p.id = ?1 AND
    	p.deleted = false
	""")
@Where(clause = "deleted = false")
public class Post extends BaseEntity {

    @Id
    private Long id;

    private String title;

    @OneToMany(
        mappedBy = "post",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    private List<PostComment> comments = new ArrayList<>();

    @OneToOne(
        mappedBy = "post",
        cascade = CascadeType.ALL,
        orphanRemoval = true,
        fetch = FetchType.LAZY
    )
    private PostDetails details;

    @ManyToMany
    @JoinTable(
        name = "post_tag",
        joinColumns = @JoinColumn(name = "post_id"),
        inverseJoinColumns = @JoinColumn(name = "tag_id")
    )
    private List<Tag> tags = new ArrayList<>();

    //Getters and setters omitted for brevity

    public void addComment(PostComment comment) {
        comments.add(comment);
        comment.setPost(this);
    }

    public void removeComment(PostComment comment) {
        comments.remove(comment);
        comment.setPost(null);
    }

    public void addDetails(PostDetails details) {
        this.details = details;
        details.setPost(this);
    }

    public void removeDetails() {
        this.details.setPost(null);
        this.details = null;
    }

    public void addTag(Tag tag) {
        tags.add(tag);
    }
}

The Post entity mapping is similar to the Tag entity, which we already discussed so we will focus on the @OneToMany and @ManyToMany associations.

The bidirectional @OneToMany association

While prior to Hibernate 5.2, it was necessary to provide the @Where clause annotation to collections (e.g. `@OneToMany` or `@ManyToMany`), in Hibernate 5.2 we don’t need these collection-level annotations since the `PostComment` is already annotated accordingly, and Hibernate knows that it needs to filter any _deleted_ `PostComment`.

Therefore, assuming we have one Post entity with two PostComment child entities:

doInJPA( entityManager -> {
    Post post = new Post();
    post.setId(1L);
    post.setTitle("High-Performance Java Persistence");
    entityManager.persist(post);

    PostComment comment1 = new PostComment();
    comment1.setId(1L);
    comment1.setReview("Great!");
    post.addComment(comment1);

    PostComment comment2 = new PostComment();
    comment2.setId(2L);
    comment2.setReview("Excellent!");
    post.addComment(comment2);
} );

When we delete a PostComment:

doInJPA( entityManager -> {
    Post post = entityManager.find(Post.class, 1L);
    post.removeComment(post.getComments().get(0));
} );

The cascade mechanism will trigger the child removal, and Hibernate is going to execute the following SQL statement:

UPDATE post_comment 
SET deleted = true 
WHERE id = 1

And now we can see that the collection has only one entry:

doInJPA( entityManager -> {
    Post post = entityManager.find(Post.class, 1L);
    assertEquals(1, post.getComments().size());
} );

When fetching the comments collection, Hibernate executes the following query:

SELECT 
    pc.id as id1_0_, 
    pc.deleted as deleted2_0_, 
    pc.title as title3_0_ 
FROM 
    post pc 
WHERE 
    ( pc.deleted = 0) AND 
    pc.id=1 AND 
    pc.deleted = 0

The reason why we need the @Where clause annotation on the @OneToMany and @ManyToMany associations is that collections act just like entity queries. A child entity might be deleted, so we need to hide it when we fetch a collection.

The bidirectional @ManyToMany association

Again, since we are using a bidirectional association, there is no need to apply the @Where annotation at the children relationship level. the @Where annotation on collection only makes sense for unidirectional associations, but those are not as efficient as bidirectional ones.

So, if we have one Post entity with three Tag child entities:

doInJPA( entityManager -> {
    Post post = new Post();
    post.setId(1L);
    post.setTitle("High-Performance Java Persistence");

    entityManager.persist(post);

    post.addTag(entityManager.getReference(
        Tag.class, "Java"
    ));
    post.addTag(entityManager.getReference(
        Tag.class, "Hibernate"
    ));
    post.addTag(entityManager.getReference(
        Tag.class, "Misc"
    ));
} );

doInJPA( entityManager -> {
    Post post = entityManager.find(Post.class, 1L);
    assertEquals(3, post.getTags().size());
} );

If we remove one Tag:

doInJPA( entityManager -> {
    Tag miscTag = entityManager.getReference(Tag.class, "Misc");
    entityManager.remove(miscTag);
} );

Then, we will no longer see it in the tags collection:

doInJPA( entityManager -> {
    Post post = entityManager.find(Post.class, 1L);
    assertEquals(2, post.getTags().size());
} );

That’s because Hibernate is filtering it out when loading the child entities:

SELECT 
    pt.post_id as post_id1_3_0_, 
    pt.tag_id as tag_id2_3_0_, 
    t.id as id1_4_1_, 
    t.deleted as deleted2_4_1_ 
FROM post_tag pt 
INNER JOIN 
    tag t ON pt.tag_id = t.id 
WHERE 
    ( t.deleted = 0 ) AND 
    pt.post_id = 1

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

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

Conclusion

Soft deleting is a very handy feature when your application requires keeping the deleted entries and just hiding them in the UI. While it is much more convenient to use a technology such as Flashback in Oracle, if your database lacks such a feature, then Hibernate can simplify this task for you.

Insert details about how the information is going to be processed

DOWNLOAD NOW