How to do bulk delete in JPA when using Element Collections?

Question

I am having trouble working out how to do a bulk delete of a Person object using JPA, when the Person objects contain data stored using an @ElementCollection. Any ideas on how to do this would be much appreciated.

@Entity
@Table(name="at_person")
public class Person implements Comparable<Person> {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="id")
    private long id = 0;

    @Column(name="name", nullable = true, length = 128)
    private String name = "";

    @ElementCollection
    @Column(name = "email")
    @CollectionTable(name = "person_email", joinColumns = @JoinColumn(name = "person_id"))
    private Set<String> email = new HashSet<String>();
}

What I am doing at the moment is this, and it fails with a foreign key constraint error:

Query query=em.createQuery("DELETE FROM Person");

Caused by: java.sql.SQLException: integrity constraint violation: foreign key no action; FKCEC6E942485388AB table: PERSON_EMAIL

If it can be a pure JPA annotation rather than a Hibernate annotation that would be a bonus!

Answer 1

I'll let you interpret the part of the JPA 2.0 specification that mentions that a bulk delete operation is not cascaded:

4.10 Bulk Update and Delete Operations

...

A delete operation only applies to entities of the specified class and its subclasses. It does not cascade to related entities.

And the fact is that Hibernate won't cascade a delete to a collection table either. This has been reported in HHH-5529 and the suggested approaches are:

You could also (a) clean up the collection table yourself or (b) use cascading foreign keys in the schema.

In other words, (a) use native SQL or (b) use a cascade delete constraint at the database level - and you'll have to add it manually, I don't think you can use @OnDelete with the @ElementCollection annotation (same story as HHH-4301 IMO).