Conditionally delete from 2 tables using Hibernate

Question

I have 2 tables: NoteBhpSetup Columns: note_bhp_setup_id, node_id, created_by (note_bhp_setup_id is FK to below table)

NoteBhpSetup_NoteDefinition Columns: note_bhp_setup_id, node_definition_id

I need to implement below logic:

• For a given node_definition_id, delete all entries from NoteBhpSetup_NoteDefinition.
• IF, for the deleted records, NoteBhpSetup has no associated rows left in NoteBhpSetup_NoteDefinition, then delete those rows from NoteBhpSetup

Here is the logic I have implemented:

@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();
}

and below is how my Entity looks like :

@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;
    }
}

I feel like this is not the best way to do it, as I am hitting the database 3 times. Please can someone point me to a better way of implementing this logic.

Answer 1

I am not sure if this is the best solution but you can do something like this. You can first check if you need to delete data from one table or both.

SELECT t1.note_bhp_setup_id, COUNT(t1.note_bhp_setup_id) FROM notebhpsetup_notedefinition t1 
INNER JOIN notebhpsetup_notedefinition t2
ON t1.note_bhp_setup_id = t2.note_bhp_setup_id AND  t1.node_definition_id = ?
GROUP BY t1.note_bhp_setup_id

if COUNT(t1.note_bhp_setup_id) > 1 for any of the returned row, you need to delete only from the notebhpsetup_notedefinition table.
else delete all (t1.note_bhp_setup_id) rows from notebhpsetup_notedefinition and note_bhp_setup_id tables.