JPA Named Query showing error

Question

I am trying to execute JPA named query on my entity, but it is showing syntax error. Can anyone tell me what is the problem with my named query?

@Repository
public interface CollegeRepository extends CrudRepository<CollegeEntity, Integer> {


    @Query("SELECT c FROM(SELECT *,(((acos(sin((:latitude * pi()/180)) * sin((latitude*pi()/180))+cos((:latitude * pi()/180)) * cos((latitude*pi()/180)) * cos(((:longitude - longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM college) t WHERE distance <= 30")
    List<CollegeEntity> getCollegeByLocation(@Param("latitude") Double latitude, @Param("longitude") Double longitude, @Param("distance") Integer distance);    
}

This is error after executing above query.

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 14 [SELECT c FROM(SELECT *,(((acos(sin((:latitude * pi()/180)) * sin((latitude*pi()/180))+cos((:latitude * pi()/180)) * cos((latitude*pi()/180)) * cos(((:longitude - longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM college) t WHERE distance <= :distance]

When I execute below native query in MySQL terminal then working good.

mysql> SELECT * FROM(SELECT *,(((acos(sin((0.0 * pi()/180)) * sin((latitude*pi()/180))+cos((0.0 * pi()/180)) * cos((latitude*pi()/180)) * cos(((0.0 - longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM college) t WHERE distance <= 30;
Empty set (0.08 sec)

User has 2 queries posted and doesn't say which one they refer to, nor what the "syntax error" is. Also there is no JPA "named query" there. You have 2 SPRING DATA JPA queries — user8558216
– user8558216, Commented Jan 14, 2018 at 7:33
Yes, it is not valid JPQL, you can see error, I have edited answer with error too. — Sagar Yadav
– Sagar Yadav, Commented Jan 14, 2018 at 7:44

Youcef LAIDANI · Accepted Answer · 2018-01-14 07:48:46Z

1

It seems that your query is not a correct JPQL Syntax, because there are no acos, pi, sin, cod functions in JPA, your query look like a native query, to solve your problem you have two options :

First option : Convert your query to a correct JPQL syntax, you can follow the documentation
Second option : use nativeQuery = true attribute @Query(nativeQuery = true, value="SELECT * FROM(..."), note the * there are no alias c in your query.

You can use :

@Query(value = "SELECT * FROM(SELECT *,"
        + "(((acos(sin((0.0 * pi()/180)) * sin((latitude*pi()/180)) + "
        + "cos((0.0 * pi()/180)) * cos((latitude*pi()/180)) * "
        + "cos(((0.0 - longitude)*pi()/180))))*180/pi()) * "
        + "60*1.1515*1.609344) as distance FROM college) t WHERE distance <= 30;", 
        nativeQuery = true)

In your case, I strongly suggest the second option.

edited Jan 14, 2018 at 7:48

answered Jan 14, 2018 at 7:26

Youcef LAIDANI

60.2k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sagar Yadav Over a year ago

I used the second option, But there is error showing "Invalid MemberValuePair". Any suggestion?

Youcef LAIDANI Over a year ago

@SagarYadav can you edit your question and add the full error, beside focus on one error per question please

Sagar Yadav Over a year ago

I am accepting your answer. But, before that can you tell me what can be JPQL alternative? If the JPQL option is too difficult, can you describe why you strongly suggest the second option?

Youcef LAIDANI Over a year ago

@SagarYadav i already mention that, because you are using some function not exist in JPQL, unless you go with other options like eclipse.org/eclipselink/documentation/2.4/jpa/extensions/…

Collectives™ on Stack Overflow

JPA Named Query showing error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related