Soft-Delete（软删除）—Spring Data JPA学习笔记（5）

4-fish

在一些业务场景中，需要用到软删除Soft-Delete。所谓软删除就是记录并未真实从数据库中删除，而是通过某种技法，把删除的记录隐藏起来，使得查询时无法获取“删除的”记录。常用的做法是设定删除标志值，然后通过标志值进行过滤。当然，也可以设计一个删除表，将删除的记录转存到删除表中。

Spring Data JPA实现Soft-Delete（软删除）

查阅了一些资料发现在JPA的层面来实现Soft-Delete较为麻烦，大都需要在Repository层重载实现。而在JPA的实现层hibernate、eclipse link都有比较简单的实现方法。这次要不看下hibernate是怎么实现软删除的

hibernate的实现软删除还是很方便的，只需在定义实体的时候添加注解@SQLDelete、@Where。

@SQLDelete是指实体删除时，执行的SQL语句。

@Where是指查询是附加的查询条件

@SQLDelete(sql = "UPDATE sys_user SET deleted=true WHERE id=?")
@Where(clause = "deleted = false")
@Entity
public class SysUser extends BaseIntegerEntity {    private String username;
    private String email;
    @Enumerated(EnumType.STRING)
    private GenderType gender;
    @Column(nullable = false)
    private Boolean deleted = Boolean.FALSE;
//get set
}

查询时执行的SQL语句，会自动附加@Where的查询条件。

执行删除操作时，则会执行@SQLDelete的SQL语句

常用的Repository通过@Query定义JPQL或者SQL是否也可以自动附加查询条件呢？在SysUserRepository新增3个方法

public interface SysUserRepository extends BaseRepository<SysUser, Integer> {
    @Query(value = "SELECT * FROM sys_user WHERE gender='MALE'" ,nativeQuery = true)
    List<SysUser> findMalesBySQL();
    @Query(value = "SELECT u From SysUser u Where u.gender='MALE' ")
    List<SysUser> findMales();
    List<SysUser> findSysUsersByGender(GenderType gender);
}

执行findSysUserByGender的SQL语句

执行findMales的SQL语句

执行findMalesBySQL的SQL语句，理所当然是不可以的，但万一呢？好吧，没有万一……

包含关联关系的软删除

独立的实体已经达到了软删除的目的，那如果有关联关系会怎样。

修改下实体SysUser，然后新增实体Organization

@SQLDelete(sql = "UPDATE sys_user SET deleted=true WHERE id=?")
@Where(clause = "deleted <> true")
@Entity
public class SysUser extends BaseIntegerEntity {
    private String username;
    private String email;
    @Enumerated(EnumType.STRING)
    private GenderType gender;
    @Column(name = "organization_id")
    private Integer organizationId;
    @JoinColumn(name = "organization_id",insertable = false,updatable = false)
    @ManyToOne(fetch = FetchType.LAZY)
    private Organization organization;
    @Column(nullable = false)
    private Boolean deleted = Boolean.FALSE;
//get set
@SQLDelete(sql = "UPDATE organization SET deleted=true WHERE id=?")
@Where(clause = "deleted <> true")
@Entity
public class Organization extends BaseIntegerEntity {
    private String name;
    private String code;
    @OneToMany(cascade = CascadeType.REMOVE,fetch = FetchType.LAZY, mappedBy = "organization")
    private List<SysUser> users;
    @Column(nullable = false)
    private Boolean deleted = Boolean.FALSE;
//get set
}

通过Organization服务访问下users

执行的SQL语句，已经自动附加了SysUser附加条件

来试下级联删除有没有问题，可以简单执行SQL的语句，也是SQLDelete自定义的。

@Filter和@FilterDef过滤器

通过注解@SQLDelete和@Where已经实现软删除大部分功能了。但是如果要根据业务可以动态设置是否过滤“删除的”记录，要如何实现呢？这里要用到hibernate提供另外的两个注解@Filter和@FilterDef。我们修改实体SysUser如下

@SQLDelete(sql = "UPDATE sys_user SET deleted=true WHERE id=?")
@Filter(name = "softDeleteFilter", condition = "deleted= :deleted")
@FilterDef(name = "softDeleteFilter",
        parameters = @ParamDef(name = "deleted", type = "boolean"),
        defaultCondition = "deleted <> true")
@Entity
public class SysUser extends BaseIntegerEntity {
    private String username;
    private String email;
    @Enumerated(EnumType.STRING)
    private GenderType gender;
    @Column(name = "organization_id")
    private Integer organizationId;
    @JoinColumn(name = "organization_id", insertable = false, updatable = false)
    @ManyToOne(fetch = FetchType.LAZY)
    private Organization organization;
    @Column(nullable = false)
    private Boolean deleted = Boolean.FALSE;
//get set
}

过滤器已经定义好了，要起作用的话，需要手动启用下

@Service
public class SysUserServiceImpl extends BaseCommonCrudApplicationServiceImpl<SysUser, Integer, SysUserDTO, SysUserRepository>
        implements SysUserService {
    private final EntityManager entityManager;
    protected SysUserServiceImpl(SysUserRepository repository, EntityManager entityManager) {
        super(repository);
        this.entityManager = entityManager;
    @Override
    public List<SysUserDTO> getMaleUserList() {
        List<SysUser> users;
        try (Session session = entityManager.unwrap(Session.class)) {
            Filter softDeleteFilter = session.enableFilter("softDeleteFilter");
            softDeleteFilter.setParameter("deleted", true);