我有一个定义为 Stuff 的表格...
id, <fields>..., active
Active是软删除标记，始终为1或0。长期来看，这可能会被历史表取代。
public interface StuffRepository extends JpaRepository<StuffEntity, Long> {}
active=1条件附加到为此存储库生成的查询中？或者更理想的是，允许我扩展用于生成查询的语法？@queues，但那样我就失去了生成查询的便利性。我还想避免在接口中添加“active”方法。@Where(clause="is_active=1") 不是使用 Spring Data JPA 处理软删除的最佳方式。
首先，它只能与 Hibernate 实现一起使用。
其次，您无法通过 Spring Data 检索已软删除的实体。
我的解决方案是 Spring Data 提供的 EL 表达式。可以在通用仓库上使用 #{#entityName} 表达式表示具体实体类型名称。
代码将会是这样：
//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);
这是一个老问题，你可能已经找到了答案。但是，对于所有正在寻找Spring/JPA/Hibernate答案的程序员们——
假设你有一个实体Dog：
@Entity
public class Dog{
......(fields)....
@Column(name="is_active")
private Boolean active;
}
以及一个代码仓库：
public interface DogRepository extends JpaRepository<Dog, Integer> {
}
您只需要在实体级别添加@Where注释即可，结果如下：
@Entity
@Where(clause="is_active=1")
public class Dog{
......(fields)....
@Column(name="is_active")
private Boolean active;
}
仓库执行的所有查询都将自动过滤掉“非活动”行。
@Where是JPA或Spring的功能，请分享。 - Andrew White根据易天明的回答，我创建了一个带有覆盖软删除方法的CrudRepository实现：
@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();
}
它可以与BasicEntity一起使用：
@MappedSuperclass
public abstract class BasicEntity {
@Column(name = "is_active")
private boolean isActive = true;
public abstract Long getId();
// isActive getters and setters...
}
最后的实体：
@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...
}
AndIsActive，或者尝试实现BeanDefinitionPostProcessor或类似的东西来自动添加它并创建代理接口来隐藏它。我个人更喜欢简单、高效/可扩展的解决方案，所以我根本不会使用JPA/Hibernate/SpringData ;) - vdshb目前版本（最高版本为1.4.1）的Spring Data JPA没有专门支持软删除的功能。然而，我强烈建议您玩一下特性分支DATAJPA-307，因为这是即将发布的新版本当前正在开发的功能。
要使用当前状态，请更新您使用的版本到1.5.0.DATAJPA-307-SNAPSHOT，确保您让它引入特殊的Spring Data Commons版本以使其正常工作。您应该能够遵循我们的样例测试用例以了解如何让它们正常工作。
P.S.：我们完成这项特性后，我会更新问题。
我将vdshb提供的解决方案适应了新版本的Spring JPA存储库，并添加了一些在企业应用程序中可能出现的常见字段。
基本实体：
@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;
}
基本仓库：
@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);
}
}
你可能已经看到，我从删除方法中抛出了UnsupportedOperationException异常。这是为了限制在你的项目中没有经验的程序员调用这些方法。相反，你可以实现自己的删除方法。
我使用了@vadim_shb的解决方案来扩展JpaRepository，以下是我的Scala代码。请为他的答案点赞，而不是这个。只是想展示一个包含分页和排序的示例。
与查询注释一起使用时，分页和排序非常有效。虽然我没有测试过所有内容，但对于那些询问分页和排序的人来说，它们似乎是在Query注释之上构建的。如果我解决了任何问题，我将进一步更新此内容。
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
}
您可以扩展SimpleJpaRepository并创建自己的自定义存储库，在其中以通用方式定义软删除功能。
您还需要创建自定义JpaRepositoryFactoryBean并在主类中启用它。
您可以在此处检查我的代码https://github.com/dzinot/spring-boot-jpa-soft-delete
这是否是一个好主意可能取决于您的数据库，但在我的实现中它非常有效。create or replace view active_stuff as select * from Stuff where active=1;
@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";
}
}
@Query注解编写jpql，该怎么办？ - Muhammad HewedyfindByLastName(String lastName)或findByStatus(boolean status)或findByAge(int age)，会发生什么？ 创建自定义的BaseJpaRepository（我已经扩展了JpaRepository）之后（我使用下面提到的示例），我可以删除实体，这意味着在findAll()或findById(int id)中只能找到delete标志为 false 的实体。但是对于其他自定义的findByOtherProeprty()方法，则不起作用，它们也会返回delete标志为 true 的所有其他实体。我是否遗漏了某些东西，以使它们正常工作？ - Mamun