我如何做一个JPQL SubQuery？ [英] How do I do a JPQL SubQuery?

问题描述

可以在JPQL中执行此sql查询的等效操作吗？

It is possible to do the equivalent of this sql query in JPQL?

SELECT * 
 FROM COUNTRIES c WHERE COUNTRY_ID IN (
  SELECT DISTINCT COUNTRY_ID 
   FROM PORTS p 
   WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
) 

推荐答案

您需要使用 IN 和子查询因为两者都在JPQL中工作（根据它们的语法参考）一起工作）。您还可以查看表达成员。

You need to test it with IN and subquery since both do work in JPQL (according to syntax reference they do work together). You may also look at MEMBER OF expressions.

但我认为有更好的方法。此类查询称为相关子查询，并且可以使用EXISTS重写它们：

But there is a better approach in my opinion. Such queries are called correlated sub-queries and one can always re-write them using EXISTS:

SELECT * FROM COUNTRIES c WHERE 
EXISTS (
        SELECT 'found' FROM PORTS p 
        WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
) 

JPQL支持 EXISTS with subqueries 。

这篇关于我如何做一个JPQL SubQuery？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！