Search code examples
javaspring-dataspring-data-jdbc

How to handle soft-delete in Spring Data JDBC?

Is there a good way of how to handle soft-delete in Spring Data JDBC?

In Spring Data JPA we can either add @Where(clause="is_active=1") annotation or extend CrudRepository or PagingAndSortingRepository.

Since Spring Data JDBC doesn't support SPEL in queries, we cant write them in generic way like this: 

@NoRepositoryBean
public interface SoftDeleteCrudRepository<T extends BasicEntity, ID extends 
Long> extends CrudRepository<T, ID> {
//Override CrudRepository or PagingAndSortingRepository's query method:
@Override
@Transactional(readOnly = true)
@Query("select e from #{#entityName} e where e.deleteFlag=false")
public List<T> findAll();

//Look up deleted entities
@Query("select e from #{#entityName} e where e.deleteFlag=true")
@Transactional(readOnly = true)
public List<T> findAllDeleted(); 

//Soft delete.
@Query("update #{#entityName} e set e.deleteFlag=true where e.id=?1")
@Transactional
@Modifying
public void softDelete(String id);
...
}

So extending CrudRepository or PagingAndSortingRepository means writing same queries for each repository for each entity/table? Like 

Repository1
@Override
@Transactional(readOnly = true)
@Query("select id, name, value, deleteFlag from table1 e where e.deleteFlag=false")
public List<T> findAll();
....

Repository2
@Override
@Transactional(readOnly = true)
@Query("select id, name, value, deleteFlag from table2 e where e.deleteFlag=false")
public List<T> findAll();
....

Thank you for the reply in advance!

Solution

  • I currently see three options how one might achieve this.

    1. Use views. For each aggregate root create a database view that filters out the soft-deleted rows. Map your entities against these views.

    2. write your own SqlGenerator. And inject it into the DefaultDataAccessStrategy via a SqlGeneratorSource. Since SqlGenerator is only package-visible you'll have to create your own DefaultDataAccessStrategy for this basically duplicating the existing one. This, of course, will come with a long-term cost of maintaining it.

    3. Since all you seem to need for your scenario is the very special SpEL support for the entity name opening an issue for that and submitting a pull request might be a viable option. If you are interested in this approach and need more help with it mention it in the issue description.