How do I write this JPQL query?

Question

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/she 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 the database engine to do. So, how do I write the above plain SQL into JPQL? And what type of object will the JPQL query 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.

UPDATE : As per pascal's answer, I tried to write JPQL query for the above SQL query. I am facing a little problem. This query is working, but is incomplete:

SELECT bm.blogId FROM BlogMembers bm 
    INNER JOIN bm.blogId b 
    INNER JOIN b.blogPostsList bp 
    INNER JOIN bp.blogPostCommentList bpc 
    WHERE bm.userId = :userId

I want to modify this to:

SELECT bm.blogId FROM BlogMembers bm 
    INNER JOIN bm.blogId b 
    INNER JOIN b.blogPostsList bp 
    INNER JOIN bp.blogPostCommentList bpc 
    WHERE bpc.blogMembersId = bm.blogMembersId AND bm.userId = :userId

The above query is not working. How can I solve this problem?

Answer 1

In JPQL, we can only write simple queries (...)

That's not true and JPQL does support [ LEFT [OUTER] | INNER ] JOIN. For Inner Joins, refer to the section 4.4.5.1 Inner Joins (Relationship Joins) of the specification:

4.4.5.1 Inner Joins (Relationship Joins)

The syntax for the inner join operation is

[ INNER ] JOIN join_association_path_expression [AS] identification_variable

For example, the query below joins over the relationship between customers and orders. This type of join typically equates to a join over a foreign key relationship in the database.

SELECT c FROM Customer c JOIN c.orders o WHERE c.status = 1

The keyword INNER may optionally be used:

SELECT c FROM Customer c INNER JOIN c.orders o WHERE c.status = 1

You just need to think association between entities.