Hibernate软删除查询返回已删除孙实体问题求助

我在应用中通过@OneToMany和@ManyToOne构建树形数据，同时用@SQLDelete和@Where实现软删除，但查询父实体时，仍会返回已标记为软删除的孙实体，寻求基于注解的解决方案。

实体代码 #

父实体 #

@Entity
@Table(name = "gson_test_parent")
@SQLDelete(sql = "UPDATE gson_test_parent SET deleted = true, deleted_at = now() WHERE id=?")
@Where(clause = "deleted=false")
public class GsonTestParent extends SoftDeletableEntity {

    public static final String STATUS_NEW = "new";
    public static final String STATUS_ACTIVE = "active";
    public static final String STATUS_DISABLED = "disabled";

    @Expose
    private String name;

    @Expose
    @OneToMany(fetch = FetchType.EAGER, mappedBy="gsonTestParentId")
    private List<GsonTestParentToGsonTestChild> gsonTestParentToGsonTestChild = new ArrayList<>();

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<GsonTestParentToGsonTestChild> getGsonTestParentToGsonTestChild() {
        return gsonTestParentToGsonTestChild;
    }
}

关联实体 #

@Entity
@Table(name = "gson_test_parent_to_gson_test_child")
@SQLDelete(sql = "UPDATE gson_test_parent_to_gson_test_child SET deleted = true, deleted_at = now() WHERE id=?")
@Where(clause = "deleted=false")
public class GsonTestParentToGsonTestChild extends SoftDeletableEntity {
    public static final String STATUS_ACTIVE = "active";
    public static final String STATUS_DISABLED = "disabled";

    @Expose
    private Long gsonTestParentId;

    @Expose
    @Transient
    @GsonExcludeBackReference
    private GsonTestParent gsonTestParent;

    @Expose
    @ManyToOne(fetch = FetchType.EAGER)
    @Where(clause = "deleted=false")
    private GsonTestChild gsonTestChild;

    public Long getGsonTestParentId() {
        return gsonTestParentId;
    }

    public GsonTestParent getGsonTestParent() {
        return gsonTestParent;
    }

    public void setGsonTestParent(GsonTestParent gsonTestParent) {
        this.gsonTestParent = gsonTestParent;
    }

    public GsonTestChild getGsonTestChild() {
        return gsonTestChild;
    }
}

子实体 #

@Entity
@Table(name = "gson_test_child")
@SQLDelete(sql = "UPDATE gson_test_child SET deleted = true, deleted_at = now() WHERE id=?")
@Where(clause = "deleted=false")
public class GsonTestChild extends SoftDeletableEntity {

    public static final String STATUS_NEW = "new";
    public static final String STATUS_ACTIVE = "active";
    public static final String STATUS_DISABLED = "disabled";

    @Expose
    private String name;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

软删除父类 #

@MappedSuperclass
public class SoftDeletableEntity extends BaseEntity {

    public SoftDeletableEntity() {
        super();
    }

    @Expose
    protected Timestamp deletedAt;

    protected Boolean deleted = Boolean.FALSE;

    public Timestamp getDeletedAt() {
        return deletedAt;
    }

    public void setDeletedAt(Timestamp deletedAt) {
        this.deletedAt = deletedAt;
    }

    public Boolean getDeleted() {
        return deleted;
    }

