如何从sql查询中获取jpql查询? [英] How to get jpql query from sql query?
问题描述
我可以像这样在JPQL中使sql简单查询,并且效果很好:
i can make sql simple query in JPQL like this and its work well :
Query query = em.createQuery("SELECT p2 FROM Zp01 p2 where p2.gestionnaire IN (SELECT d.libelle FROM Affaire d)") ;
liszp01general= (List<Zp01>) query.getResultList();
但是我无法将此查询转换为已经在sql中运行的JPQL:
but i cant translate this query to JPQL thats already working in sql :
SELECT p2.* from zp01 p2 join (SELECT TYPEC,count(TYPEC) as cnt_typec FROM planning_cuisson group by TYPEC HAVING COUNT(TYPEC) > 0) p1 where p2.type_cuisson=p1.typec order by cnt_typec asc ;
我尝试了这个但没用:
Query query = em.createQuery("SELECT p2 FROM Zp01 p2 join ( select G.TYPEC,count(G.TYPEC) as cnt_typec from PlanningCuisson G group by G.TYPEC HAVING COUNT(G.TYPEC) > 0) p1 Where p2.typeCuisson=p1.typec and p2.ordre NOT IN (SELECT k.numof FROM OfSemiplanifie k) AND p2.gestionnaire IN (SELECT d.libelle FROM Affaire d) order by cnt_typec asc");
liszp01general= (List<Zp01>) query.getResultList();
推荐答案
这是一个较旧的文章,但是如果它对其他人有帮助,我只想补充一点,当前版本的JPQL不支持子查询.在FROM
子句中.在JPQL参考中:
This is an older post, but in case it helps anyone else out, I'll just add that the current version of JPQL doesn't support sub-queries in the FROM
clause. From the JPQL Reference:
在此版本中,子查询仅限于WHERE和HAVING子句.在规范的更高版本中,将考虑对FROM子句中子查询的支持.
Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of the specification.
当我必须在项目上执行类似操作时,必须将聚合子查询下移到where子句中,将Comparator添加到实体,然后在JPQL调用之后执行Collections.sort.因此查询将类似于以下内容:
When I had to do something similar on a project, I had to move the aggregate sub-query down into the where clause, add a Comparator to the entity, and then do a Collections.sort after the JPQL call. So the query would become something along the lines of:
...
WHERE p2.typeCuisson IN (SELECT G.TYPEC
FROM PlanningCuisson G
GROUP BY G.TYPEC
HAVING COUNT(G.TYPEC) > 0)...
在query.getResultList()
这篇关于如何从sql查询中获取jpql查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!