Spring Boot JPA原生查询中PostGIS计算距离映射至实体字段问题

以下是三种可行的解决方案，均能避免重复计算距离，同时将查询得到的distance_in_m值填充到distanceInM字段中：

方案1：使用@SqlResultSetMapping + @NamedNativeQuery映射字段 #

通过显式配置结果集映射，让Hibernate忽略@Transient的默认行为，将查询列与实体字段绑定：

@Entity
@Table(name = "places")
@NamedNativeQuery(
    name = "PlaceDto.findNearbyPlaces",
    query = "WITH distance_query AS (" +
            "   SELECT p.*, ST_Distance(p.location, ST_MakePoint(:lng, :lat)) AS distance_in_m " +
            "   FROM places p" +
            ") " +
            "SELECT id, update_date, name, location, address, distance_in_m FROM distance_query " +
            "WHERE distance_in_m > :lastDistanceInM " +
            "ORDER BY distance_in_m ASC",
    resultSetMapping = "PlaceDtoWithDistanceMapping"
)
@SqlResultSetMapping(
    name = "PlaceDtoWithDistanceMapping",
    entities = @EntityResult(
        entityClass = PlaceDto.class,
        fields = {
            @FieldResult(name = "id", column = "id"),
            @FieldResult(name = "updateDate", column = "update_date"),
            @FieldResult(name = "name", column = "name"),
            @FieldResult(name = "location", column = "location"),
            @FieldResult(name = "address", column = "address"),
            @FieldResult(name = "distanceInM", column = "distance_in_m")
        }
    )
)
public class PlaceDto {
    // 原有字段和方法保持不变
    @Transient
    public Double distanceInM;
    
    // 补充setter方法（原代码未展示，需添加）
    public void setDistanceInM(Double distanceInM) {
        this.distanceInM = distanceInM;
    }
}

修改Repository中的查询，引用上述命名查询：

@Query(name = "PlaceDto.findNearbyPlaces", nativeQuery = true)
List<PlaceDto> findNearbyPlaces(
        double lat,
        double lng,
        double lastDistanceInM
);

方案2：使用专用DTO接收查询结果 #

创建仅用于接收查询结果的DTO类，避免在实体类中混入非持久化逻辑：

public class PlaceWithDistanceDto {
    private String id;
    private Date updateDate;
    private String name;
    private Point location;
    private String address;
    private Double distanceInM;

    // 全参构造函数，参数顺序需与查询返回列顺序完全匹配
    public PlaceWithDistanceDto(String id, Date updateDate, String name, Point location, String address, Double distanceInM) {
        this.id = id;
        this.updateDate = updateDate;
        this.name = name;
        this.location = location;
        this.address = address;
        this.distanceInM = distanceInM;
    }

    // 生成所有字段的Getter方法
}

修改Repository方法，直接返回该DTO：

@Query(value = "WITH distance_query AS (" +
        "   SELECT p.id, p.update_date, p.name, p.location, p.address, ST_Distance(p.location, ST_MakePoint(:lng, :lat)) AS distance_in_m " +
        "   FROM places p" +
        ") " +
        "SELECT * FROM distance_query " +
        "WHERE distance_in_m > :lastDistanceInM " +
        "ORDER BY distance_in_m ASC", nativeQuery = true)
List<PlaceWithDistanceDto> findNearbyPlaces(
        double lat,
        double lng,
        double lastDistanceInM
);

方案3：手动转换查询结果 #

在Repository中返回原始结果数组，在Service层手动组装实体并设置距离字段：

首先修改Repository方法：

@Query(value = "WITH distance_query AS (" +
        "   SELECT p.*, ST_Distance(p.location, ST_MakePoint(:lng, :lat)) AS distance_in_m " +
        "   FROM places p" +
        ") " +
        "SELECT p, distance_in_m FROM distance_query " +
        "WHERE distance_in_m > :lastDistanceInM " +
        "ORDER BY distance_in_m ASC", nativeQuery = true)
List<Object[]> findNearbyPlacesRaw(
        double lat,
        double lng,
        double lastDistanceInM
);

然后在Service层处理结果：

import java.util.List;
import java.util.stream.Collectors;

public class PlaceService {
    private final PlaceRepository placeRepository;

    // 构造函数注入Repository
    public PlaceService(PlaceRepository placeRepository) {
        this.placeRepository = placeRepository;
    }

    public List<PlaceDto> getNearbyPlaces(double lat, double lng, double lastDistanceInM) {
        List<Object[]> rawResults = placeRepository.findNearbyPlacesRaw(lat, lng, lastDistanceInM);
        return rawResults.stream()
                .map(result -> {
                    PlaceDto place = (PlaceDto) result[0];
                    place.setDistanceInM((Double) result[1]);
                    return place;
                })
                .collect(Collectors.toList());
    }
}

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