How do i write this jpql query?
- by Nitesh Panchal
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 :).
