java - JPA 查询，SQRT 函数在 ORDER BY 子句中不起作用

Question

我有以下 JPA 查询

SELECT p FROM Entity p WHERE p.type = (:type1) AND SQRT((( (:constlong)*(p.longitude - (:long)))*((:constlong)*(p.longitude - (:long)))) + (((:constlat) * (p.latitude - (:lat)))*((:constlat)*(p.latitude - (:lat))))) <= :rad ORDER BY SQRT((( (:constlong)*(p.longitude - (:long)))*((:constlong)*(p.longitude - (:long)))) + (((:constlat) * (p.latitude - (:lat)))*((:constlat)*(p.latitude - (:lat))))) ASC

此查询在 eclipselink 2.6.9 上运行良好。更新到版本 2.7.9 后，它停止工作。当我删除该ORDER BY子句时，它再次起作用。SQRT我需要按在表达式中计算的距离对我的实体进行排序。

有人可以帮忙吗？

编辑

这是错误：

18:08:58,197 INFO  [stdout] (default task-1) Query error java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
18:08:58,197 INFO  [stdout] (default task-1) Exception Description: Syntax error parsing [SELECT p FROM Entity p WHERE p.type = (:type1) AND SQRT((( (:constlong)*(p.longitude - (:long)))*((:constlong)*(p.longitude - (:long)))) + (((:constlat) * (p.latitude - (:lat)))*((:constlat)*(p.latitude - (:lat))))) <= :rad ORDER BY SQRT((( (:constlong)*(p.longitude - (:long)))*((:constlong)*(p.longitude - (:long)))) + (((:constlat) * (p.latitude - (:lat)))*((:constlat)*(p.latitude - (:lat))))) ASC]. 
18:08:58,197 INFO  [stdout] (default task-1) [553, 553] The right parenthesis is missing from the SQRT expression.
18:08:58,197 INFO  [stdout] (default task-1) [635, 640] The query contains a malformed ending.

这是eclipselink 2.6.4版中生成的sql

SELECT entityid, latitude, longitude FROM public.entity WHERE (((type = ?) OR (type = ?)) AND (SQRT((((? * (longitude - ?)) * (? * (longitude - ?))) + ((? * (latitude - ?)) * (? * (latitude - ?))))) <= ?)) ORDER BY SQRT((((? * (longitude - ?)) * (? * (longitude - ?))) + ((? * (latitude - ?)) * (? * (latitude - ?))))) ASC

在 2.7.7 版本中。没有order by

SELECT entityid, latitude, longitude FROM public.entity WHERE (((type = ?) OR (type = ?)) AND (SQRT((((? * (longitude - ?)) * (? * (longitude - ?))) + ((? * (latitude - ?)) * (? * (latitude - ?))))) <= ?))

2. 编辑

我做了一些进一步的挖掘。上面的查询也不适用于 eclipselink 版本 2.6.9。但它肯定适用于 2.6.4 版本。这两个版本之间发生了一些变化。

标签： javaeclipselinkjpqljpa-2.0

Answer 1

我的解决方案是切换到 nativeQuery。查询如下所示：

getEntityManager().createNativeQuery("SELECT * FROM entity p WHERE (p.type = ?) AND SQRT( (( 71.5*(p.longitude - ?))*( 71.5*(p.longitude - ?))) + (( 111.3 * (p.latitude - ?))*( 111.3*(p.latitude - ?)))) <= 15.0 ORDER BY SQRT( (( 71.5*(p.longitude - ?))*( 71.5*(p.longitude - ?))) + (( 111.3 * (p.latitude - ?))*( 111.3*(p.latitude - ?)))) ASC", Entity.class);