Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 915
Jean Logeart Avatar asked Oct 19 '11 17:10

Jean Logeart


People also ask

What is JPA orphanRemoval?

orphanRemoval is an entirely ORM-specific thing. It marks "child" entity to be removed when it's no longer referenced from the "parent" entity, e.g. when you remove the child entity from the corresponding collection of the parent entity.

How does JPA delete work?

In JPA, to delete an entity, the entity itself must be managed, meaning that it is present in the persistence context. This means that the calling application should have already loaded or accessed the entity and is now issuing a command to remove it.

How do I delete multiple records in JPA?

First of all you need to create a jpa query method that brings all records belong to id. After that you can do deleteAll() operation on List.

What is orphanRemoval?

Such target entities are considered “orphans,” and the orphanRemoval attribute can be used to specify that orphaned entities should be removed. For example, if an order has many line items and one of them is removed from the order, the removed line item is considered an orphan.


1 Answers

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); 
like image 147
Mikko Maunu Avatar answered Sep 28 '22 03:09

Mikko Maunu