JPA @NamedQuery相关子查询
问题描述:
我试图创建一个@NamedQuery
将包含以下相关子查询:JPA @NamedQuery相关子查询
@NamedQuery(name = "Drcomments.findByOrderDesc", query = " SELECT a FROM (SELECT ROWNUMBER() OVER(ORDER BY d.drcommentsPK.commentSecondaryCode DESC) AS EL_ROWNM , d.drcommentsPK.commentSecondaryCode , d.commentSecondaryCodeDescription FROM Drcomments d WHERE d.drcommentsPK.commentPrimaryCode = 1) a WHERE a.EL_ROWNM < 4 and a.EL_ROWNM > 0 ")})
的问题是,我得到这个错误:
"Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [Drcomments.findByOrderDesc: SELECT a FROM (SELECT ROWNUMBER() OVER(ORDER BY d.drcommentsPK.commentSecondaryCode DESC) AS EL_ROWNM , d.drcommentsPK.commentSecondaryCode , d.commentSecondaryCodeDescription FROM Drcomments d WHERE d.drcommentsPK.commentPrimaryCode = 1) a WHERE a.EL_ROWNM < 4 and a.EL_ROWNM > 0 ], line 1, column 15: unexpected token [(].
Internal Exception: org.eclipse.persistence.internal.jpa.parsing.jpql.InvalidIdentifierException"
不@NamedQuery支持相关的subquerys?如果是这样, 如何创建一个将执行此类查询的命名查询?
感谢提前。
答
@NamedQuery不支持SQL,它只支持JPQL。您的查询显然不是JQPL,因为它使用数据库特定的语法,如ROWNUMBER()。此外,JPQL子查询仅在WHERE和HAVING子句中受支持。
改为使用@NamedNativeQuery。