如何在NamedQuery中编写NamedQuery hasrsine公式？ [英] How to write NamedQuery haversine formula in NamedQuery?

问题描述

我想在以下查询下运行hasrsine公式为NamedQuery，但我不知道如何纠正它。

I want to run below query of haversine formula as NamedQuery but I don't know how to correct it.

set @orig_lat = 37.334542;
set @orig_lon = -121.890821;
set @dist = 10;

select  *,
        3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat - abs(mlatitude)) * pi() / 180 / 2), 2) 
          + COS(@orig_lat * pi() / 180) * COS(abs(mlatitude) * pi() / 180) * POWER(SIN((@orig_lon - mlogitude) * pi() / 180 / 2), 2))) as distance
from user_gps_location
having distance < @dist
ORDER BY distance

我将此查询运行到mysql中，它对我来说运行正常但当我在查询下面写为NamedQuery时，它给了我错误：

I run this query into mysql and it works fine for me but when I write below query as NamedQuery it gives me error:

UserGpsLocation users = (UserGpsLocation)em.createQuery("select (3956*2*ASIN(SQRT(POWER(SIN((?1-abs(u.mlatitude))*pi()/180/2),2)+COS(?1*pi()/180) * COS(abs(u.mlatitude)* pi()/180) *POWER(SIN((?2 -u.mlogitude)* pi()/180/2),2)))) as distance from UserGpsLocation u having distance < :dist ORDER BY distance")
      .setParameter(1, mlatitude)
      .setParameter(2, mlogitude)
      .setParameter("dist", 10)
      .getResultList();

例外：

javax.servlet.ServletException: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing the query [select (3956*2*ASIN(SQRT(POWER(SIN((?1-abs(u.mlatitude))*pi()/180/2),2)+COS(?1*pi()/180) * COS(abs(u.mlatitude)* pi()/180) *POWER(SIN((?2 -u.mlogitude)* pi()/180/2),2)))) as distance from UserGpsLocation u having distance < :dist ORDER BY distance], line 1, column 19: unexpected token [(].
Internal Exception: NoViableAltException(83@[()* loopback of 383:9: (d= DOT right= attribute )*])

任何人都可以帮助我并告诉我它有什么问题吗？

Can anyone help me and tell me what's wrong with it?

推荐答案

最后能够找到解决方案。而不是createQuery，我使用createNativeQuery来解决我的问题。

Finally able to find out solution. Instead of createQuery, I used createNativeQuery and that solve my problem.

这篇关于如何在NamedQuery中编写NamedQuery hasrsine公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！