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);

Answer 2

Have you tried using session.delete(), or equivalent EntityManager.remove() ?

When you use an HQL delete statement to issue a query, you might be bypassing Hibernate's cascading mechanism. Take a look at this JIRA Issue: HHH-368

You will possibly be able to achieve this by:

Mother mother = session.load(Mother.class, id);
// If it is a lazy association, 
//it might be necessary to load it in order to cascade properly
mother.getChildren(); 
session.delete(mother);

I'm not sure right now if it is necessary to initialize the collection in order to make it cascade properly.

Answer 3

You could relay on the RDBMS to delete those Mothers using foreign key constraint. This assumes your generateing your DDL from entities:

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

    @ManyToOne
    @JoinColumn(name = "MOTHER_ID", foreignKey = @ForeignKey(foreignKeyDefinition =
        "FOREIGN KEY(MOTHER_ID) REFERENCES MOTHER(ID) ON DELETE CASCADE",
        value = ConstraintMode.CONSTRAINT))
    private Mother mother;    
}

Answer 4

This is related and may offer a solution if you're using Hibernate.

JPA CascadeType.ALL does not delete orphans

EDIT

Since Oracle is the one giving you the error you could maybe make use of the Oracle cascade delete to get around this. However, this could have unpredictable results: since JPA doesn't realize that you're deleting other records those objects could remain in the cache and be used even though they've been deleted. This only applies if the implementation of JPA you are using has a cache and is configured to use it.

Here is info on using cascade delete in Oracle: http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php

Answer 5

I must say I am not sure if 'delete' in a query won't remove all related onetomany entities in your case as 'MikKo Maunu' says. I would say it would. The problem is (sorry for not trying this out) that what JPA/Hibernate will do is to just execute the 'real sql delete' and while those Mother and Child instances are not managed at that moment, it has no way of knowing which Child instances to remove too. orphanRemoval is a great help, but not in this case. I would

1) try to add 'fetch = FetchType.EAGER' into the onetomany relation (this might be a performance issue too)

2) if 1) does not work, not do all Mother/Child fetching to make everything clear for the JPA layer, and just run a query before the one you use (in the same transaction, but I am not sure if you need not to run 'em.flush' between them)

DELETE FROM Child c WHERE c.mother <the condition>

(Deletes are often a nuisance with JPA/Hibernate and one example I use to denounce the use of ORM, which is essentially an added layer in apps, to make things 'easier'. Only good thing about it is, that ORM issues/bugs are usually discovered during development phase. My money is always on MyBatis which is much cleaner in my opinion.)

UPDATE:

Mikko Maunu is right, bulk delete in JPQL does not cascade. Using two queries as I suggested is fine though.

Tricky thing is, that persistence context (all entities managed by EntityManager) is not synchronized with what bulk delete does, so it (both queries in the case I suggest) should be run in a separate transaction.

UPDATE 2: If using manual remove instead of bulk delete, many JPA providers and Hibernate too provide removeAll(...) method or something similar (non-API) on their EntityManager implementations. It is simpler to use and might be more effective in regards to performance.

In e.g. OpenJPA you only need to cast your EM to OpenJPAEntityManager, best by OpenJPAPersistence.cast(em).removeAll(...)