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.

回答1:

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.

回答2:

@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); 

回答3:

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...
}

回答4:

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.

回答5:

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

回答6:

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

回答7:

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
}