How do i write this jpql query? java

Posted by Nitesh Panchal on Stack Overflow See other posts from Stack Overflow or by Nitesh Panchal
Published on 2010-05-16T14:00:27Z Indexed on 2010/05/16 14:10 UTC
Read the original article Hit count: 338

Filed under:
|

Hello, Say i have 5 tables,

tblBlogs     tblBlogPosts     tblBlogPostComment    tblUser    tblBlogMember
BlogId        BlogPostsId       BlogPostCommentId   UserId      BlogMemberId
BlogTitle     BlogId            CommentText         FirstName   UserId
              PostTitle         BlogPostsId                     BlogId
                                 BlogMemberId

Now i want to retrieve only those blogs and posts for which blogMember has actually commented. So in short, how do i write this plain old sql :-

Select b.BlogTitle, bp.PostTitle, bpc.CommentText from tblBlogs b Inner join tblBlogPosts bp on b.BlogId = bp.BlogId Inner Join tblBlogPostComment bpc on bp.BlogPostsId = bpc.BlogPostsId Inner Join tblBlogMember bm On bpc.BlogMemberId = bm.BlogMemberId Where bm.UserId = 1;

As you can see, everything is Inner join, so only that row will be retrieved for which the user has commented on some post of some blog. So, suppose he has joined 3 blogs whose ids are 1,2,3 (The blogs which user has joined are in tblBlogMembers) but the user has only commented in blog 2 (of say BlogPostId = 1). So that row will be retrieved and 1,3 won't as it is Inner Join. How do i write this kind of query in jpql?

In jpql, we can only write simple queries like say :-

Select bm.blogId from tblBlogMember Where bm.UserId = objUser;

Where objUser is supplied using :-

em.find(User.class,1);

Thus once we get all blogs(Here blogId represents a blog object) which user has joined, we can loop through and do all fancy things. But i don't want to fall in this looping business and write all this things in my java code. Instead, i want to leave that for database engine to do. So, how do i write the above plain sql into jpql? and what type of object the jpql query will return? because i am only selecting few fields from all table. In which class should i typecast the result to?

I think i posted my requirement correctly, if i am not clear please let me know. Thanks in advance :).

© Stack Overflow or respective owner

Related posts about java

Related posts about jpql