03. Making Soft Delete Easier

#Introduction

When building a database, there are two ways to delete data.

Method Description

Soft Delete UPDATE table SET delete = 1 WHERE id = 2 format The ROW is not deleted; instead, it's controlled through a flag

Hard Delete DELETE FROM table WHERE id = 2 format where the ROW is actually deleted

If a bug occurs due to Soft Delete, you'll probably feel dazed.

Although Hard Delete is sometimes used, Soft Delete is more common.

In such cases, JPA Entity Graph will include the soft-deleted data when loading.

You might think, Can't we just filter at the application level? But since we're human, there can be many gaps in the logic.

Today, I want to talk about how to make Soft Delete easier in JPA.

#@Where Annotation

https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/annotations/Where.html

[

Where (Hibernate JavaDocs)

docs.jboss.org

](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/annotations/Where.html) Where clause to add to the element Entity or target entity of a collection. The clause is written in SQL. A common use case here is for soft-deletes.

The @Where annotation lets you specify default conditions for an Entity.

The advantage is that you don't have to worry about issues that can occur in the object graph due to 'Soft Delete'.

Also, it makes things easier because you can specify query conditions separately even when using joins like @OneToMany, @ManyToOne.

The downside is that since the condition is always applied, it becomes tricky when you need to retrieve deleted items.

(In this case, you can use @FilterDef, which I plan to cover separately soon)

(And personally, I think retrieving deleted items is a contradiction anyway..)

For example, let's assume we have an Entity like this.

@Entity
@Where("del=0")
class Product(
    var name: String,
    val del: Boolean = false
) {

    @Id
    @GeneratedValue
    var id: Long? = null
        protected set

    @ManyToOne
    val image: Image? = null

    fun expire() {
    	this.del = true
    }
}

If this data has id=1 and del=true, calling productRepository.findById(1) will return no data.

#@SQLDelete Annotation

Since default conditions are applied during queries, we can use them without any problems.

But human desire is endless, so we start thinking it would be nice if deletion was handled at the ORM level rather than changing the flag directly. That's where the @SQLDelete annotation comes in.

"Custom SQL statement for delete of an entity/collection."

[

SQLDelete (Hibernate JavaDocs) check For persistence operation what style of determining results (success/failure) is to be used. docs.jboss.org

](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/annotations/SQLDelete.html)

Let's look at the example below.

@Entity
@Where("del=0")
@SQLDelete("UPDATE product SET del=1 WHERE id = ?")
class Product(
    var name: String,
    val del: Boolean = false
) {

    @Id
    @GeneratedValue
    var id: Long? = null
        protected set

    @ManyToOne
    val image: Image? = null
}

With the @SQLDelete annotation, you write the query to execute when an actual delete occurs.

Then, when productRepository.deleteById(1L) is called, instead of the query running as 'DELETE FROM product WHERE id = 1', it will run as 'UPDATE product SET del=1 WHERE id = 1'.