Java 在 WHERE 子句中使用 CASE WHEN 的 JPA 查询。怎么做？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9673341/
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
JPA query with CASE WHEN in the WHERE clause. How to do?
提问by edze
How can run a query like below with JPA. (It works with plain SQL)
如何使用 JPA 运行如下查询。（它适用于纯 SQL）
SELECT t
FROM table t
WHERE
(
CASE WHEN (( ... subquery ... ) IS NULL)
THEN (t.category IS NULL)
ELSE (t.viewId = :viewId)
END
)
I get a MismatchedTokenExceptionat IS
我得到了MismatchedTokenException在IS
THEN (t.category IS NULL)
THEN (t.category IS NULL)
Is it possible? Or need I to rewrite this query?
是否可以？或者我需要重写这个查询？
采纳答案by Amir Pashazadeh
You can convert your where clause to something like:
您可以将 where 子句转换为以下内容：
where
((... my first condition ...) and (something is NULL))
or
(not (... first condition ...) and (something2 = otherthing))
回答by Tom Anderson
I think you can't do that. Have a read of the JPQL grammar(here courtesy of OpenJPA). Here are the terms defining the case expression, specifically the general case expression you're using:
我认为你不能那样做。阅读JPQL 语法（此处由 OpenJPA 提供）。以下是定义 case 表达式的术语，特别是您使用的一般 case 表达式：
- case_expression ::= general_case_expression | simple_case_expression | coalesce_expression | nullif_expression
- general_case_expression ::= CASE when_clause {when_clause}* ELSE scalar_expression END
- when_clause ::= WHEN conditional_expression THEN scalar_expression
- case_expression ::= general_case_expression | simple_case_expression | 合并表达式 | nullif_表达式
- general_case_expression ::= CASE when_clause {when_clause}* ELSE scalar_expression END
- when_clause ::= WHEN conditional_expression THEN scalar_expression
The expression on the right-hand side of the THENhas to be a scalar_expression. Which is to say:
右侧的表达式THEN必须是 a scalar_expression。也就是说：
- scalar_expression ::= arithmetic_expression | string_primary | enum_primary | datetime_primary | boolean_primary | case_expression | entity_type_expression
- 标量表达式 ::= 算术表达式 | string_primary | enum_primary | datetime_primary | boolean_primary | case_expression | entity_type_expression
I haven't rooted through the definition of all those terms, but i think they don't include things like (t.category IS NULL), i'm afraid.
我还没有深入了解所有这些术语的定义，但我认为它们不包括诸如(t.category IS NULL)，恐怕。
回答by Futó Tamás
You can download the JPA 2.0 specification final release from here: http://download.oracle.com/otndocs/jcp/persistence-2.0-fr-eval-oth-JSpec/
您可以从这里下载 JPA 2.0 规范最终版本：http: //download.oracle.com/otndocs/jcp/persistence-2.0-fr-eval-oth-JSpec/
You should check Example 6in section 6.5.7.
It shows how to use case-whenin Criteria API.
I'm not sure, but I think it's possible to use logical expressions (t.category IS NULL) in THENblock if you use it in a WHEREclause.
您应该检查例6中节6.5.7。它展示了如何case-when在 Criteria API 中使用。我不确定，但我认为如果您在子句中使用逻辑表达式 ( t.category IS NULL)，THEN则可以在块中使用它WHERE。
