使用Spring Boot JPA实现谷歌地图门店定位时Query返回异常求助

Let's walk through the issues with your current query and fix them one by one:

1. Return Type Mismatch #

Your query returns a combination of Location entities and calculated distance values (as an object array [Location, Double]), but your method declares it returns List<Location>. This will immediately throw a type conversion error.

Fix Options: #

• If you need both Location and distance: Create a simple DTO to hold both values:

  public class LocationWithDistance {
      private Location location;
      private Double distance;
  
      // Constructor matching the query's SELECT order
      public LocationWithDistance(Location location, Double distance) {
          this.location = location;
          this.distance = distance;
      }
  
      // Add getters/setters as needed
  }
  

• If you only need Location entities: Skip selecting the distance alias and use the calculation directly in ordering.

2. Misuse of HAVING Clause #

HAVING is designed to filter results after grouping with GROUP BY. Since you're not grouping data, you should use WHERE instead. However, SQL doesn't allow using column aliases (like distance) in WHERE, so you'll need to repeat the Haversine calculation in the filter.

3. SQL Keyword Conflict #

long is a reserved SQL keyword. If your Location entity has a field named long (for longitude), this will cause parsing errors. Rename the field to longitude (cleanest fix) or escape it (e.g., p."long" for PostgreSQL, p.long`` for MySQL).

Corrected Query (With DTO) #

@Query("SELECT new com.your.package.LocationWithDistance(p, " +
       "(6371 * acos(cos(radians(:latitude)) * cos(radians(p.latitude)) * " +
       "cos(radians(p.longitude) - radians(:longitude)) + " +
       "sin(radians(:latitude)) * sin(radians(p.latitude)))) AS distance) " +
       "FROM Location p " +
       "WHERE (6371 * acos(cos(radians(:latitude)) * cos(radians(p.latitude)) * " +
       "cos(radians(p.longitude) - radians(:longitude)) + " +
       "sin(radians(:latitude)) * sin(radians(p.latitude)))) < :radius " +
       "ORDER BY distance LIMIT 25")
List<LocationWithDistance> getNearbyLocations(
    @Param("latitude") Double latitude,
    @Param("longitude") Double longitude,
    @Param("radius") Double radius
);

Corrected Query (Only Location Entities) #

@Query("SELECT p FROM Location p " +
       "WHERE (6371 * acos(cos(radians(:latitude)) * cos(radians(p.latitude)) * " +
       "cos(radians(p.longitude) - radians(:longitude)) + " +
       "sin(radians(:latitude)) * sin(radians(p.latitude)))) < :radius " +
       "ORDER BY (6371 * acos(cos(radians(:latitude)) * cos(radians(p.latitude)) * " +
       "cos(radians(p.longitude) - radians(:longitude)) + " +
       "sin(radians(:latitude)) * sin(radians(p.latitude)))) LIMIT 25")
List<Location> getNearbyLocations(
    @Param("latitude") Double latitude,
    @Param("longitude") Double longitude,
    @Param("radius") Double radius
);

Additional Checks #

• Ensure all @Param annotations are complete (your original code was truncated to @Param("rad...")—make sure it matches the query parameters like :radius).
• Verify your database supports the trigonometric functions used (acos, cos, sin, radians). Most modern databases (MySQL, PostgreSQL, H2) do, but double-check if you're using a niche DB.

内容的提问来源于stack exchange，提问作者faisal najib