Handling soft-deletes with Spring JPA

I have a table Stuff defined as...

id, <fields>..., active

Active is the soft-delete flag and is always 1 or 0. Long term this may go away in favor of a historical table.

public interface StuffRepository extends JpaRepository<StuffEntity, Long> {} 

In code, we always use active records. Is there any way to get Spring to always append an active=1 condition to queries generated for this repository? Or more ideally allow me to extend the grammar used to generate the queries?

I understand that I can create named @queues everywhere but then I lose the convenience of the generated queries. I also want to avoid polluting the interface with "active" methods.

I am using Hibernate 4.2 as my JPA implementation if that matters.

@Where(clause="is_active=1") is not the best way to handle soft delete with spring data jpa.

First, it only works with hibernate implement.

Second, you can never fetch soft deleted entities with spring data.

My solution is el provided by spring data. #{#entityName} expression can be used on generic repository represent concrete entity type name.

And code will be like this:

//Override CrudRepository or PagingAndSortingRepository's query method:
@Override
@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")
public List<T> recycleBin(); 

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

This is an old question, and you probably already found the answer. BUT, for all the Spring/JPA/Hibernate programmers out there seeking for answer -

Say you have an entity Dog:

 @Entity
 public class Dog{
    
 ......(fields)....        

 @Column(name="is_active")
 private Boolean active;
 }

and a repository:

public interface DogRepository extends JpaRepository<Dog, Integer> {
} 

All you need to do is add the @Where annotation on the entity level, resulting:

@Entity
@Where(clause="is_active=1")
public class Dog{
      
......(fields)....        

@Column(name="is_active")
private Boolean active;
}

All the queries performed by the repository will automatically filter out the "non-active" rows.

Based on 易天明 answer I've created CrudRepository implementation with overriden methods for soft delete:

@NoRepositoryBean
public interface SoftDeleteCrudRepository<T extends BasicEntity, ID extends Long> extends CrudRepository<T, ID> {
  @Override
  @Transactional(readOnly = true)
  @Query("select e from #{#entityName} e where e.isActive = true")
  List<T> findAll();

  @Override
  @Transactional(readOnly = true)
  @Query("select e from #{#entityName} e where e.id in ?1 and e.isActive = true")
  Iterable<T> findAll(Iterable<ID> ids);

  @Override
  @Transactional(readOnly = true)
  @Query("select e from #{#entityName} e where e.id = ?1 and e.isActive = true")
  T findOne(ID id);

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

  @Override
  @Transactional(readOnly = true)
  @Query("select count(e) from #{#entityName} e where e.isActive = true")
  long count();

  @Override
  @Transactional(readOnly = true)
  default boolean exists(ID id) {
      return findOne(id) != null;
  }

  @Override
  @Query("update #{#entityName} e set e.isActive=false where e.id = ?1")
  @Transactional
  @Modifying
  void delete(Long id);


  @Override
  @Transactional
  default void delete(T entity) {
      delete(entity.getId());
  }

  @Override
  @Transactional
  default void delete(Iterable<? extends T> entities) {
      entities.forEach(entitiy -> delete(entitiy.getId()));
  }

  @Override
  @Query("update #{#entityName} e set e.isActive=false")
  @Transactional
  @Modifying
  void deleteAll();
}

It could be used with BasicEntity:

@MappedSuperclass
public abstract class BasicEntity {
  @Column(name = "is_active")
  private boolean isActive = true;

  public abstract Long getId();

  // isActive getters and setters...
}

And final entity:

@Entity
@Table(name = "town")
public class Town extends BasicEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "town_id_seq")
    @SequenceGenerator(name = "town_id_seq", sequenceName = "town_id_seq", allocationSize = 1)
    protected Long id;

    private String name;

    // getters and setters...
}

In current versions (up to 1.4.1) there's no dedicated support for soft deletes in Spring Data JPA. However, I strongly encourage you to play with the feature branch for DATAJPA-307 as this is a feature currently worked on for the upcoming release.

To use the current state update the version you use to 1.5.0.DATAJPA-307-SNAPSHOT and make sure you let it pull in the special Spring Data Commons version it needs to work. You should be able to follow the sample test case we have to see how to get that stuff working.

P.S.: I'll update the question once we finished working on the feature.

