基于给定经纬度与距离，在JPA Repository中检索位置经纬度的替代方案

Hey there! Since you’ve already got a working JPQL solution with @Query, let’s dive into some reusable alternatives that might fit different use cases or make your code more maintainable. Here are a few solid options to consider:

This approach keeps your repository interface clean by encapsulating the distance logic in a separate implementation class—great for reusability and separation of concerns.

First, define your main repository interface that extends both JpaRepository and a custom interface:

public interface LocationRepository extends JpaRepository<Location, Long>, LocationRepositoryCustom {
    // Your existing methods go here, plus the custom distance query method
}

Next, create the custom interface to declare your distance query method:

public interface LocationRepositoryCustom {
    List<Location> findLocationsWithinDistance(double targetLat, double targetLon, double distanceThreshold);
}

Then implement the custom interface with your distance calculation logic (using Haversine formula in this example):

@Repository
public class LocationRepositoryImpl implements LocationRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Location> findLocationsWithinDistance(double targetLat, double targetLon, double distanceThreshold) {
        String jpql = """
            SELECT l FROM Location l 
            WHERE (6371 * acos(cos(radians(:targetLat)) * cos(radians(l.latitude)) * 
            cos(radians(l.longitude) - radians(:targetLon)) + sin(radians(:targetLat)) * 
            sin(radians(l.latitude)))) <= :distanceThreshold
            """;

        return entityManager.createQuery(jpql, Location.class)
                .setParameter("targetLat", targetLat)
                .setParameter("targetLon", targetLon)
                .setParameter("distanceThreshold", distanceThreshold)
                .getResultList();
    }
}

Now you can call this method anywhere in your code, and if you need to adjust the distance calculation logic later, you only modify the implementation class.

If you need dynamic querying (e.g., combining distance filters with other conditions like category or date), the Specification API is perfect. It lets you build reusable query predicates.

First, create a utility class to hold your distance specification:

public class LocationSpecifications {
    public static Specification<Location> withinDistance(double targetLat, double targetLon, double distanceThreshold) {
        return (root, query, criteriaBuilder) -> {
            // Build the Haversine formula using CriteriaBuilder functions
            Expression<Double> radiansTargetLat = criteriaBuilder.function("radians", Double.class, criteriaBuilder.literal(targetLat));
            Expression<Double> radiansTargetLon = criteriaBuilder.function("radians", Double.class, criteriaBuilder.literal(targetLon));
            Expression<Double> radiansLat = criteriaBuilder.function("radians", Double.class, root.get("latitude"));
            Expression<Double> radiansLon = criteriaBuilder.function("radians", Double.class, root.get("longitude"));

            Expression<Double> distance = criteriaBuilder.prod(
                criteriaBuilder.literal(6371.0),
                criteriaBuilder.function("acos", Double.class,
                    criteriaBuilder.sum(
                        criteriaBuilder.prod(
                            criteriaBuilder.function("cos", Double.class, radiansTargetLat),
                            criteriaBuilder.prod(
                                criteriaBuilder.function("cos", Double.class, radiansLat),
                                criteriaBuilder.function("cos", Double.class, criteriaBuilder.diff(radiansLon, radiansTargetLon))
                            )
                        ),
                        criteriaBuilder.prod(
                            criteriaBuilder.function("sin", Double.class, radiansTargetLat),
                            criteriaBuilder.function("sin", Double.class, radiansLat)
                        )
                    )
                )
            );

            return criteriaBuilder.lessThanOrEqualTo(distance, distanceThreshold);
        };
    }
}

Update your repository to extend JpaSpecificationExecutor:

public interface LocationRepository extends JpaRepository<Location, Long>, JpaSpecificationExecutor<Location> {
}

Then use it in your service layer—you can even combine it with other specifications:

// Fetch locations within 10km of the target point
List<Location> nearbyLocations = locationRepository.findAll(
    LocationSpecifications.withinDistance(48.8566, 2.3522, 10.0)
);

// Example: Combine with a filter for "restaurant" locations
Specification<Location> combinedSpec = Specification.where(
    LocationSpecifications.withinDistance(48.8566, 2.3522, 10.0)
    .and((root, query, cb) -> cb.equal(root.get("category"), "restaurant"))
);
List<Location> nearbyRestaurants = locationRepository.findAll(combinedSpec);

If you’re using a database with spatial support (like PostgreSQL with PostGIS, MySQL with spatial extensions), leveraging native SQL can give you better performance (thanks to spatial indexes) and access to built-in distance functions.

Add a native query method to your repository:

public interface LocationRepository extends JpaRepository<Location, Long> {
    @Query(value = """
        SELECT * FROM location l 
        WHERE ST_Distance(
            ST_SetSRID(ST_MakePoint(l.longitude, l.latitude), 4326),
            ST_SetSRID(ST_MakePoint(:targetLon, :targetLat), 4326)
        ) <= :distanceThreshold
        """, nativeQuery = true)
    List<Location> findNearbyLocations(
        @Param("targetLat") double targetLat,
        @Param("targetLon") double targetLon,
        @Param("distanceThreshold") double distanceThreshold
    );
}

This uses PostGIS’s ST_Distance function—adjust the SQL to match your database’s spatial functions. For MySQL, you might use ST_Distance_Sphere instead.

If you ever need to calculate distances in-memory (e.g., for small datasets or post-processing), encapsulate the logic in a utility class that you can reuse anywhere in your codebase:

public class DistanceUtils {
    private static final double EARTH_RADIUS_KM = 6371.0;

    public static double calculateHaversineDistance(double lat1, double lon1, double lat2, double lon2) {
        double dLat = Math.toRadians(lat2 - lat1);
        double dLon = Math.toRadians(lon2 - lon1);
        
        double a = Math.sin(dLat / 2) * Math.sin(dLat / 2) +
                   Math.cos(Math.toRadians(lat1)) * Math.cos(Math.toRadians(lat2)) *
                   Math.sin(dLon / 2) * Math.sin(dLon / 2);
        
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        return EARTH_RADIUS_KM * c;
    }
}

Use it to filter locations in-memory (note: this is only efficient for small datasets—always prefer database-level filtering for large datasets):

List<Location> allLocations = locationRepository.findAll();
List<Location> nearbyLocations = allLocations.stream()
    .filter(location -> DistanceUtils.calculateHaversineDistance(
        targetLat, targetLon,
        location.getLatitude(), location.getLongitude()
    ) <= distanceThreshold)
    .collect(Collectors.toList());

内容的提问来源于stack exchange，提问作者Ala'a Mezian