• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How should JPQL query look like for described scenario?

 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using JPA and mysql.
I have next entities (simplyfied):
Post (id, title, summary, content ...)
Comment(id, title, content, postId ...)


Normally, one post can have many comments.
How to get a list of posts arranged by comments count? Most commented posts first.
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should post the mappings between entities when asking about queries over them.
Do you know how to do the query in plain sql?
What have you tried so far?
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, here are full details:
This is code excerpt from entity Post;

This is code excerpt from entity Comment:

Do you know how to do the query in plain sql?

This is plain SQL command in terminal that returns result I want:

You can see named query I have tried inside entity `Post`.
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can't return Comments and the count in the select because the values are only going to be returned as List<Post> which doesn't have those extra columns.
You could always use that same MySQL query as a native query.
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
(I am not yet so familiar with JPQL, this is first time I work with it)
Is it possible to do this with `left join statement` (as a named query)?

Or, how to use it as native mysql?
This is method in DAO object for this query:

Actually, I would like to create a list of all posts, arranged by number of post comments - most commented posts first.
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This article (http://www.oracle.com/technetwork/articles/vasiliev-jpql-087123.html) explains how to use native queries
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have changed code to use native query, so the method now looks like this:


I AM getting number of posts message printed in console window, so data are being extracted from database successfully.
But then, in JSP page, where I need to read these data, with: application throws next exception (log messages excerpt from console window):


and error in browser looks like this:
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What code do you have at ShowPostsController.java line 40? The error suggests that you are trying to change the string 'title' to a number using Integer.parseInt at that line.
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is ShowPostsController content:
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you have in your JSP?
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is code excerpt in JSP that should read data (and it works for other queries I have in app):
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's difficult to say where the error is coming from without seeing the rest of the JSP. The likelihood is that there is a line somewhere in your JSP that does something like ${posts.title} but this is all guesswork. If you can't post the JSP then you will have to check the JSP on your own. Look for all references to posts or title and verify that you are using the correct syntax.
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I have parsed my JSP page content into several parts, put that parts in separate JSP's and included it in main JSP. And, I use this pattern for more JSP pages. Basicly only the main content of the JSP page changes.
So, JSP page looks like this:

Only in the homeContent.jsp I am reading these data (post data). So homeContent.jsp looks like this:

 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try putting the ${posts[1].title} in a using . If you get the error then post the complete statcktrace.
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Same error with .
Stack trace looks like this:

(btw, is it possible somehow to wrap long posts like this one in a limited size frame with slider on a side?)
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you notice all the errors at the start of your stacktrace which say

SEVERE: Servlet.service() for servlet [jsp] in context with path [/BlogApp] threw exception [Unable to compile class for JSP:

An error occurred at line: [41] in the generated java file: [/home/vladimir/javatools/apache-tomee-plus-1.5.2/work/Catalina/localhost/BlogApp/org/apache/jsp/home_jsp.java]
This method must return a result of type Map<String,Long>

An error occurred at line: [203] in the generated java file: [/home/vladimir/javatools/apache-tomee-plus-1.5.2/work/Catalina/localhost/BlogApp/org/apache/jsp/home_jsp.java]
This method must return a result of type boolean
?

You should fix those errors first.

 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I saw those errors, but I first posted stack trace here before fixing them My apologies. I have updated stack trace (edited same post), take a look now.
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you use a c:foreach to print all the post titles does it work?
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Same thing when using 'foreach'.
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vladimir Razov wrote:Same thing when using 'foreach'.


Post the code you used with the foreach
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use cout to display the values. Also why do you have a space in your el instead of.
Try displaying another property like p.summary to verify that you are running new code not the old code.

 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Same with cout, and without that space character.

I think the issue is related to type of objects that database returns, Here is why I think that:
Beside this method that should return most commented posts, I have another method that returns newest posts. That method returns valid results. Method look:


where named query is defined as:

You can see, I am printing number of elements in the result list, and the title of the first element in the console. Both methods are used within same JSP, so JSP content should be valid.

Now, I have put these same lines (that prints messages in console) in the method 'getMostCommentedPosts' (problematic one), and the line that should print title of the first element in the result list fails.
java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to mbs2.blog.server.entity.Post
Here is full stack trace look:
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Earlier you said the database results are returned and printed successfully and said nothing about the class cast exception. You need to pass a second parameter Post.class to the method createNativeQuery to get a List<Post>.
 
Vladimir Razov
Ranch Hand
Posts: 42
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
(Database results ARE returned correctly when I type this sql command in terminal/command prompt. But now, I have added line that should print title of the post in the console window (System.out.println...), and thats when this cast exception occured.)

You need to pass a second parameter Post.class to the method createNativeQuery to get a List<Post>

YES, you are correct, I forgot to pass this parameter, and that was causing all the troubles. (don't know how I have missed that). It works now .
Thanks A LOT for you time and help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic