How to 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 get only those blogs and posts for which blogMember really commented. 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 included in Inner, so only this line will be retrieved, for which the user commented on some blog post. Suppose he joined 3 blogs whose IDs are 1,2,3 (the blogs that the user has joined are located at tblBlogMembers), but the user only commented on blog 2 (say, BlogPostId = 1). Thus, this line will be extracted, but 1.3 will not be, since it will be Inner Join. How to write such a query in JPQL ?

In JPQL, we can only write simple queries, for example say:

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

If objUser comes using:

 em.find(User.class,1); 

Thus, as soon as we get all the blogs (here the blogId represents the blog object) that the user has joined, we can scroll through and do all the fancy stuff. But I do not want to go into this cycle and write all this in my Java code. Instead, I want to leave this for the database engine. So how to write this simple SQL code in JPQL? And what type of object will be returned in the JPQL query? Because I select only a few fields from the whole table. In which class should I bring the result to?

I think I correctly fulfilled my requirement, if not clear, let me know.

UPDATE: In accordance with the answer to pascal, I tried to write a JPQL query for the above SQL query. I ran into a little problem. This query works, 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 change 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 request does not work. How can I solve this problem?

Answer 1

Ok, this is the final answer. It took one hour to create this line. During this hour, I had many strange errors, but now my concepts are clear enough:

 @NamedQuery(name = "BlogMembers.findBlogsOnWhichCommentsAreMade", query = "SELECT bm.blogId FROM BlogMembers bm INNER JOIN bm.blogId b INNER JOIN b.blogPostsList bp INNER JOIN bp.blogPostCommentList bpc INNER JOIN bpc.blogMembersId bmt WHERE bm.userId = :userId") 

Answer 2

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

This is not the case, and JPQL supports [ LEFT [OUTER] | INNER ] JOIN [ LEFT [OUTER] | INNER ] JOIN . For internal joins, refer to section 4.4.5.1 Internal joins (relationships) of the specification:

4.4.5.1 Internal joins (relationships connected)

Syntax internal join operation

 [ INNER ] JOIN join_association_path_expression [AS] identification_variable 

For example, the request below is attached over the relationship between customers and orders. This type of connection is usually equivalent to combining a foreign key relationship into a database.

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

The INNER keyword may optionally be used:

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

You just need to think about the association between entities.