SpringDataJPA实现全局软删除（逻辑删除）

背景

一个为0的新项目，刚刚接手，从搭框架做起，因为项目业务的原因，要求大部分的数据库数据都不允许硬删除，必须全部做到软删除（逻辑删除），之前没有做过相关的东西，查阅了多个Stackoverflow的大神的解答之后做一下整理，在这里记录一下，希望对大家有用。

过程

1. 创建基础类

因为并不是所有的实体都需要逻辑删除，所以，先创建一个通用的基础实体类

package cn.lemonit.yoyolearn.common.db.base;

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Getter;
import lombok.Setter;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import java.util.Date;

@MappedSuperclass
@Setter
@Getter
public class BaseEntity {

    @Id
    @Column
    private String dataKey;
    @Column
    @JsonIgnore
    private Date createAt = null;
    @JsonIgnore
    @Column
    private Date updateAt = null;
}

接下来，我们创建软删除基础实体类，之后所有需要软删除的实体继承自当前类即可

package cn.lemonit.yoyolearn.common.db.base;

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Getter;
import lombok.Setter;

import javax.persistence.Column;
import javax.persistence.MappedSuperclass;
import java.util.Date;

@MappedSuperclass
@Setter
@Getter
public class SoftDeletableEntity extends BaseEntity {

    @JsonIgnore
    @Column
    private Date deleteAt = null;

}

接下来，我们创建通用的DAO，也就是JPA中的Repository，为其添加软删除方法，之后所有的需要软删除的实体，对应的Repository继承自此类即可

package cn.lemonit.yoyolearn.common.db.base;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.NoRepositoryBean;

import javax.transaction.Transactional;
import java.util.Date;

@NoRepositoryBean
public interface SoftDeletableDao<T extends SoftDeletableEntity> extends BaseDao<T> {

    @Modifying
    @Transactional
    @Query("UPDATE #{#entityName} t SET t.deleteAt = ?2 WHERE t.dataKey = ?1")
    void softDeleteByDataKey(String dataKey, Date deleteDate);

    default void softDeleteByDataKey(String dataKey) {
        softDeleteByDataKey(dataKey, new Date());
    }

}

之前看到其他博客的文章，有提到用@SQLDelete和@Where做软删除的方案，但是在写entity的@SQLDelete注解的时候，会写死表名在SQL中，这样造成的后果就是，每个需要软删除的实体都写写一遍SQL，笔者比较懒，没有使用这种方法，而是使用了上面代码的这种方法。

贴上上面代码中涉及到的其他几个类：

• 这个类是把一些常量字符串抽出来定义，方便其他地方使用

package cn.lemonit.yoyolearn.common.db.define;

public class CommonDbStringDefine {

    public static final String COLUMN_NAME_DATA_KEY = "DATA_KEY";
    public static final String COLUMN_NAME_DELETE_AT = "DELETE_AT";
    public static final String COLUMN_NAME_CREATE_AT = "CREATE_AT";
    public static final String COLUMN_NAME_UPDATE_AT = "UPDATE_AT";

    public static final String SOFT_DELETE_WHERE_CLAUSE = COLUMN_NAME_DELETE_AT + " IS NULL";
}

这个类是为了统一表名、列名命名规则的基础类

package cn.lemonit.yoyolearn.common.db.base;

import cn.lemonit.yoyolearn.common.db.define.CommonDbStringDefine;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;
import org.hibernate.cfg.ImprovedNamingStrategy;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;

import java.util.Arrays;
import java.util.List;

public abstract class BaseDbNamingStrategy extends PhysicalNamingStrategyStandardImpl {

    public abstract String getTablePrefix();

    public abstract String getColumnPrefix();

    private static final List<String> ignoreColumnNameList = Arrays.asList(
            CommonDbStringDefine.COLUMN_NAME_DATA_KEY,
            CommonDbStringDefine.COLUMN_NAME_CREATE_AT,
            CommonDbStringDefine.COLUMN_NAME_UPDATE_AT,
            CommonDbStringDefine.COLUMN_NAME_DELETE_AT
    );

    @Override
    public Identifier toPhysicalTableName(Identifier identifier, JdbcEnvironment jdbcEnvironment) {
        return Identifier.toIdentifier(getTablePrefix() +
                ImprovedNamingStrategy.INSTANCE.classToTableName(identifier.toString()).toUpperCase());
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier identifier, JdbcEnvironment jdbcEnvironment) {
        String tableName = ImprovedNamingStrategy.INSTANCE.classToTableName(identifier.toString()).toUpperCase();
        return Identifier.toIdentifier(
                (ignoreColumnNameList.contains(tableName) ? "" : getColumnPrefix()) + tableName);
    }
}

好了这就是所需要的所有基础类，接下来我们根据业务进行开发

2. 编写业务相关代码

我们举一个用户表的例子，下面是支持软删除的用户实体类

package cn.lemonit.yoyolearn.user_center.entity;

import cn.lemonit.yoyolearn.common.db.base.SoftDeletableEntity;
import cn.lemonit.yoyolearn.common.db.define.CommonDbStringDefine;
import lombok.*;
import org.hibernate.annotations.Where;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity
@Table
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Where(clause = CommonDbStringDefine.SOFT_DELETE_WHERE_CLAUSE)
public class YoyolearnUser extends SoftDeletableEntity {

    @Column
    private String number;
    @Column
    private String name;
    @Column
    private String openPlatformUnionId;

}

接下来，我们创建一个用户的DAO类

package cn.lemonit.yoyolearn.user_center.dao;

import cn.lemonit.yoyolearn.common.db.base.SoftDeletableDao;
import cn.lemonit.yoyolearn.user_center.entity.YoyolearnUser;
import org.springframework.stereotype.Repository;

@Repository
public interface YoyolearnUserDao extends SoftDeletableDao<YoyolearnUser> {
}

没错，什么都不用做，直接继承就可以了，然后在需要的地方直接调用软删除的方法就可以了：

package cn.lemonit.yoyolearn.user_center.controller;

import cn.lemonit.yoyolearn.user_center.dao.YoyolearnUserDao;
import cn.lemonit.yoyolearn.user_center.entity.YoyolearnUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class YoyolearnUserController {

    @Autowired
    private YoyolearnUserDao yoyolearnUserDao;

    @DeleteMapping("/deleteByUserKey")
    private String delete(String userKey) {
        yoyolearnUserDao.softDeleteByDataKey(userKey);
        return "ok";
    }
}