How can I cascade delete a collection which is part of a JPA entity?

Question

@Entity
public class Report extends Model {

    public Date date;
    public double availability;

    @ElementCollection
    @Cascade(value={CascadeType.ALL})
    public Map<FaultCategory, Integer> categories;      
}

In one of my jobs I have the following code:

int n = MonthlyReport.delete("date = ?", date);

This always fails to delete the entity with the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK966F0D9A66DB1E54". The conflict occurred in database "TFADB", table "dbo.MonthlyReport_categories", column 'MonthlyReport_id'.

How can I specify the mapping so the elements from the categories collection get deleted when the report is deleted?

Solution 1

Cascading delete (and cascading operations in general) is effective only when operation is done via EntityManager. Not when delete is done as bulk delete via JP QL /HQL query. You cannot specify mapping that would chain removal to the elements in ElementCollection when removal is done via query.

ElementCollection annotation does not have cascade attribute, because operations are always cascaded. When you remove your entity via EntityManager.remove(), operation is cascaded to the ElementCollection.

You have to fetch all MonthlyReport entities you want to delete and call EntityManager.remove for each of them. Looks like instead of this in Play framework you can also call delete-method in entity.

Solution 2

The answer provided by J.T. is correct, but was incomplete for me and for sebge2 as pointed out in his/her comment. The combination of @ElementCollection and @OnDelete further requires @JoinColumn().

Follow-up example:

@Entity
public class Report extends Model {
    @Id
    @Column(name = "report_id", columnDefinition = "BINARY(16)")
    public UUID id; // Added for the sake of this entity having a primary key
    public Date date;
    public double availability;

    @ElementCollection
    @CollectionTable(name = "report_category", joinColumns = @JoinColumn(name = "report_id")) // choose the name of the DB table storing the Map<>
    @MapKeyColumn(name = "fault_category_key") // choose the name of the DB column used to store the Map<> key
    @Column(name = "fault_category_value")     // choose the name of the DB column used to store the Map<> value
    @JoinColumn(name = "report_id")            // name of the @Id column of this entity
    @OnDelete(action = OnDeleteAction.CASCADE)
    @Cascade(value={CascadeType.ALL})
    public Map<FaultCategory, Integer> categories;      
}

This setup will create a table called report and another table report_category with three columns: report_id, fault_category_key, fault_category_value. The foreign key constraint between report_category.report_id and report.report_id will be ON DELETE CASCADE. I tested this setup with Map<String, String>.

Solution 3

We found the magic ticket! Add OnDelete(action= OnDeleteAction.CASCADE) to the ElementCollection. This allows us to remove the item from SQL (outside of the entityManager).