Deleting orphans with JPA
- by homaxto
I have a one-to-one relation where I use CascadeType.PERSIST. This has over time build up a huge amount of child records that has not been deleted, to such an extend that it is reflected in the performance.
Now I wish to add some code that cleans up the database removing all the child records that are not referenced by a parent. At the moment we are talking 400K+ records, at I need to run the code on all customer installations just to be sure they do not run into the same problem.
I think the best solution would be to run a named query (because we support two databases) that deletes the necessary records, and this is where I get into problems, because how should I write it in JPQL?
The result I want can be defined like the following sql statement, which unfortunaltely does not run on MySQL.
DELETE FROM child c1
WHERE c1.pk NOT IN (SELECT DISTINCT p.pk FROM child c2
JOIN parent p ON p.child = c2.pk);
