[FIXED] JPA cascading delete fails with custom delete method

Issue

I ran into an error with custom delete method in spring data jpa. Basically there's a bag which contains items, and when deleting the bag, all the items in it should be deleted.

Here're the entities:

@Entity
@Table(name = "bag")
public class Bag {
    @Id private Long id;
    @Column("uid") private Long uid;
    @Column("name") private String name;

    @OneToMany(mappedBy = "bag", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Item> items;
}

@Entity
@Table(name = "item")
public class Item {
    @Id private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "bid", referencedColumnName = "id")
    private Bag bag;
}

and the repository:

@Repository
public interface BagRepository extends JpaRepository<Bag, Long> {
    Bag findByUidAndName(Long uid, String name);

    @Transactional
    @Modifying
    @Query(value = "DELETE FROM `bag` WHERE `uid` = :uid AND `name` = :name", nativeQuery = true)
    void deleteByUidAndName(@Param("uid") Long uid, @Param("name") String name);
}

When I call bagRepository.deleteByUidAndName(uid, name), I get an Exception from hibernate relating to foreign key constraint. Setting spring.jpa.show-sql=true shows it does not try to delete the items first before deleting the bag.

However, if I call Bag bag = bagRepository.findByUidAndName(uid, name) and then bagRepository.deleteById(bag.getId()) everything is fine.

I'd like to know what's wrong about customizing this delete method and how to fix it.

Solution

In case deleting entity via bagRepository.deleteById(bag.getId()) Hibernate will remove from parent to child entity because you defined cascade = CascadeType.ALL on the relation. When we perform some action on the target entity, the same action will be applied to the associated entity. Logic is in Hibernate and does not utilize database cascades.

    @OneToMany(mappedBy = "bag", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Item> items;

In case bagRepository.deleteByUidAndName(uid, name) you defined native query for deletion. This means that Hibernate logic will be ignored and the query will be executed as-is. You are working directly with the database in this case and to delete record via native SQL you need to define ON DELETE CASCADE on the database level to have similar logic.

    @Query(value = "DELETE FROM `bag` WHERE `uid` = :uid AND `name` = :name", nativeQuery = true)
    void deleteByUidAndName(@Param("uid") Long uid, @Param("name") String name);

Solution 1, @OnDelete(action = OnDeleteAction.CASCADE)

In case you have auto-generated tables you can add Hibernate-specific annotation @OnDelete to the relation. During tables generation ON DELETE CASCADE will be applied to the foreign key constraint.
Relation definition:

    @OneToMany(mappedBy = "bag", cascade = CascadeType.ALL, orphanRemoval = true)
    @OnDelete(action = OnDeleteAction.CASCADE)
    private List<Item> items;

Auto generated constaint:

    alter table item 
       add constraint FK19sn210fxmx43i8r3icevbeup 
       foreign key (bid) 
       references bag 
       on delete cascade

Implemetation:

import org.hibernate.annotations.OnDelete;
import org.hibernate.annotations.OnDeleteAction;

import javax.persistence.*;
import java.util.List;

@Entity
@Table(name = "bag")
public class Bag {
    @Id
    private Long id;
    @Column(name = "uid")
    private Long uid;
    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "bag", cascade = CascadeType.ALL, orphanRemoval = true)
    @OnDelete(action = OnDeleteAction.CASCADE)
    private List<Item> items;
}

Solution 2, @JoinColumn annotation with foreign key ON DELETE CASCADE
Specify foreign key with ON DELETE CASCADE for Item entity

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "bid", referencedColumnName = "id",
            foreignKey = @ForeignKey(
                    name="FK_ITEMS_ID",
                    foreignKeyDefinition = "FOREIGN KEY (ID) REFERENCES ITEM(BID) ON DELETE CASCADE"))
    private Bag bag;

Implementation:

import javax.persistence.*;

@Entity
@Table(name = "item")
public class Item {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "bid", referencedColumnName = "id",
            foreignKey = @ForeignKey(
                    name="FK_ITEMS_ID",
                    foreignKeyDefinition = "FOREIGN KEY (ID) REFERENCES ITEM(BID) ON DELETE CASCADE"))
    private Bag bag;
}

Solution 3, do not use native query
In this case Hibernate logic will be applied.
Define repository like:

@Repository
public interface BagRepository extends JpaRepository<Bag, Long> {
    Bag findByUidAndName(Long uid, String name);

    @Transactional
    @Modifying   
    void deleteByUidAndName(@Param("uid") Long uid, @Param("name") String name);
}

Solution 4, Add ON DELETE CASCADE manually to the database
In case your table is not auto-generated you can manually add ON DELETE CASCADE to the database.

    alter table item 
       add constraint FK_BAG_BID
       foreign key (bid) 
       references bag 
       on delete cascade

Answered By - Eugene
Answer Checked By - Candace Johnson (JavaFixing Volunteer)