aspose file tools*
The moose likes EJB and other Java EE Technologies and the fly likes Looking for alternative way of JPQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"" Watch "Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"" New topic
Forums: EJB and other Java EE Technologies JPA Certification (OCEJPA)
Author

Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"

Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
I am using JPA, MySQL.
There are following entities:
Post:

Comment:

Then, there is a method in PostDAOBean:

Now,you can see that named query used in this method is SELECT p FROM Post p ORDER BY SIZE(p.comments) DESC.
And this is wrong, because function SIZE(some_collection) cannot be used to be ordered by it in JPQL.
I would like to get all posts from database ordered by number of comments.
How should JPQL query look then?
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
I found out that another way to get all posts from database ordered by number of comments is by using JPA Criteria API.
This is what I am trying now:

I am aware I am missing getting the size of the set comments, but don't know how to add that part. Need help with Criteria API.
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
This is valid code:

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19696
    
  20

Why return null if you have no results? Now you have to check if the result is null before you can loop over it.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
Yes, you are right. Checking if resultList is empty is not needed. Thanks for pointing out.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19696
    
  20

You're welcome.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"