基于Hibernate实现两张表的条件删除优化方案咨询

数据表结构 #

• NoteBhpSetup：字段包括 note_bhp_setup_id（主键，关联中间表）、node_id、created_by
• NoteBhpSetup_NoteDefinition（多对多中间表）：字段包括 note_bhp_setup_id（外键关联NoteBhpSetup）、node_definition_id

需求逻辑 #

• 给定node_definition_id，删除中间表中所有匹配的条目
• 若被删除记录对应的NoteBhpSetup在中间表中已无任何关联记录，则删除这些NoteBhpSetup行数据

当前实现（三次数据库访问） #

@Override
public void removeBhpSetupForNote(String noteDefinitionId) {
    Query select = getEntityManager().createNativeQuery(
            "SELECT note_bhp_setup_id FROM NoteBhpSetup_NoteDefinition WHERE note_definition_id = :noteDefinitionId");
    select.setParameter("noteDefinitionId", noteDefinitionId);
    List<Long> noteBhpSetupIds =  select.getResultList();

    SQLQuery delete= NativeQueryUtil.getSQLQueryWithTableName(
            "DELETE FROM NoteBhpSetup_NoteDefinition WHERE note_definition_id = :noteDefinitionId",
            getEntityManager(),"NoteBhpSetup_NoteDefinition");
    delete.setParameter("noteDefinitionId", noteDefinitionId);
    delete.executeUpdate();
    this.flush();

    deleteNoteBhpSetupWithNoNoteDefinitions(noteBhpSetupIds);
}

private void deleteNoteBhpSetupWithNoNoteDefinitions(List<Long> noteBhpSetupIds) {
    SQLQuery delete = NativeQueryUtil.getSQLQueryWithTableName(
            "DELETE noteBhp FROM NoteBhpSetup noteBhp WHERE noteBhp.note_bhp_setup_id IN (:noteBhpSetupIds) AND (SELECT COUNT(ndef.note_bhp_setup_id) " +
                    "            FROM   NoteBhpSetup_NoteDefinition ndef " +
                    "            WHERE  noteBhp.note_bhp_setup_id = ndef.note_bhp_setup_id) = 0",
            getEntityManager(),"NoteBhpSetup");
    delete.setParameter("noteBhpSetupIds", noteBhpSetupIds);
    delete.executeUpdate();
    this.flush();
}

对应的实体类代码：

