构建 JPA Criteria API 查询 - 按集合中的元素数量排序 [英] Building JPA Criteria API query - sorting by number of elements in collection

问题描述

我在使用 JPA Criteria API 构建查询时遇到问题.

I am having problem with building a query with JPA Criteria API.

实体和重要属性:

@Entity
public class Post {
    @Id int id;
    @OneToMany (mappedBy = "post") Set<Comment> comments;
    //...
}

@Entity
public class Comment {
    @Id int id;
    @ManyToOne Post post;
    //...
}

我需要一个查询，该查询将返回按评论数量排序的 db 中的所有帖子(Post 中的 OneToMany).起初我认为这可以用 JPQL 实现，例如:

I need a query that will return all posts from db ordered by number of comments (OneToMany in Post). At first I thought this can be implemented with JPQL like:

SELECT p 
FROM Post p 
ORDER BY SIZE(p.comments) DESC

但是函数SIZE(...)不能在JPQL中使用它来排序.

But function SIZE(...) can not be used to be ordered by it in JPQL.

所以，我找到了 JPA Criteria API，并尝试了以下操作:

So, I found about JPA Criteria API, and tried following:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Post> cq = cb.createQuery(Post.class);
Root<Post> p = cq.from(Post.class);
cq.select(p);
cq.orderBy(cb.desc(p.get("comments")));
List<Post> resultList = em.createQuery(cq).getResultList();

通过这个查询，我没有得到正确的结果.我知道我缺少获取集合评论"的 size，但不知道如何添加该部分.我对 JPA Criteria API 不是很熟悉.这个查询应该如何让所有帖子按其 comments 字段(集)的大小排序?

With this query I am not getting proper results. I am aware that I am missing getting size of the set 'comments', but don't know how to add that part. I am not really familiar with JPA Criteria API. How should this query look to get all posts ordered by size of its comments field(set)?

推荐答案

CriteriaBuilder.size(Expression) 返回您可以在 ORDER BY 子句中使用的 Expression.这行代码:

CriteriaBuilder.size(Expression) returns an Expression<Integer> that you may use in the ORDER BY clause. This line of code:

p.get("comments")

..返回一个Path，它扩展了Expression，因此使用返回值作为Collection.size()的参数是安全的.

..returns a Path<X> which extends Expression<X> so it is safe to use the returned value as an argument to Collection.size().

然而，前面引用的代码行使用了 Path.get() 的特定重载版本，这将使编译器无法推断类型参数 X.相反，类型参数将被假定为 对象.但是 Collection.size() 已经将他的 Expression-parameter 声明为一个参数化类型"，其上限"为 Collection(这没有准确反映在我的回答中第一次引用 CriteriaBuilder.size() 时，StackOverflow 坚持从方法签名中删除类型.请参阅 JavaDocs！).所以我们必须明确提供类型参数.

However, the previously quoted line of code is using a particular overloaded version of Path.get() which will make it impossible for the compiler to infer type parameter X. Instead, the type argument will be assumed to be Object. But Collection.size() has declared his Expression-parameter to be a "parameterized type" with an "upper bound" of Collection (this is not reflected accurately in the first reference to CriteriaBuilder.size() in my answer, StackOverflow insist on erasing the type from the method signature. Please see the JavaDocs instead!). So we must provide the type argument explicitly.

试试这个:

cq.orderBy(cb.desc(cb.size(p.<Collection>get("comments"))));

这篇关于构建 JPA Criteria API 查询 - 按集合中的元素数量排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！