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

@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?

Answer

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.