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?

We would be using this in our own version of AbstractAudible, which extends Spring Data's Persistable. — ilana, Jan 09 '12 at 19:27

score 4 · Answer 1 · edited Jan 23 '17 at 07:50

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.

easy and clear solution, thanks. but don't forget @Transactional annotation for update-method or the whole class. as almost beginner, took me some time to find out... — duke, May 08 '18 at 20:57

score 2 · Answer 2 · answered Jul 07 '18 at 06:05

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.

Nice approach for overriding delete() and throw the error. But, is there any way I can do these at the global level, otherwise, I need to write in all repository. Code duplicacy. — Satish Patro, Jan 30 '20 at 04:50

Using Hibernate @SQLDelete for Soft Delete across All Entities

2 Answers2