如何使用jpa规范和criteriabuilder编写这个查询?因此,每个父母都有相同数量的maxkids(例如,2个孩子)。一些父母可以有更多的孩子,但在这里我想返回每个父母有较少的孩子,然后在定义maxkids。
SELECT p.* FROM Parent AS p LEFT JOIN
(SELECT k.parent_id, COUNT(k.id) AS counted FROM Kids AS k GROUP BY k.parent_id) AS counts
ON p.id = counts.parent_id
WHERE counts.counted IS NULL OR counts.counted < p.maxKids;
我有两个实体(父母和孩子)
@Entity
public class Parent {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@OneToMany(mappedBy = "parent")
private Set<Kids> kidsOfParent = new HashSet<>();
private Integer maxKids;
//getters and setters
}
@Entity
public class Kids {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.EAGER, optional = false)
@JoinColumn
private Parent parent;
private String name;
//getters and setters
}
1条答案
按热度按时间5ssjco0h1#
据我所知,我们不能用相同的方法计算和发现,所以我分两步来做。在您的服务层中,逻辑是。
parentrepository.class类
kidsrepository.class类