You can extend from SimpleJpaRepository and create your own custom repository where you can define the soft delere functionality in a generic way.

You'll also need to create a custom JpaRepositoryFactoryBean and enable that in your main class.

You can check my code here https://github.com/dzinot/spring-boot-jpa-soft-delete

I adapted the solution provided by vdshb to the newer version of spring JPA repository. Also added some common fields which might appear in your enterprise application.

Basic entity:

@Data
@MappedSuperclass
public abstract class BasicEntity {

  @Id
  @GeneratedValue
  protected Integer id;

  protected boolean active = true;

  @CreationTimestamp
  @Column(updatable = false, nullable = false)
  protected OffsetDateTime createdDate;

  @UpdateTimestamp
  @Column(nullable = false)
  protected OffsetDateTime modifiedDate;

  protected String createdBy = Constants.SYSTEM_USER;

  protected String modifiedBy = Constants.SYSTEM_USER;
}

Basic repository:

@NoRepositoryBean
public interface BasicRepository<T extends BasicEntity, ID extends Integer> extends JpaRepository<T, ID> {
    @Override
    @Transactional(readOnly = true)
    @Query("select e from #{#entityName} e where e.active = true")
    List<T> findAll();

    @Override
    @Transactional(readOnly = true)
    @Query("select e from #{#entityName} e where e.active = true and e.id = ?1")
    Optional<T> findById(ID id);

    @Override
    @Transactional(readOnly = true)
    @Query("select e from #{#entityName} e where e.id in ?1 and e.active = true")
    List<T> findAllById(Iterable<ID> ids);

    @Override
    @Transactional(readOnly = true)
    @Query("select e from #{#entityName} e where e.id = ?1 and e.active = true")
    T getOne(ID id);

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

    @Override
    @Transactional(readOnly = true)
    @Query("select count(e) from #{#entityName} e where e.active = true")
    long count();

    @Override
    @Transactional(readOnly = true)
    default boolean existsById(ID id) {
        return getOne(id) != null;
    }

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

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

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

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

    /**
     * Soft deletes entity in the database.
     * It will not appear in the result set of default queries.
     *
     * @param id of the entity for deactivation
     * @param modifiedBy who modified this entity
     * @return deactivated entity with fetched fields
     * @throws IncorrectConditionException when the entity is already deactivated.
     * @throws NotFoundException when the entity is not found in the database.
     */
    @Transactional
    @Modifying
    default T deactivate(ID id, String modifiedBy) throws IncorrectConditionException {
        final T entity = findById(id)
                .orElseThrow(() -> new NotFoundException(
                        String.format("Entity with ID [%s] wasn't found in the database. " +
                                "Nothing to deactivate.", id)));
        if (!entity.isActive()) {
            throw new IncorrectConditionException(String.format("Entity with ID [%s] is already deactivated.", id));
        }
        entity.setActive(false);
        entity.setModifiedBy(modifiedBy);
        return save(entity);
    }

    /**
     * Activates soft deleted entity in the database.
     *
     * @param id of the entity for reactivation
     * @param modifiedBy who modified this entity
     * @return updated entity with fetched fields
     * @throws IncorrectConditionException when the entity is already activated.
     * @throws NotFoundException when the entity is not found in the database.
     */
    @Transactional
    @Modifying
    default T reactivate(ID id, String modifiedBy) throws IncorrectConditionException {
        final T entity = findById(id)
                .orElseThrow(() -> new NotFoundException(
                        String.format("Entity with ID [%s] wasn't found in the database. " +
                                "Nothing to reactivate.", id)));
        if (entity.isActive()) {
            throw new IncorrectConditionException(String.format("Entity with ID [%s] is already active.", id));
        }
        entity.setActive(true);
        entity.setModifiedBy(modifiedBy);
        return save(entity);
    }
}

As you might see, I throw UnsupportedOperationException from delete method. It was made to restrict unexperienced programmer on your project to call these methods. Instead you may implement your own delete methods.

I suggest you use a database view (or equivalent in Oracle) if you don't want to import hibernate specific annotations. In mySQL 5.5, these views can be updateable and insertable if the filter criteria is as simple as active=1

> create or replace view active_stuff as select * from Stuff where active=1;

Whether this is a good idea probably depends on your database but it works great in my implementation.

