子查询过滤器在JPA中的一对多求和

huangapple go评论72阅读模式
英文:

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&lt;Contract&gt; contracts= new HashSet&lt;&gt;();
}

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) &gt; 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&lt;Order&gt; criteriaQuery = criteriaBuilder.createQuery(Order.class);
Root&lt;Order&gt; root = criteriaQuery.from(Order.class);
criteriaQuery.select(root);
criteriaQuery.distinct(true);

// Subquery: correlate the root of parent query
Subquery&lt;Integer&gt; subquery = criteriaQuery.subquery(Integer.class);
Root&lt;Order&gt; subqueryRoot = subquery.correlate(root);
Join&lt;Order, Contract&gt; contracts = subqueryRoot.join(&quot;contracts&quot;);
subquery.select(criteriaBuilder.sum(contracts.get(&quot;numberOfClaims&quot;)));

Predicate restriction = criteriaBuilder.greaterThan(subquery, x);
criteriaQuery.where(restriction);

huangapple
  • 本文由 发表于 2020年10月22日 20:01:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/64481785.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定