I am having problem with building a query with JPA Criteria API.
Entities and significant properties:
@Entity
public class Post {
@Id int id;
@OneToMany (mappedBy = "post") Set<Comment> comments;
//...
}
@Entity
public class Comment {
@Id int id;
@ManyToOne Post post;
//...
}
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
But function SIZE(...)
can not be used to be ordered by it in JPQL
.
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();
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) returns an Expression<Integer>
that you may use in the ORDER BY clause. This line of code:
p.get("comments")
..returns a Path<X>
which extends Expression<X>
so it is safe to use the returned value as an argument to Collection.size().
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.
Try this:
cq.orderBy(cb.desc(cb.size(p.<Collection>get("comments"))));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With