Spring中如何将客户端传入的经纬度绑定到SQL查询本地变量？

Hey there! Let's break down how to get your nearest Garda station query working in Spring, plus some key optimizations to make it efficient—especially since this is your first large Spring project, I’ll keep things clear and practical.

First, let's fix up your controller to handle coordinates properly and execute the SQL query safely. Right now you're receiving coordinates as strings, so we'll convert them to double (matching your database column type) and use parameterized queries to avoid SQL injection risks.

Step 1: Set Up JdbcTemplate and Update the Controller #

Make sure JdbcTemplate is available (it's included by default in Spring Boot). Inject it into your controller and adjust the logic to run the query:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

@RestController
public class GardaStationController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @PostMapping("/results")
    public ResponseEntity<?> getNearestStation(
            @RequestParam("latitude") String latitudeStr,
            @RequestParam("longitude") String longitudeStr) {

        try {
            // Convert string coordinates to doubles
            double latitude = Double.parseDouble(latitudeStr);
            double longitude = Double.parseDouble(longitudeStr);

            // Validate coordinate ranges (latitude: -90 to 90, longitude: -180 to 180)
            if (latitude < -90 || latitude > 90 || longitude < -180 || longitude > 180) {
                return new ResponseEntity<>("Invalid coordinate values", HttpStatus.BAD_REQUEST);
            }

            // Parameterized SQL query (safe from injection)
            String sql = """
                SELECT * FROM garda_station
                ORDER BY ((garda_station_latitude - ?) * (garda_station_latitude - ?)) +
                         ((garda_station_longitude - ?) * (garda_station_longitude - ?))
                ASC LIMIT 1
            """;

            // Map result to a GardaStation object
            GardaStation station = jdbcTemplate.queryForObject(sql,
                    new Object[]{latitude, latitude, longitude, longitude},
                    (rs, rowNum) -> new GardaStation(
                            rs.getInt("garda_station_id"),
                            rs.getDouble("garda_station_latitude"),
                            rs.getDouble("garda_station_longitude"),
                            rs.getString("garda_station_name")
                    )
            );

            return new ResponseEntity<>(station, HttpStatus.OK);

        } catch (NumberFormatException e) {
            return new ResponseEntity<>("Invalid latitude/longitude format", HttpStatus.BAD_REQUEST);
        } catch (Exception e) {
            return new ResponseEntity<>("Failed to fetch nearest station", HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
}

Step 2: Create a GardaStation Model Class #

You'll need a simple POJO to hold station data so Spring can serialize it to JSON for your AJAX response:

public class GardaStation {
    private int id;
    private double latitude;
    private double longitude;
    private String name;

    // Constructor
    public GardaStation(int id, double latitude, double longitude, String name) {
        this.id = id;
        this.latitude = latitude;
        this.longitude = longitude;
        this.name = name;
    }

    // Getters (required for JSON serialization)
    public int getId() { return id; }
    public double getLatitude() { return latitude; }
    public double getLongitude() { return longitude; }
    public String getName() { return name; }
}

Your current query works, but it does a full table scan every time—this gets slow with many stations. Here's how to fix that:

a. Use Spatial Indexes & Accurate Distance Calculation #

Euclidean distance isn't accurate for Earth's curvature. Instead, use your database's spatial functions (like MySQL's ST_Distance_Sphere which calculates real spherical distance in meters) and add a spatial index to speed up queries:

First, add the spatial index to your table:

ALTER TABLE garda_station ADD SPATIAL INDEX idx_garda_coords(garda_station_latitude, garda_station_longitude);

Then update your SQL query to use the spatial function:

SELECT * FROM garda_station
ORDER BY ST_Distance_Sphere(POINT(garda_station_longitude, garda_station_latitude), POINT(?, ?))
ASC LIMIT 1;

Replace the SQL string in your controller with this version—it's more accurate and faster with the index.

b. Switch to Spring Data JPA for Cleaner Code #

If you want to move away from raw SQL, use Spring Data JPA with Hibernate Spatial support. This lets you work with spatial types (like Point) directly in your model:

1. Add Hibernate Spatial dependencies to your pom.xml or build.gradle.
2. Update your model to use a Point field:

import org.locationtech.jts.geom.Point;
import javax.persistence.*;
import org.hibernate.annotations.Type;

@Entity
@Table(name = "garda_station")
public class GardaStation {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int garda_station_id;

    @Spatial
    @Column(columnDefinition = "POINT")
    @Type(type = "org.hibernate.spatial.GeometryType")
    private Point location;

    private String garda_station_name;

    // Getters and setters
}

3. Create a repository interface to handle queries:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.locationtech.jts.geom.Point;

public interface GardaStationRepository extends JpaRepository<GardaStation, Integer> {
    @Query(value = "SELECT * FROM garda_station ORDER BY ST_Distance_Sphere(location, :point) ASC LIMIT 1", nativeQuery = true)
    GardaStation findNearestStation(@Param("point") Point point);
}

4. Inject the repository into your controller instead of JdbcTemplate—it's more maintainable for larger projects.

c. Cache Results (If Station Data Doesn't Change Often) #

Garda stations don't move often, so cache frequent queries to reduce database load. Use Spring Cache:

1. Enable caching with @EnableCaching on your main application class.
2. Add @Cacheable to your repository method:

@Cacheable(value = "nearestStation", key = "#point.x + ',' + #point.y")
GardaStation findNearestStation(@Param("point") Point point);

This stores results for repeated coordinate requests, making your app faster.

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