SpringData : is it possible to have subqueries in

问题:

I would like to know if it is possible to have subquery in a @Query annotation (org.springframework.data.jpa.repository.Query;)

I am getting a QuerySyntaxException on the first subquery parentesis.

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!

回答1:

No, it is not possible to have subquery in the select clause in JPQL query.

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:

SELECT a
FROM A a
WHERE a.val = (SELECT b.someval 
               FROM B b 
               WHERE b.someotherval=3)

回答2:

I did get expected results in Spring-data jpa with

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.

defined Like below.

@OneToMany(mappedBy = "xxx", fetch = FetchType.LAZY)
private Set<Table2> table2Obj = new HashSet<>();

回答3:

The content of the @Query annotation 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 EXISTS and IN clauses.