Java SpringData：查询注释中是否可以有子查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9743011/
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
SpringData : is it possible to have subqueries in the Query annotation?
提问by jpboudreault
I would like to know if it is possible to have subquery in a @Query annotation (org.springframework.data.jpa.repository.Query;)
我想知道是否可以在 @Query 注释中使用子查询（org.springframework.data.jpa.repository.Query;）
I am getting a QuerySyntaxException on the first subquery parentesis.
我在第一个子查询 parentesis 上收到 QuerySyntaxException。
Here is my query
这是我的查询
@Query(value="select c1 from ComplaintModel c1, "
+ "(select c2.id, min(cb.termDate) minDate from ComplaintModel c2 "
+ "join c2.complaintBullets cb join cb.status s where s.code = ?1 "
+ "group by c2.id) tmp where c1.id = tmp.id order by tmp.minDate")
Thanks!
谢谢！
采纳答案by Mikko Maunu
No, it is not possible to have subquery in the select clause in JPQL query.
不，在 JPQL 查询的 select 子句中不可能有子查询。
JPQL supports subqueries in WHERE and HAVING clauses. It can be (at least) part of ANY, SOME, ALL, IN, EXIST expressions, and of course it can be used normal conditional expressions:
JPQL 支持 WHERE 和 HAVING 子句中的子查询。它可以（至少）是 ANY、SOME、ALL、IN、EXIST 表达式的一部分，当然也可以用作普通条件表达式：
SELECT a
FROM A a
WHERE a.val = (SELECT b.someval
FROM B b
WHERE b.someotherval=3)
回答by Oliver Drotbohm
The content of the @Queryannotation is more or less passed as is to the persistence provider by calling EntityManager.createQuery(…). So whatever is allowed in there can be used in @Query. AFAIK, JPQL (by the time of JPA 2.0) only supports subqueries for EXISTSand INclauses.
@Query注释的内容或多或少通过调用传递给持久性提供者EntityManager.createQuery(…)。因此，那里允许的任何内容都可以在@Query. AFAIK，JPQL（到 JPA 2.0 时）仅支持EXISTS和IN子句的子查询。
回答by Narendra N
I did get expected results in Spring-data jpa with
我确实在 Spring-data jpa 中得到了预期的结果
public final static String FIND_BY_ID_STATE = "SELECT a FROM Table1 a RIGHT JOIN a.table2Obj b " +
"WHERE b.column = :id" +
"AND a.id NOT IN (SELECT c.columnFromA from a.table3Obj c where state = :state)";
@Query(FIND_BY_ID_STATE)
public List<Alert> findXXXXXXXX(@Param("id") Long id, @Param("state") Long state);
where
在哪里
Table2Obj & Table3Obj are the mapping of the relationship between entities Table1 and Table2, Table3 respectively.
Table2Obj & Table3Obj分别是实体Table1和Table2、Table3之间关系的映射。
defined Like below.
定义如下。
@OneToMany(mappedBy = "xxx", fetch = FetchType.LAZY)
private Set<Table2> table2Obj = new HashSet<>();
