使用 JPA 删除孤儿
我使用 CascadeType.PERSIST 建立一对一关系。随着时间的推移，这会积累大量尚未删除的子记录，以至于反映在性能中。 现在我希望添加一些代码来清理数据库，删除父级未引用的所有子记录。目前我们正在谈论 400K+ 记录，我需要在所有客户安装上运行代码，以确保他们不会遇到同样的问题。
我认为最好的解决方案是运行一个命名查询（因为我们支持两个数据库）来删除必要的记录，这就是我遇到问题的地方，因为我应该如何在 JPQL 中编写它？
我想要的结果可以像下面的sql语句一样定义，不幸的是它不能在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);
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);
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）