如何编写这个 JPQL 查询?
假设我有 5 个表,
tblBlogs tblBlogPosts tblBlogPostComment tblUser tblBlogMember
BlogId BlogPostsId BlogPostCommentId UserId BlogMemberId
BlogTitle BlogId CommentText FirstName UserId
PostTitle BlogPostsId BlogId
BlogMemberId
现在我只想检索 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;
正如您所看到的,所有内容都是内连接,因此只会检索用户对某个博客的某些帖子发表评论的行。因此,假设他/她加入了 3 个博客,其 ID 分别为 1,2,3(用户加入的博客位于 tblBlogMembers 中),但用户仅在博客 2 中发表了评论(例如 BlogPostId = 1)。因此该行将被检索,而 1,3 则不会,因为它是内连接。如何在 JPQL 中编写此类查询?
在 JPQL 中,我们只能编写简单的查询,例如:
Select bm.blogId from tblBlogMember Where bm.UserId = objUser;
其中 objUser 是使用以下方式提供的:
em.find(User.class,1);
因此,一旦我们获取了用户已加入的所有博客(此处 blogId 代表博客对象),我们就可以循环并执行所有奇特的操作。但我不想陷入这种循环业务并在我的 Java 代码中编写所有这些内容。相反,我想将其留给数据库引擎来做。那么,如何将上面的纯 SQL 写入 JPQL 中呢? JPQL 查询将返回什么类型的对象?因为我只从所有表中选择几个字段。我应该将结果类型转换到哪个类?
我认为我正确地发布了我的要求,如果我不清楚,请告诉我。
更新:根据 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
我想将其修改为:
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
上面的查询不起作用。我该如何解决这个问题?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
事实并非如此,JPQL 确实支持
[ LEFT [OUTER] |内部]加入
。对于Inner Joins,请参考规范的4.4.5.1 Inner Joins(Relationship Joins)部分:您只需要考虑实体之间的关联。
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:You just need to think association between entities.
好的,这就是最终的答案。构筑这一行花了一个小时。在这一小时内我遇到了很多奇怪的错误,但现在我的概念已经足够清晰了:
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: