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?
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
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加