引用关系维护

在业务中，经常出现以下这种情形：

A--->B--->C

A引用B，B引用C。

A可能有增删改查接口，B也有增删改查接口。

此时，如果删除B，会导致A业务在展示时报错。另外，如果在删除B时，不删除C，就会导致C是一个孤立的脏数据，永远存在于数据库中，但是又没有用。

为了解决上面的2个问题，这里提供了一个表，讲述的是，引用者和资源之间的关系。

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[busi_refer_resource]') AND type in (N'U')) DROP TABLE [dbo].[busi_refer_resource];
CREATE TABLE [dbo].[busi_refer_resource](
    id VARCHAR(50) NOT NULL,
    referer_table_name VARCHAR(200) NOT NULL,
    referer_column_name VARCHAR(200) NOT NULL,
    referer_busi_name VARCHAR(200) NOT NULL,
    resource_table_name VARCHAR(200) NOT NULL,
    resource_column_name VARCHAR(200) NOT NULL,
    resource_busi_name VARCHAR(200) NOT NULL,
    before_delete_res INT NOT NULL,
    after_delete_res INT NOT NULL,
    error_scene_type INT,
    PRIMARY KEY (id)
);

EXEC sp_addextendedproperty 'MS_Description', '业务中引用资源', 'SCHEMA', dbo, 'table', busi_refer_resource, null, null;
EXEC sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', id;
EXEC sp_addextendedproperty 'MS_Description', '引用者的表名', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', referer_table_name;
EXEC sp_addextendedproperty 'MS_Description', '引用者的字段名', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', referer_column_name;
EXEC sp_addextendedproperty 'MS_Description', '引用者的业务名称', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', referer_busi_name;
EXEC sp_addextendedproperty 'MS_Description', '资源的表名', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', resource_table_name;
EXEC sp_addextendedproperty 'MS_Description', '资源的字段名', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', resource_column_name;
EXEC sp_addextendedproperty 'MS_Description', '资源的业务名称', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', resource_busi_name;
EXEC sp_addextendedproperty 'MS_Description', '在删除资源之前的操作;1：允许删除；2：阻止删除', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', before_delete_res;
EXEC sp_addextendedproperty 'MS_Description', '在删除资源之后的操作;1：不做任何操作；2：引用者也被删除', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', after_delete_res;
EXEC sp_addextendedproperty 'MS_Description', '检测时，对应的异常场景类型', 'SCHEMA', dbo, 'table', busi_refer_resource, 'column', error_scene_type;

删除资源之前，判断是否该资源被引用

下面是关键代码：

public class TableColumnRecord {
    private String tableName;

    private String columnName;

    private String preventReason;

    private Object columnValue;

}


// ------------------------------------

@Component
public class ResourceRoleExer {

    @PersistenceContext
    private EntityManager entityManager;

    public boolean checkBeforeDelete(TableColumnRecord tableColumnRecord) {
        String tableName = tableColumnRecord.getTableName();
        String columnName = tableColumnRecord.getColumnName();

       String sql = "SELECT * FROM busi_refer_resource WHERE resource_table_name = :tablename and resource_column_name = :columnname and before_delete_res=2";
       List<BusiReferResourcePO> resultList = entityManager.createNativeQuery(sql, BusiReferResourcePO.class)
                .setParameter("tablename", tableName).setParameter("columnname", columnName).getResultList();
        if (CollectionUtils.isEmpty(resultList)) {
            return true;
        } else {
            for (BusiReferResourcePO busiReferResourcePO : resultList) {
                String dataSql = String.format("SELECT * FROM %s WHERE %s = :columnValue", busiReferResourcePO.getRefererTableName(), busiReferResourcePO.getRefererColumnName());
                List<Object> dataResultList = entityManager.createNativeQuery(dataSql).setParameter("columnValue", tableColumnRecord.getColumnValue()).getResultList();
                if (CollectionUtils.isNotEmpty(dataResultList)) {
                    tableColumnRecord.setPreventReason("准备删除的"+busiReferResourcePO.getResourceBusiName()+"，已被"+busiReferResourcePO.getRefererBusiName()+"引用了，无法直接删除");
                    return false;
                }
            }
            return true;
        }
    }

下面，再展示一个，批量检查的关键代码

public class BatchTableColumnRecord {
    private String tableName;

    private String columnName;

    private String preventReason;