Undeleting required an additional entity which accessed 'Stuff' directly but then so would @Where

I used the solution from @vadim_shb to extend JpaRepository and here is my code in Scala. Upvote his answer, not this one. Just wanted to show an example that includes paging and sorting.

Paging and sorting work great in conjunction with the query annotations. I have not tested all of it, but for those asking about paging and sorting, they seem to be layered on top of the Query annotation. I'll update this further if I resolve any issues.

import java.util
import java.util.List

import scala.collection.JavaConverters._
import com.xactly.alignstar.data.model.BaseEntity
import org.springframework.data.domain.{Page, Pageable, Sort}
import org.springframework.data.jpa.repository.{JpaRepository, Modifying, Query}
import org.springframework.data.repository.NoRepositoryBean
import org.springframework.transaction.annotation.Transactional

@NoRepositoryBean
trait BaseRepository[T <: BaseEntity, ID <: java.lang.Long] extends JpaRepository[T, ID] {

  /* additions */
  @Query("select e from #{#entityName} e where e.isDeleted = true")
  @Transactional(readOnly = true)
  def findInactive: Nothing

  @Transactional
  def delete(entity: T): Unit = delete(entity.getId.asInstanceOf[ID])

  /* overrides */
  @Query("select e from #{#entityName} e where e.isDeleted = false")
  override def findAll(sort: Sort):  java.util.List[T]

  @Query("select e from #{#entityName} e where e.isDeleted = false")
  override def findAll(pageable: Pageable): Page[T]

  @Transactional(readOnly = true)
  @Query("select e from #{#entityName} e where e.isDeleted = false")
  override def findAll: util.List[T]

  @Transactional(readOnly = true)
  @Query("select e from #{#entityName} e where e.id in :ids and e.isDeleted = false")
  override def findAll(ids: java.lang.Iterable[ID]): java.util.List[T]

  @Transactional(readOnly = true)
  @Query("select e from #{#entityName} e where e.id = :id and e.isDeleted = false")
  override def findOne(id: ID): T

  @Transactional(readOnly = true)
  @Query("select count(e) from #{#entityName} e where e.isDeleted = false")
  override def count: Long

  @Transactional(readOnly = true)
  override def exists(id: ID): Boolean = findOne(id) != null

  @Query("update #{#entityName} e set e.isDeleted=true where e.id = :id")
  @Transactional
  @Modifying
  override def delete(id: ID): Unit

  @Transactional
  override def delete(entities: java.lang.Iterable[_ <: T]): Unit = {
    entities.asScala.map((entity) => delete(entity))
  }

  @Transactional
  @Modifying
  override def deleteInBatch(entities: java.lang.Iterable[T]): Unit = delete(entities)

  override def deleteAllInBatch(): Unit = throw new NotImplementedError("This is not implemented in BaseRepository")

  @Query("update #{#entityName} e set e.isDeleted=true")
  @Transactional
  @Modifying
  def deleteAll(): Unit
}

I defined a respository like this

@NoRepositoryBean
public interface SoftDeleteRepository<T, ID extends Serializable> extends JpaRepository<T, ID>,
    JpaSpecificationExecutor<T> {

    enum StateTag {
        ENABLED(0), DISABLED(1), DELETED(2);

        private final int tag;

        StateTag(int tag) {
            this.tag = tag;
        }

        public int getTag() {
            return tag;
        }
    }

    T changeState(ID id, StateTag state);

    List<T> changeState(Iterable<ID> ids, StateTag state);

    <S extends T> List<S> changeState(Example<S> example, StateTag state);

    List<T> findByState(@Nullable Iterable<StateTag> states);

    List<T> findByState(Sort sort, @Nullable Iterable<StateTag> states);

    Page<T> findByState(Pageable pageable, @Nullable Iterable<StateTag> states);

    <S extends T> List<S> findByState(Example<S> example, @Nullable Iterable<StateTag> states);

    <S extends T> List<S> findByState(Sort sort, Example<S> example, @Nullable Iterable<StateTag> states);

    <S extends T> Page<S> findByState(Pageable pageable, Example<S> example,
                                  @Nullable Iterable<StateTag> states);

    long countByState(@Nullable Iterable<StateTag> states);

    default String getSoftDeleteColumn() {
        return "disabled";
    }
}