对springjpa规范的sql查询

pxiryf3j  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(319)

如何使用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

}
5ssjco0h

5ssjco0h1#

据我所知,我们不能用相同的方法计算和发现,所以我分两步来做。在您的服务层中,逻辑是。

List<Parent> parentList = parentRepository.findAll();
parentList.stream().filter(parent-> kidsRepository.countByParent_Id(parent.id)< parent.maxKids).collect(Collectors.toList());

parentrepository.class类

@Repository
public interface ParentRepository extends JpaRepository<Parent,Integer> {
}

kidsrepository.class类

@Repository
public interface KidsRepository extends JpaRepository<Kids,Integer> {
   Integer countByParent_Id(Integer id);
}

相关问题