从SQL到JPQL的加入请求 [英] SQL to JPQL join request
本文介绍了从SQL到JPQL的加入请求的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
希望这里有人可以帮助我.
Hopefully someone here can help me.
我已经找到了要在sqlDev上使用的请求:
I have found the request I want to use on sqlDev :
SELECT t1.*
FROM table1 t1 INNER JOIN
(
SELECT ev.ID_AGENCE, MAX(ev.DATE_CREATION) DATE_CREATION
FROM table1 ev
WHERE ev.ID_AGENCE IN (326,324)
GROUP BY ev.ID_AGENCE
) t2 ON t1.ID_AGENCE = t2.ID_AGENCE
AND t1.DATE_CREATION = t2.DATE_CREATION
order by t1.id_agence;
仅在列表中保留最接近的日期,每个ID仅保留一个日期(在我的示例中为324和326):
to keep only the closest date in a list, and only one per id (324 and 326 here in my exemple) :
324 22/10/18
324 21/10/18
324 20/10/18
326 10/08/18
326 09/08/18
326 07/08/18
326 06/08/18
326 05/08/18
326 04/08/18
326 03/08/18
326 02/08/18
326 01/08/18
我试图翻译成JPA(JPQL):
I tried to translate to JPA (JPQL) :
final String requete = "SELECT e FROM ClasseJava JOIN " +
"( " +
"SELECT f.id, MAX(f.dateCreation) dateCreation " +
"FROM ClasseJava f " +
"WHERE f.idAgence IN (326,324) " +
"GROUP BY f.idAgence " +
") " +
"t ON e.idAgence = t.idAgence " +
"AND e.dateCreation = t.dateCreation " +
"GROUP BY idAgence ";
final TypedQuery<ClasseJava> query = entityManager.createQuery(requete, ClasseJava.class);
query.setParameter("listIdAgence", listIdAgence);
return query.getResultList();
然后出现以下错误:联接关联路径不是有效的表达式."
有人对如何解决此问题有想法吗?
Does anyone have an idea on how to fix the issue ?
推荐答案
我通过删除Join子句使它起作用.
I made it work by removing the Join clause.
像这样:
final String requete = "SELECT e FROM classeJava e, " +
"( " +
"SELECT f.idAgence, MAX(f.dateCreation) dateCreation " +
"FROM classeJava f " +
"WHERE f.idAgence IN :listIdAgence " +
"GROUP BY f.idAgence " +
") " +
"t WHERE e.idAgence = t.idAgence " +
"AND e.dateCreation = t.dateCreation " +
"ORDER BY e.idAgence ";
希望这对某人有帮助.
这篇关于从SQL到JPQL的加入请求的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文