Java 使用选择中的子选择将 SQL 转换为 HQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2746756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting SQL with subselect in select to HQL
提问by RyanLynch
I have the following SQL that I am having problems converting to HQL. A NPE is getting thrown -- which I think has something to do with the SUM function. Also, I'd like to sort on the subselect alias -- is this possible?
我有以下 SQL，但在转换为 HQL 时遇到问题。正在抛出 NPE - 我认为这与 SUM 函数有关。另外，我想对子选择别名进行排序——这可能吗？
SQL (subselect):
SQL（子选择）：
SELECT q.title, q.author_id,
(SELECT IFNULL(SUM(IF(vote_up=true,1,-1)), 0)
FROM vote WHERE question_id = q.id) AS votecount
FROM question q ORDER BY votecount DESC
HQL (not working)
HQL（不工作）
SELECT q,
(SELECT COALESCE(SUM(IF(v.voteUp=true,1,-1)), 0)
FROM Vote v WHERE v.question = q) AS votecount
FROM Question AS q
LEFT JOIN q.author u
LEFT JOIN u.blockedUsers ub
WHERE q.dateCreated BETWEEN :week AND :now
AND u.id NOT IN (
SELECT ub.blocked FROM UserBlock AS ub WHERE ub.blocker = :loggedInUser
)
AND (u.blockedUsers IS EMPTY OR ub.blocked != :loggedInUser)
ORDER BY votecount DESC
采纳答案by RyanLynch
Here is the working HQL if anyone is interested:
如果有人感兴趣，这里是工作 HQL：
SELECT q,
(SELECT COALESCE(SUM(CASE v.voteUp WHEN true THEN 1 ELSE -1 END), 0)
FROM Vote v WHERE v.question = q) AS votecount
FROM Question AS q
LEFT JOIN q.author u
LEFT JOIN u.blockedUsers ub
WHERE q.dateCreated BETWEEN :week AND :now
AND u.id NOT IN (
SELECT ub.blocked FROM UserBlock AS ub WHERE ub.blocker =:loggedInUser
)
AND (u.blockedUsers IS EMPTY OR ub.blocked !=:loggedInUser)
ORDER BY col_1_0_ DESC
Notice the ORDER BY col_1_0_
注意 ORDER BY col_1_0_
There is an open issue with Hibernate -- it does not correctly parse aliases and since the aliases are renamed in the query, an error will be thrown. So, col_1_0_ is basically a workaround --it's the name Hibernate generates. See issue: http://opensource.atlassian.com/projects/hibernate/browse/HHH-892
Hibernate 有一个未解决的问题——它没有正确解析别名，并且由于别名在查询中被重命名，将引发错误。所以， col_1_0_ 基本上是一种解决方法——它是 Hibernate 生成的名称。请参阅问题：http: //opensource.atlassian.com/projects/hibernate/browse/HHH-892
