JPA CriteriaBuilder - 按照一对多关系中关联实体的数量排序 [英] JPA CriteriaBuilder - sort by the number of associated entities in a one-to-many relationship
问题描述
我有两个实体Customer和Order,它们是一对多的关系。
对于每位客户,我需要统计相关订单的数量并按照该数字对结果进行排序。
在原生postgres查询中,它看起来像这样:
select cust.id,count(order.id)from customers cust
left outer join order order
on cust.id = order.customer_id
where .... conditions ...
group by cust.id
order通过计数desc;
但是我必须使用CriteriaBuilder来做这件事,因为这个查询是一个使用CriteriaBuilder的更大代码的一部分增加附加条件。在Hibernate中,我可能会使用Projections,但在JPA中找不到任何类似的东西。
使用CriteraBuilder编写查询的任何帮助都将非常感谢。
预先感谢您。
假设实体Customer具有 OneToMany
属性如下:
@OneToMany(mappedBy =customerId )
私人收藏<订单>命令;
您可以使用以下查询:
EntityManager em; //被构建或注入
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery< Tuple> cq = cb.createTupleQuery();
根< Customer> customer = cq.from(Customer.class);
CollectionJoin<客户,订单> orders = customer.join(Customer_.orders,JoinType.LEFT);
cq.select(cb.tuple(customer,cb.count(orders)));
cq.where(...在这里添加一些谓词...);
cq.groupBy(customer.get(Customer_.id));
cq.orderBy(cb.desc(cb.count(orders)));
List< Tuple> result = em.createQuery(cq).getResultList();
for(Tuple t:result){
Customer c =(Customer)t.get(0);
Long cnt =(Long)t.get(1);
System.out.println(Customer+ c.getName()+has+ cnt +orders);
}
上述方法使用元模型。如果你不喜欢它,你可以用orders
和 Customer_.orders
> Customer_.id 与id
。
如果 OneToMany
属性是另一种类型,用适当类型( ListJoin $ c)的集合替换
CollectionJoin
$ c>, SetJoin
, MapJoin
)。
I have two entities Customer and Order in a one-to-many relation. For each customer I need to count the number of associated orders and sort the results by this number. In a native postgres query it looks like this:
select cust.id, count(order.id) from customers cust
left outer join orders order
on cust.id = order.customer_id
where .... conditions ...
group by cust.id
order by count desc;
But I must do this using CriteriaBuilder because this query is part of a larger piece of code that uses CriteriaBuilder to put in additional conditions. In Hibernate I would have probably used Projections, but I can't find anything similar in JPA.
Any help in composing the query using CriteraBuilder would be much appreciated.
Thank you in advance.
Supposing that the entity Customer has a OneToMany
property like this:
@OneToMany(mappedBy = "customerId")
private Collection<Order> orders;
You can use the following query:
EntityManager em; // to be built or injected
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Customer> customer = cq.from(Customer.class);
CollectionJoin<Customer, Order> orders = customer.join(Customer_.orders, JoinType.LEFT);
cq.select(cb.tuple(customer, cb.count(orders)));
cq.where(... add some predicates here ...);
cq.groupBy(customer.get(Customer_.id));
cq.orderBy(cb.desc(cb.count(orders)));
List<Tuple> result = em.createQuery(cq).getResultList();
for (Tuple t : result) {
Customer c = (Customer) t.get(0);
Long cnt = (Long) t.get(1);
System.out.println("Customer " + c.getName() + " has " + cnt + " orders");
}
The above approach uses Metamodel. If you don't like it, you can replace Customer_.orders
with "orders"
and Customer_.id
with "id"
.
If the OneToMany
property is of another type, replace CollectionJoin
with the collection of the proper type (ListJoin
, SetJoin
, MapJoin
).
这篇关于JPA CriteriaBuilder - 按照一对多关系中关联实体的数量排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!