@Entity
@DynamicInsert
@DynamicUpdate
@AttributeOverride(name = AbstractGeneratedNumericIdEntity.ID_NAME, column = @Column(name = NoteBhpSetup.NOTE_BHP_SETUP_ID))
@Cache(region = CacheRegion.Names.TRU_CARE_ENTITY_CACHE_REGION, usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class NoteBhpSetup extends AbstractGeneratedNumericIdEntity {
    private static final long serialVersionUID = 1L;

    public static final String BHP_NODE = "bhpNode";
    public static final String NOTE_BHP_SETUP_ID = "note_bhp_setup_id";
    public static final String IS_SYSTEM_COLUMN_NAME = "is_system";
    public static final String NOTE_BHP_SETUP_NOTE_DEFINITION = "NoteBhpSetup_NoteDefinition";

    @Column(name = IS_SYSTEM_COLUMN_NAME, nullable = false)
    private boolean isSystem = false;

    @OneToOne
    @JoinColumn(name = BhpNode.BHP_NODE_ID_COLUMN, nullable = false, updatable = false, unique = true)
    private BhpNode bhpNode;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable( name = NOTE_BHP_SETUP_NOTE_DEFINITION,
            joinColumns = @JoinColumn( name = NoteBhpSetup.NOTE_BHP_SETUP_ID, nullable = false ),
            inverseJoinColumns = @JoinColumn( name = NoteDefinition.NOTEDEF_ID_COLUMN, nullable = false )
    )
    private Set<NoteDefinition> noteDefinitions = new HashSet<>();

    protected NoteBhpSetup() {}

    public NoteBhpSetup(final BhpNode bhpNode) {
        this.bhpNode = bhpNode;
    }

    public boolean getIsSystem() {
        return isSystem;
    }

    public void setIsSystem(final boolean isSystem) {
        this.isSystem = isSystem;
    }

    public BhpNode getBhpNode() {
        return bhpNode;
    }

    public void setBhpNode(final BhpNode bhpNode) {
        this.bhpNode = bhpNode;
    }

    public Set<NoteDefinition> getNoteDefinitions() {
        return noteDefinitions;
    }

    public void setNoteDefinitions(Set<NoteDefinition> noteDefinitions) {
        this.noteDefinitions = noteDefinitions;
    }

    /**
     * Use bhpNode and note definitions to generate hashCode
     */
    @Override
    public int hashCode() {
        return Objects.hashCode(getIdLazy(bhpNode), noteDefinitions);
    }

    @Override
    @SuppressWarnings(value = "BC_EQUALS_METHOD_SHOULD_WORK_FOR_ALL_OBJECTS", justification = "if obj is not an instance of AbstractGeneratedNumericIDEntity the Hibernate.getClass(this) if statement is true and this method returns false")
    public boolean equals(final Object obj) {
        // Check the state of the object
        boolean isEqual;
        if (this == obj) {
            // The object reference is the same
            isEqual = true;
        } else if ((obj == null) || !Hibernate.getClass(this).equals(Hibernate.getClass(obj))) {
            // The object does not have the concrete class as its base
            isEqual = false;
        } else {
            NoteBhpSetup other = (NoteBhpSetup) obj;
            isEqual =  getIdLazy(bhpNode).equals(getIdLazy(other.getBhpNode())) && noteDefinitions.equals(other.getNoteDefinitions());
        }
        // Return the equality status
        return isEqual;
    }
}

优化方案（减少数据库访问次数） #

方案1：合并为两次数据库操作（无需预查询ID） #

去掉预查询note_bhp_setup_id的步骤，直接通过NOT EXISTS判断NoteBhpSetup是否还有关联记录：

@Override
public void removeBhpSetupForNote(String noteDefinitionId) {
    // 第一步：删除中间表匹配记录
    SQLQuery deleteJoinTable = NativeQueryUtil.getSQLQueryWithTableName(
            "DELETE FROM NoteBhpSetup_NoteDefinition WHERE note_definition_id = :noteDefinitionId",
            getEntityManager(), "NoteBhpSetup_NoteDefinition");
    deleteJoinTable.setParameter("noteDefinitionId", noteDefinitionId);
    deleteJoinTable.executeUpdate();

    // 第二步：删除所有无关联的NoteBhpSetup
    SQLQuery deleteSetup = NativeQueryUtil.getSQLQueryWithTableName(
            "DELETE FROM NoteBhpSetup nbs " +
            "WHERE NOT EXISTS (" +
            "    SELECT 1 FROM NoteBhpSetup_NoteDefinition nd " +
            "    WHERE nd.note_bhp_setup_id = nbs.note_bhp_setup_id" +
            ")",
            getEntityManager(), "NoteBhpSetup");
    deleteSetup.executeUpdate();

    this.flush();
}

说明：

• 减少一次数据库查询操作，整体仅两次数据库访问
• 逻辑简洁直接，适合业务上允许删除所有无关联NoteBhpSetup的场景

方案2：CTE合并查询与删除（精准影响本次操作的记录） #

使用数据库CTE（公共表表达式）在一次调用中完成中间表删除和关联ID查询，再针对性删除无关联的记录：

@Override
public void removeBhpSetupForNote(String noteDefinitionId) {
    EntityTransaction tx = getEntityManager().getTransaction();
    if (!tx.isActive()) {
        tx.begin();
    }

    try {
        // 一次操作完成中间表删除+获取受影响的note_bhp_setup_id
        SQLQuery deleteAndGetIds = NativeQueryUtil.getSQLQueryWithTableName(
                "WITH DeletedSetupIds AS (" +
                "    SELECT note_bhp_setup_id FROM NoteBhpSetup_NoteDefinition WHERE note_definition_id = :noteDefinitionId" +
                ")" +
                "DELETE FROM NoteBhpSetup_NoteDefinition WHERE note_definition_id = :noteDefinitionId;" +
                "SELECT note_bhp_setup_id FROM DeletedSetupIds;",
                getEntityManager(), "NoteBhpSetup_NoteDefinition");
        deleteAndGetIds.setParameter("noteDefinitionId", noteDefinitionId);
        List<Long> affectedSetupIds = deleteAndGetIds.getResultList();

        // 删除本次操作涉及的、已无关联的NoteBhpSetup
        if (!affectedSetupIds.isEmpty()) {
            SQLQuery deleteSetup = NativeQueryUtil.getSQLQueryWithTableName(
                    "DELETE FROM NoteBhpSetup nbs " +
                    "WHERE nbs.note_bhp_setup_id IN (:setupIds) " +
                    "AND NOT EXISTS (" +
                    "    SELECT 1 FROM NoteBhpSetup_NoteDefinition nd " +
                    "    WHERE nd.note_bhp_setup_id = nbs.note_bhp_setup_id" +
                    ")",
                    getEntityManager(), "NoteBhpSetup");
            deleteSetup.setParameter("setupIds", affectedSetupIds);
            deleteSetup.executeUpdate();
        }

        tx.commit();
        this.flush();
    } catch (Exception e) {
        if (tx.isActive()) {
            tx.rollback();
        }
        throw e;
    }
}

说明：

• 仅针对本次操作涉及的NoteBhpSetup进行检查，避免误删其他无关联记录
• 通过CTE将两次数据库操作合并为一次，进一步减少数据库往返次数

方案3：利用JPA关联映射自动处理 #

如果NoteDefinition实体配置了反向关联，可通过JPA实体操作让ORM自动处理中间表和实体删除：

@Override
public void removeBhpSetupForNote(String noteDefinitionId) {
    NoteDefinition noteDef = getEntityManager().find(NoteDefinition.class, noteDefinitionId);
    if (noteDef == null) {
        return;
    }

    List<NoteBhpSetup> setupsToDelete = new ArrayList<>();
    // 遍历关联的NoteBhpSetup，移除当前NoteDefinition并标记空关联的实体
    for (NoteBhpSetup setup : noteDef.getNoteBhpSetups()) {
        setup.getNoteDefinitions().remove(noteDef);
        if (setup.getNoteDefinitions().isEmpty()) {
            setupsToDelete.add(setup);
        }
    }

    // 删除空关联的NoteBhpSetup
    setupsToDelete.forEach(getEntityManager()::remove);
    getEntityManager().flush();
}

说明：

• 需要在NoteDefinition实体中添加反向@ManyToMany关联：@ManyToMany(mappedBy = "noteDefinitions") private Set<NoteBhpSetup> noteBhpSetups;
• 无需编写原生SQL，代码更易维护，适合数据量较小的场景

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