JPA: DELETE WHERE does not delete children and throws an exception

Question

I am trying to delete a large number of rows from MOTHER thanks to a JPQL query.

The Mother class is defined as follows:

@Entity
@Table(name = "MOTHER")
public class Mother implements Serializable {

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "mother", 
               orphanRemoval = true)
    private List<Child> children;    
}

@Entity
@Table(name = "CHILD")
public class Child  implements Serializable {

    @ManyToOne
    @JoinColumn(name = "MOTHER_ID")
    private Mother mother;    
}

As you can see, the Mother class has "children" and when executing the following query:

String deleteQuery = "DELETE FROM MOTHER WHERE some_condition";
entityManager.createQuery(deleteQuery).executeUpdate();

an exception is thrown:

ERROR - ORA-02292: integrity constraint <constraint name> violated - 
                   child record found

Of course, I could first select all the objects I want to delete and retrieve them into a list before iterating through it to delete all the retrieved object, but the performance of such a solution would just be terrible!

So is there a way to take advantage of the previous mapping to delete all the Mother objects AND all the Child objects associated with them efficiently and without writing first the queries for all the children?

Answer 1

DELETE (and INSERT) do not cascade via relationships in JPQL query. This is clearly spelled in specification:

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

Luckily persist and removal via entity manager do (when there is cascade attribute defined).

What you can do:

• fetch all Mother entity instances that should be removed.
• for each of them call EntityManager.remove().

Code is something like this:

String selectQuery = "SELECT m FROM Mother m WHERE some_condition";  
List<Mother> mothersToRemove = entityManager
    .createQuery(selectQuery)
    .getResultStream()
    .forEach(em::remove);