删除带有JPA和子查询的条目 [英] deleting entries with JPA and subqueries

问题描述

我刚刚写了一个SQL查询:

I just wrote an sql query :

DELETE FROM basisgegevens.gm_persoonburgstaat pbs 
WHERE (pbs.ingangsdatum, pbs.id_persoon) in (
  SELECT pbs2.ingangsdatum, pbs2.id_persoon 
  FROM basisgegevens.gm_persoonburgstaat pbs2 
  WHERE pbs2.ingangsdatum = pbs.ingangsdatum 
  AND pbs2.id_persoon = :persoonID 
  AND pbs2.id_persoonburgerlijkestaat > pbs.id_persoonburgerlijkestaat);

我需要将其重写为JPQL，但是在子查询中使外部查询无法使用.

I need to rewrite it to JPQL, but am getting stuck with the subquery refrencing the outer query.

public class PersoonBurgerlijkeStaatEntity {
@Column(name = "id_persoonburgerlijkestaat"
private Long identifier;
private Date ingangsdatum;
@ManyToOne
@JoinColumn(name = "id_persoon", referencedColumnName = "id_persoon", nullable = false)
private PersoonEntity persoon;

}

persoon实体具有一个标识符

The persoon entity has an identifier

有人可以帮我重写吗?

谢谢

推荐答案

对此不确定，请尝试一下.

Not sure about this but give a try.

DELETE FROM persoonburgstaat person where (person.ingangsdatum, person identifier) in 
    (select p.ingangsdatum, p.identifier from persoonburgstaat p
        left join p.persoon per where per.id_persoon = :persoonID 
        AND per.id_persoonburgerlijkestaa > p.identifier)

左连接将进行外部查询

但是要确定的是发布PersoonEntity实体，因为我认为"id_persoonburgerlijkestaa"是列的名称，而不是属性，因此查询将基于该列失败.

But to be more sure post PersoonEntity entity as I think " id_persoonburgerlijkestaa " is the name of the column not the property and query will fail based on that.

这篇关于删除带有JPA和子查询的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！