Spring Boot Hibernate - Cannot execute custom query

Hibernate ORM
1

Hello there, I am trying to execute a custom Spring JPA query but it keeps on giving me the below error.

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: HAVING near line 1, column 247 [SELECT id, (6371 * acos ( cos ( radians(48.1369945) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(11.5689834) ) + sin ( radians(48.1369945) ) * sin( radians( lat ) ))) AS distance FROM com.dushero.app.persistance.model.LocationEntity HAVING distance < 5 ORDER BY distance LIMIT 0 , 20].

When I execute the query on my mysql client it works fine:

SELECT id, ( 6371 * acos ( cos ( radians(48.1369945) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(11.5689834) ) + sin ( radians(48.1369945) ) * sin( radians( lat ) ) ) ) AS distance FROM location HAVING distance < 5 ORDER BY distance LIMIT 0 , 20;

The error occurs only when I execute my custom query in my JpaRepository.

`@Repository
interface LocationRepository : JpaRepository<LocationEntity, Long> {

@Query("SELECT id, (6371 * acos ( cos ( radians(48.1369945) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(11.5689834) ) + sin ( radians(48.1369945) ) * sin( radians( lat ) ))) AS distance FROM location HAVING distance < 5 ORDER BY distance LIMIT 0 , 20")
fun filterByDistance(
    @Param("lat") lat: String,
    @Param("lng") lng: String
): List<LocationEntity>

}`

Thanks in advance,
Elvis

2

Hello,

Not an expert(scratching the surface) but maybe this answer will help you

Thank you,
Cosmin