I have a database where I made a query successfully in SQL, but the matter is that I need to write this same query in JPQL for a method of one REST service.
This is the query that worked:
SELECT * FROM user u WHERE SQRT(POW(69.1 * (u.latitude - 38.0),2) + POW(69.1 * (-2.8 - u.longitude) * COS(u.latitude / 57.3), 2)) < 100
I need to change the values written there as "38.0" and "-2.8" and "100" to input parameters and make it in JPQL.
This is what I tried
@GET
@Path("nearusers={lat}&{lon}&{dist}")
@Produces({MediaType.APPLICATION_JSON})
public List<Usuario> findNearUsers(@PathParam("lat") BigDecimal lat,@PathParam("lon") BigDecimal lon, @PathParam("dist") BigDecimal dist){
TypedQuery query = getEntityManager().createQuery("SELECT u FROM User u WHERE SQRT(POW(69.1 * (u.latitude - :lat),2) + POW(69.1 * (:lon - u.longitude) * COS(u.latitude / 57.3), 2)) < :dist",Usuario.class);
List<User> users = query
.setParameter("lat", lat)
.setParameter("lon", lon)
.setParameter("dist", dist)
.getResultList();
return users;
}
And when I launch this service method what I'm getting by my server is an error that says:
Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [SELECT u FROM Usuario u WHERE SQRT(POW(69.1 * (u.latitud - :lat),2) + POW(69.1 * (:lon - u.longitud) * COS(u.latitud / 57.3), 2)) < :dist].
[35, 129] The encapsulated expression is not a valid expression
The IDE (Netbeans) is showing some warnings in the line I forming the query: IDE warnings
I tried to change the expression several times, adding brackets, casting parameters to double, but nothing worked.
POW
and COS
are not supported by JPQL. In addition to basic arithmetic operations, JPQL only supports a limited set of arithmetic operations: ABS
, SQRT
, MOD
, SIZE
, INDEX
(see JPA 2.1 Specification, Sec. 4.6.17.2.2).
Therefore, you need to use a JPA native query formulated in the SQL dialect of your database:
Query query = getEntityManager().createNativeQuery(
"SELECT * FROM user u WHERE SQRT(POW(69.1 * (u.latitude - ?1),2) + POW(69.1 * (?2 - u.longitude) * COS(u.latitude / 57.3), 2)) < ?3",
User.class);
List<User> users = query
.setParameter(1, lat)
.setParameter(2, lon)
.setParameter(3, dist)
.getResultList();
Notice that a native query is still capable of returning managed entities.