英文:
Subquery filter sum with one to many in JPA
问题
我有两个实体:
@Entity
class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<Contract> contracts = new HashSet<>();
}
和
@Entity
class Contract {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private Integer numberOfClaims;
}
现在我想要构建一个javax.persistence.criteria.Predicate
来查找所有具有大于 x 的numberOfClaims
的订单。
英文:
I have 2 entities:
@Entity
class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<Contract> contracts= new HashSet<>();
}
and
@Entity
class Contract {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private Integer numberOfClaims;
}
Now I want to build a javax.persistence.criteria.Predicate
to find all Orders with numberOfClaims
greater then x.
答案1
得分: 1
尝试:
select o from Order o where
(select sum(c.numberOfClaims) from o.contracts c) > x
在以下 cmobilecom-jpa 开发指南中查看一些相关子查询示例:
https://cmobilecom.com/docs/jpa/latest/devguide/index.html
使用 Criteria API:
(参考章节:“Subquery”的“Correlate To Root”部分)
CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
Root<Order> root = criteriaQuery.from(Order.class);
criteriaQuery.select(root);
criteriaQuery.distinct(true);
// 子查询:关联父查询的根
Subquery<Integer> subquery = criteriaQuery.subquery(Integer.class);
Root<Order> subqueryRoot = subquery.correlate(root);
Join<Order, Contract> contracts = subqueryRoot.join("contracts");
subquery.select(criteriaBuilder.sum(contracts.get("numberOfClaims")));
Predicate restriction = criteriaBuilder.greaterThan(subquery, x);
criteriaQuery.where(restriction);
英文:
Try:
select o from Order o where
(select sum(c.numberOfClaims) from o.contracts c) > x
See some correlated subquery examples in the following cmobilecom-jpa developer guide:
https://cmobilecom.com/docs/jpa/latest/devguide/index.html
Using Criteria API:
(refer to the section: "Correlate To Root" of chapter "Subquery")
CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
Root<Order> root = criteriaQuery.from(Order.class);
criteriaQuery.select(root);
criteriaQuery.distinct(true);
// Subquery: correlate the root of parent query
Subquery<Integer> subquery = criteriaQuery.subquery(Integer.class);
Root<Order> subqueryRoot = subquery.correlate(root);
Join<Order, Contract> contracts = subqueryRoot.join("contracts");
subquery.select(criteriaBuilder.sum(contracts.get("numberOfClaims")));
Predicate restriction = criteriaBuilder.greaterThan(subquery, x);
criteriaQuery.where(restriction);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论