Java 我如何编写这个 JPQL 查询？

How do I write this JPQL query?

提问by TCM

Say I have 5 tables,

假设我有 5 张桌子，

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?

现在我只想检索那些 blogMember 实际评论过的博客和帖子。简而言之，我该如何编写这个普通的旧 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?

如您所见，一切都是内连接，因此只会检索用户对某个博客的某些帖子发表评论的那一行。因此，假设他/她加入了 3 个 id 为 1,2,3 的博客（用户加入的博客在 tblBlogMembers 中），但用户只在博客 2 中发表了评论（比如 BlogPostId = 1）。因此该行将被检索，而 1,3 不会因为它是内部连接。我如何在JPQL 中编写这种查询？

In JPQL, we can only write simple queries like say:

在 JPQL 中，我们只能编写简单的查询，例如：

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

Where objUser is supplied using:

objUser 是使用以下方式提供的：

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?

因此，一旦我们获得了用户加入的所有博客（这里 blogId 代表一个博客对象），我们就可以循环并做所有花哨的事情。但是我不想陷入这种循环业务并在我的 Java 代码中编写所有这些东西。相反，我想把它留给数据库引擎来做。那么，如何将上述纯 SQL 写入 JPQL 中呢？JPQL 查询将返回什么类型的对象？因为我只从所有表中选择了几个字段。我应该将结果类型转换到哪个类？

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:

更新：根据 pascal 的回答，我尝试为上述 SQL 查询编写 JPQL 查询。我面临一个小问题。此查询有效，但不完整：

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?

上面的查询不起作用。我怎么解决这个问题？

采纳答案by TCM

Ok, this is the final answer. It took one hour to frame this one line. I got many strange errors during this hour, 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")

回答by Pascal Thivent

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

在 JPQL 中，我们只能编写简单的查询 (...)

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:

事实并非如此，JPQL 确实支持[ LEFT [OUTER] | INNER ] JOIN. 对于Inner Joins，参考规范的4.4.5.1 Inner Joins（Relationship Joins）部分：

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

4.4.5.1 内部联接（Relationship Joins）

内连接操作的语法是

[ INNER ] JOIN join_association_path_expression [AS] identification_variable

例如，下面的查询连接客户和订单之间的关系。这种类型的联接通常等同于对数据库中的外键关系进行联接。

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

可以选择使用关键字 INNER：

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

You just need to think association between entities.

您只需要考虑实体之间的关联。