检查是否引用了对象以防止在不修改数据库的情况下进行软删除

检查是否引用了对象以防止在不修改数据库的情况下进行软删除

本文介绍了检查是否引用了对象以防止在不修改数据库的情况下进行软删除的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如您所见,我正在系统上使用软/逻辑删除:

As you can see I am using soft/logical deletion on my system:

我的实体:

@Where(clause = "deleted='false'")
public class Person {
  //...
}

我的服务:

@Override
public ServiceResponse DeletePerson (Person entity) {
    ServiceResponse sr = new ServiceResponse<>();

    try {
        sr = ValidateDeletePerson(entity); //Business logic treatment
        if (sr.hasError())
            return sr;

        Person dbEntity = GetPerson(entity.getPersonID());
        dbEntity.setDeleted(true);
        _repository.save(dbEntity);

    } catch (Exception ex) {
        sr.getErrors().add(new ServiceError(ex));
    }

    return sr;
}

当用户尝试删除对象时-实际上,如上所示,在数据库上只有布尔值 deleted 切换为 true ,我想在执行此逻辑删除之前,请先检查对象是否被引用到另一个.

When the user try to delete an object - actually only the boolean deleted is switched to true on the DB as I demonstrate above -, I want to check if the object is being referenced to another before doing this logical deletion.

因此,如果该对象正被其他对象使用/引用,我想将此操作视为错误,或采取其他类似措施以防止删除.

So, if the object is being used/referenced by other, I want to catch this action as an error, or another similar treatment to prevent the deletion.

例如,如果将Person with ID 5附加到Student,则用户无法删除此人.

Example, If a Person with ID 5 is attached to a Student, the user cannot delete this person.

什么是防止发生的最佳做法" ?

重要

1)在N个表中引用了人员,不仅在Student上,所以我正在研究一种通用的实现方法(也许您可以告诉休眠检查它吗?)

1) Person is referenced in N tables, not only on Student, so I am investigating a generic way to achieve it (Maybe you can tell hibernate to check it?)

2)这个想法是通过编程实现的,避免在数据库上进行修改.

2) The idea is to do it programatically, avoiding modifications on DB.

推荐答案

您可以在数据库级别使用检查约束.细节是特定于DBMS的,但约束的总体思路是

You could use check constraints at database level. Details are DBMS-specific but general idea for constrain is

CHECK ((select count(*) from studens
                         where studens.id= person.id and person.deleted=false) = 0)

其他解决方案-存档表

您可以通过删除记录并将已删除的记录移至person_archive表中来避免此问题(数据库触发器可以为您完成此操作).使用此解决方案,外键将能够保护数据完整性.如果您的person表很大,则此解决方案也可能会更有效,因为数据库必须读取较少的数据.我将使用person_archive表,除非您需要轻松地从UI中使用Deleted标志还原已删除的记录,否则还原只是翻转标志.使用存档表,还原需要更多工作:

You could avoid this problem by deleting records and moving deleted records to person_archive table (database trigger could do that for you). With this solution foreign key will be able to protect data integrity. If your person table is big this solution may be more efficient too, because database will have to read less data.I would use person_archive table, unless you need to easily restore deleted records from UI With deleted flag, restoring is just flipping flag. With archive table, restoring is more work:

  1. 从存档中选择
  2. 插入数据表
  3. 从存档中删除

如果您无法修改数据库

如果无法更改数据库,则必须在DAO类中进行这些检查(您需要为所有相关实体调用查询).最好确保所有数据库访问都通过这些类进行,否则(如果有人使用直接SQL)则可能导致数据库不变式不成立.

If database cannot be changed then those checks must be done inside your DAO classes (you need to invoke queries for all related entities). Better be sure that all database accesses goes by those classes, otherwise (if somebody uses direct SQL) you may end up with database invariant not holding true.

这篇关于检查是否引用了对象以防止在不修改数据库的情况下进行软删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-15 14:32