JpaRepository native query not detecting parameters

user_1379292 0
Viewed 2426

I am trying to write a native query in my SpringBoot application to get the closest location to any given coordinate. Following is the JpaRepository for the same:

@Repository
public interface OfferRepository extends JpaRepository<Offer, Long> {
    @Query(value = "SELECT *, MIN(6371000 * acos( cos( radians(:latitude) ) * cos( radians( dest.latitude ) ) * cos( radians( dest.longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( dest.latitude ) ))) as  distance FROM offer dest GROUP BY dest.id having MIN(distance)",
            nativeQuery = true)
    Tuple getClosestOffer(double latitude, double longitude);
}

The problem I am facing it that the passed latitude and longitude are not getting set in the query string. Instead they are replaced with question marks as seen below:

SELECT *, MIN(6371000 * acos( cos( radians(?) ) * cos( radians( dest.latitude ) ) * cos( radians( dest.longitude ) - radians(?) ) + sin( radians(?) ) * sin( radians( dest.latitude ) ))) as  distance FROM offer dest GROUP BY dest.id having MIN(distance)

I even tried with numbered parameters, but with same result. What am I doing wrong here? How to solve this?

spring-data-jpa spring-boot jpa hibernate
1 Answers

You can use Named Parameters using the @Param annotation

public interface OfferRepository extends JpaRepository<Offer, Long> {
    @Query(value = "SELECT *, MIN(6371000 * acos( cos( radians(:latitude) ) * cos( radians( dest.latitude ) ) * cos( radians( dest.longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( dest.latitude ) ))) as  distance FROM offer dest GROUP BY dest.id having MIN(distance)",
            nativeQuery = true)
    Tuple getClosestOffer(@Param("latitude") double latitude, @Param("longitude") double longitude);
}

or use Indexed Parameters: ?1 ?2 in you jpql

public interface OfferRepository extends JpaRepository<Offer, Long> {
    @Query(value = "SELECT *, MIN(6371000 * acos( cos( radians(?1) ) * cos( radians( dest.latitude ) ) * cos( radians( dest.longitude ) - radians(?2) ) + sin( radians(?1) ) * sin( radians( dest.latitude ) ))) as  distance FROM offer dest GROUP BY dest.id having MIN(distance)",
            nativeQuery = true)
    Tuple getClosestOffer(double latitude, double longitude);
}

if you want to show the resolved parameters in your logging just set the logging level of org.hibernate.type to trace

application.properties :

spring.jpa.show-sql=true
logging.level.org.hibernate.type=trace
user_96817550
Related