CODEWITHSUNDEEP
javaoracle-databasehibernatecascade
java123999
java123999

Reputation: 7394

Cascading delete in Hibernate: What order to delete from tables?

Within my Java application I am trying to run a cascade delete on a parent object that has children.

When when I run the application I get the following error:

java.sql.SQLIntegrityConstraintViolationException: ORA-02292: integrity constraint  violated - child record found

I have searched for this error and here it states that it is due to: 

You tried to DELETE a record from a parent table (as referenced by a foreign key), but a record in the child table exists.

Do I need to delete all the child tables first? I thought the idea behind cascade delete was that it did this automatically?

Code:

EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("myclasses");
                EntityManager em = entityManagerFactory.createEntityManager();
                Session session = em.unwrap(Session.class);

    Transaction transaction = session.beginTransaction();
            try {

               String hqlDelete = "DELETE FROM product WHERE price='PRICED'";
                Query queryDelete = session.createQuery(hqlDelete);
                queryDelete.executeUpdate();

               transaction.commit();
            } catch (Throwable t) {
                transaction.rollback();
                throw t;
            }

Upvotes: 0

Views: 1803

Answers (1)

Maciej Dobrowolski
Maciej Dobrowolski

Reputation: 12140

What you do is so-called bulk delete. It is performant, but has some drawbacks:

  • does not respect cascade
  • cannot use joins (what is somewhat logical)

You can remove children entities before, what would be most performant solution.

There is also another solution, to make use of Cascade option (actually it's anti-pattern if many rows should be deleted) - you can iterate over entities you want to delete and call Session.delete() for each.

update

Providing that you have @OneToMany association between Parent and Children entity, you can simply ask for all Children which would be affected and remove them beforehand:

session
  .createQuery("DELETE FROM Children c WHERE EXISTS (SELECT p FROM Parent p WHERE p.price='PRICED' AND c IN p.children)")
  .executeUpdate();

Then you can safely remove your Parent entities:

session
  .createQuery("DELETE FROM Parent p WHERE p.price='PRICED'")
  .executeUpdate();

More concise, but way less performant would be to query for all Parent entities you want to delete and then remove them manually:

List<Parent> parents = session
  .createQuery("SELECT p FROM Parent p WHERE p.price='PRICED'");
for (Parent parent : parents) {
    session.delete(parent);
}

Upvotes: 3

Related Questions