Using Hibernate @SQLDelete for Soft Delete across All Entities

Question

We have a fairly complex data model and are using Hibernate and Spring Data JPA on top of MySQL. We have a base class that all domain objects extend to minimize boiler plate code. I would like to be able to add soft delete functionality across all of our domain objects using only this class. However, @SQLDelete requires the table name in the clause:

@SQLDelete(sql="UPDATE (table_name) SET deleted = '1' WHERE id = ?")
@Where(clause="deleted <> '1'")

Does anybody know of a way to generalize the SQLDelete statement and allow the extending domain objects to populate their own table names?

Answer 1

If you use hibernate and @SQLDelete, there's no easy solution to your question. But you can consider another approach to soft delete with Spring Data's expression language:

@Override
@Query("select e from #{#entityName} e where e.deleteFlag=false")
public List<T> findAll();

//recycle bin
@Query("select e from #{#entityName} e where e.deleteFlag=true")
public List<T> recycleBin(); 

@Query("update #{#entityName} e set e.deleteFlag=true where e.id=?1")
@Modifying
public void softDelete(String id); 
//#{#entityName} will be substituted by concrete entity name automatically.

Rewrite base repository like this. All sub repository interfaces will have soft delete ability.

Answer 2

Another approach, which could be more flexible.

On Entity level create

@MappedSuperclass
public class SoftDeletableEntity {

    public static final String SOFT_DELETED_CLAUSE = "IS_DELETED IS FALSE";

    @Column(name = "is_deleted")
    private boolean isDeleted;
    ...

}

Update your Entity which should be soft deletable

@Entity
@Where(clause = SoftDeletableEntity.SOFT_DELETED_CLAUSE)
@Table(name = "table_name")
public class YourEntity extends SoftDeletableEntity  {...}

Create a custom Interface Repository which extends the Spring's Repository. Add default methods for soft delete. It should be as a base repo for your Repositories. e.g.

    @NoRepositoryBean
    public interface YourBaseRepository<T, ID> extends JpaRepository<T, ID> {


        default void softDelete(T entity) {

            Assert.notNull(entity, "The entity must not be null!");
            Assert.isInstanceOf(SoftDeletableEntity.class, entity, "The entity must be soft deletable!");

            ((SoftDeletableEntity)entity).setIsDeleted(true);
            save(entity);
        }

        default void softDeleteById(ID id) {

            Assert.notNull(id, "The given id must not be null!");
            this.softDelete(findById(id).orElseThrow(() -> new EmptyResultDataAccessException(
                    String.format("No %s entity with id %s exists!", "", id), 1)));
        }


    }

NOTE: If your application doesn't have the hard delete then you could add

    String HARD_DELETE_NOT_SUPPORTED = "Hard delete is not supported.";

    @Override
    default void deleteById(ID id) {
        throw new UnsupportedOperationException(HARD_DELETE_NOT_SUPPORTED);
    }

    @Override
    default void delete(T entity) {
        throw new UnsupportedOperationException(HARD_DELETE_NOT_SUPPORTED);
    }

    @Override
    default void deleteAll(Iterable<? extends T> entities) {
        throw new UnsupportedOperationException(HARD_DELETE_NOT_SUPPORTED);
    }

    @Override
    default void deleteAll() {
        throw new UnsupportedOperationException(HARD_DELETE_NOT_SUPPORTED);
    }

Hope it could be useful.