    private List<?> columnValues;
    
}

// -----------------------------------

public boolean batchCheckBeforeDelete(BatchTableColumnRecord batchTableColumnRecord) {
        String tableName = batchTableColumnRecord.getTableName();
        String columnName = batchTableColumnRecord.getColumnName();

        String sql = "SELECT * FROM busi_refer_resource WHERE resource_table_name = :tablename and resource_column_name = :columnname and before_delete_res=2";
        List<BusiReferResourcePO> resultList = entityManager.createNativeQuery(sql, BusiReferResourcePO.class)
                .setParameter("tablename", tableName).setParameter("columnname", columnName).getResultList();
        if (CollectionUtils.isEmpty(resultList)) {
            return true;
        } else {
            for (BusiReferResourcePO busiReferResourcePO : resultList) {
                String dataSql = String.format("SELECT * FROM %s WHERE %s in (:columnValues)", busiReferResourcePO.getRefererTableName(), busiReferResourcePO.getRefererColumnName());
                List<Object> dataResultList = entityManager.createNativeQuery(dataSql).setParameter("columnValues", batchTableColumnRecord.getColumnValues()).getResultList();
                if (CollectionUtils.isNotEmpty(dataResultList)) {
                    batchTableColumnRecord.setPreventReason("准备删除的"+busiReferResourcePO.getResourceBusiName()+"，已被"+busiReferResourcePO.getRefererBusiName()+"引用了，无法直接删除");
                    return false;
                }
            }
            return true;
        }
    }

删除引用者后，删除引用的独占资源

A--->B--->C

A引用B，B引用C。

在删除A时，需要考虑，如果B是A的独占资源，不会被A的其他实例共享。那么在删除A实例时，需要将B也一起删掉。

实现思路就是：在删除A之前，找到A有哪些独占资源，然后将这些资源，一起删掉。

关键代码如下：

public interface DeleteReferedResourceService {

    /**
     * 根据id，获取待删除的独占资源
     * @param id
     * @return
     */
    public List<Supplier> getPendingDeleteExclusiveResourceById(String id);

    /**
     * 根据id列表，做简单删除，只有删除，没有任何逻辑
     * @param ids
     * @return
     */
    public int simpleDeleteByIds(List<String> ids);
}


// --------------------

// com.jshxhb.module.scada.biz.service.monpanel.ScadaMonitorPanelServiceImpl#getPendingDeleteExclusiveResourceById

@Override
    public List<Supplier> getPendingDeleteExclusiveResourceById(String id) {
        //
        QScadaPropertyPO qScadaPropertyPO = QScadaPropertyPO.scadaPropertyPO;
        List<ScadaPropertyPO> scadaPropertyPOList = jpaQueryFactory.selectFrom(qScadaPropertyPO)
                .where(qScadaPropertyPO.associaType.eq(ScadaPropertyAssociaTypeEnum.ASSOCIA_MONITOR_PANEL.getTypeCode()).and(qScadaPropertyPO.associaId.eq(id))).fetch();

        List<Supplier> suppliers = new ArrayList<>();
        if (CollectionUtils.isEmpty(scadaPropertyPOList)) {
            return suppliers;
        }
        List<String> propertyIds = scadaPropertyPOList.stream().map(ScadaPropertyPO::getId).collect(Collectors.toList());
        Supplier<Object> supplier = () -> scadaPropertyService.simpleDeleteByIds(propertyIds);
        suppliers.add(supplier);
        for (String propertyId : propertyIds) {
            List<Supplier> list = scadaPropertyService.getPendingDeleteExclusiveResourceById(propertyId);
            if (CollectionUtils.isNotEmpty(list)) {
                suppliers.addAll(list);
            }
        }
        return suppliers;
    }
    
// ------------------------------
// com.jshxhb.module.scada.biz.service.monpanel.ScadaMonitorPanelServiceImpl#deleteMonPanelById

@Override
    public int deleteMonPanelById(String id, String branchId) {
        ScadaMonitorPanelBO scadaMonitorPanelBO = new ScadaMonitorPanelBO();
        scadaMonitorPanelBO.setId(id);
        scadaMonitorPanelBO.setBranchId(branchId);
        List<ScadaMonitorPanelBO> scadaMonitorPanelBOList = select(scadaMonitorPanelBO);
        boolean existed = CollectionUtils.isNotEmpty(scadaMonitorPanelBOList) ? true : false;
        if (!existed) {
            throw new ReqParamInvalidException("该点位面板未找到");
        }
        int affectedRows = 0;
        // 这一步关键，在删除前，先获取到待删除的独占资源
        List<Supplier> list = getPendingDeleteExclusiveResourceById(id);

        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setName("deleteScadaMonitorPanelTx");
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus txStatus = txManager.getTransaction(def);
        try {
            // 下面这一步，是将自身删除掉
            simpleDeleteByIds(List.of(id));
            // 下面这一步，是将独占的资源删除掉
            for (Supplier supplier : list) {
                supplier.get();
            }

            affectedRows += 1;
            // 删除 ScadaProperty
//            scadaPropertyService.deleteAllAssociaProperty(scadaMonitorPanelBO.getId(), scadaMonitorPanelBO.getBranchId());
            txManager.commit(txStatus);
        } catch (Exception e) {
            LOGGER.error("deleteScadaMonitorPanel occur exception:{}", ExceptionUtils.getStackTrace(e));
            txManager.rollback(txStatus);
        }
        if (existed) {
            String companyId = loginUserProvider.getLoginUser().getCompanyId();
            finishRefreshByGraphId(scadaMonitorPanelBOList.get(0).getGraphId(), companyId);
        }
        return affectedRows;
    }