    public void setDeleted(Boolean deleted) {
        this.deleted = deleted;
    }
}

查询接口实现 #

@GetMapping(path="/{id}")
public ResponseEntity<String> get(@PathVariable Long id) throws BaseException {
    Map<String, Object> responseMap = new HashMap<>();

    GsonTestParent gsonTestParent = gsonTestParentService.find(id);
    responseMap.put("action", "Get");
    responseMap.put("message", "Entity retrieved");
    responseMap.put("entityType", "GsonTestParent");
    responseMap.put("entity", gsonTestParent);

    return responseService.success(responseMap);
}

查询返回结果 #

{
  "payload": {
    "entityType": "GsonTestParent",
    "action": "Get",
    "message": "Entity retrieved",
    "entity": {
      "name": "test_parent_1",
      "gsonTestParentToGsonTestChild": [
        {
          "gsonTestParentId": 1,
          "gsonTestChild": {
            "name": "test_child_1",
            "deletedAt": "2022-07-26T04:31:30.000",
            "id": 1,
            "createdAt": "2022-07-22T07:24:15.000",
            "updatedAt": "2022-07-22T07:24:15.000",
            "status": "active"
          },
          "deletedAt": null,
          "id": 1,
          "createdAt": "2022-07-22T07:57:46.000",
          "updatedAt": "2022-07-22T07:57:46.000",
          "status": "active"
        }
      ],
      "deletedAt": null,
      "id": 1,
      "createdAt": "2022-07-22T07:23:15.000",
      "updatedAt": "2022-07-22T07:23:15.000",
      "status": "active"
    }
  },
  "status": "success"
}

数据库中孙实体状态 #

mysql> select * from gson_test_child where id = 1;
+----+---------------------+---------------------+---------------------+---------+--------+--------------+
| id | created_at          | updated_at          | deleted_at          | deleted | status | name         |
+----+---------------------+---------------------+---------------------+---------+--------+--------------+
|  1 | 2022-07-22 14:24:15 | 2022-07-22 14:24:15 | 2022-07-26 11:31:30 |       1 | active | test_child_1 |
+----+---------------------+---------------------+---------------------+---------+--------+--------------+

补充说明 #

• 显式使用关联表而非@JoinTable，因为关联表包含需暴露的业务字段
• 曾在GsonTestParentToGsonTestChild的gsonTestChild字段添加@Where注解，但未生效
• 可通过原生JOIN FETCH查询实现过滤，但希望通过注解方式解决

解决方案 #

方案1：全局启用Hibernate Filter过滤软删除实体 #

@Where注解在深层关联查询中可能因Hibernate的查询策略（如单独SELECT查询子实体）失效，使用@Filter可全局应用软删除过滤：

1. 在软删除父类上定义过滤器：

@MappedSuperclass
@FilterDef(name = "softDeleteFilter", parameters = @ParamDef(name = "isDeleted", type = Boolean.class))
@Filter(name = "softDeleteFilter", condition = "deleted = :isDeleted")
public class SoftDeletableEntity extends BaseEntity {
    // 原有字段及方法...
}

2. 全局启用过滤器，可通过Spring切面实现：

@Aspect
@Component
public class SoftDeleteFilterAspect {
    @PersistenceContext
    private EntityManager entityManager;

    @Before("execution(* com.yourpackage..service..*(..))")
    public void enableSoftDeleteFilter() {
        entityManager.unwrap(org.hibernate.Session.class)
            .enableFilter("softDeleteFilter")
            .setParameter("isDeleted", false);
    }
}

该方案会对所有继承SoftDeletableEntity的实体生效，包括多层关联的孙实体，且不受查询策略影响。

方案2：强制关联查询时应用@Where过滤 #

调整关联实体中的gsonTestChild字段，通过@Fetch(FetchMode.JOIN)强制Hibernate使用JOIN方式查询子实体，确保@Where条件融入主查询：

@Expose
@ManyToOne(fetch = FetchType.EAGER)
@Fetch(FetchMode.JOIN)
@Where(clause = "deleted=false")
private GsonTestChild gsonTestChild;

默认EAGER fetch可能采用单独SELECT查询子实体，导致@Where条件未被应用，@Fetch(FetchMode.JOIN)会将子实体查询合并到主SQL中，过滤条件自然生效。

额外注意：缓存问题排查 #

若启用了二级缓存，已软删除的实体可能仍存在于缓存中导致查询返回。需在软删除操作时清除对应实体的缓存，或配置过滤器绕过缓存过滤。

内容的提问来源于stack exchange，提问作者MDaigle