如何在Spring Data JPA原生查询中展示别名列distance

问题说明 #

在接口响应中需要展示原生查询计算出的distance值，已在User实体添加@Transient注解的distance属性，但无法将原生查询中别名为distance的列值映射到该属性，导致响应中没有距离数据。

现有代码示例 #

Repository代码 #

String countDistanceQuery = "(3959 * ACOS(COS(RADIANS(:latitude)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(:longitude)) + SIN(RADIANS(:latitude)) * SIN(RADIANS(latitude))))";


@Query(value = "SELECT a.* , " + countDistanceQuery + " AS distance FROM user a WHERE a.address LIKE %:address% HAVING distance < 25 ORDER BY distance LIMIT 0, 20" , nativeQuery = true)
public List<User> getNearsetLoactionOfSitter(String address, String latitude, String longitude);

User实体代码 #

package com.misha.model;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import lombok.ToString;
import javax.persistence.*;

@Entity
@ToString
@Table(name = "user",uniqueConstraints = { 
        @UniqueConstraint(columnNames = "email") 
    })
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String contactname;
    
    @Column(unique=true)
    private String email;

    private String password;

    private String company;
    
    private String location;

    private String address;

    private String latitude;

    private String longitude;

    private String open;

    private String close;

    private float chargesperhour;

    private String logo;

    private boolean enabled;
    
    @Transient
    private double distance;
    
    @Column(name = "reset_password_token")
    private String resetPasswordToken;
    
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "users_roles", joinColumns = @JoinColumn(name = "id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles = new HashSet<>();

    // 构造方法、getter和setter省略
}

Controller代码 #

@GetMapping("/users/search")
public ResponseEntity<List<User>> searchNearestLocation(@RequestParam String address, @RequestParam String latitude, @RequestParam String longitude){
        if(!address.isEmpty() && !latitude.isEmpty() && !longitude.isEmpty()) {             
    try {
    List<User> list = userService.getNearsetLoactionOfSitter(address, latitude, longitude);
                
                
    if( list.isEmpty() || list.size() == 0 ) {
    return new ResponseEntity<>(HttpStatus.NO_CONTENT);
        }       
        return new ResponseEntity<>(list, HttpStatus.OK);           
    }catch(Exception e) {
        return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
            }           
        }else {
            return new ResponseEntity<>(HttpStatus.NO_CONTENT);
        }       
}

解决方案 #

JPA原生查询返回实体时，不会自动映射@Transient属性，需通过以下方式手动处理：

方案一：使用DTO接收查询结果并转换为User #

1. 创建UserDistanceDTO类，包含User的所有属性和distance字段：

public class UserDistanceDTO {
    private Integer id;
    private String contactname;
    private String email;
    private String password;
    private String company;
    private String location;
    private String address;
    private String latitude;
    private String longitude;
    private String open;
    private String close;
    private float chargesperhour;
    private String logo;
    private boolean enabled;
    private String resetPasswordToken;
    private double distance;

    // 全参构造函数、getter和setter方法
}

2. 修改Repository方法，返回List<UserDistanceDTO>，并明确指定查询的所有列：

@Query(value = "SELECT a.id, a.contactname, a.email, a.password, a.company, a.location, a.address, a.latitude, a.longitude, a.open, a.close, a.chargesperhour, a.logo, a.enabled, a.resetPasswordToken, " + countDistanceQuery + " AS distance FROM user a WHERE a.address LIKE %:address% HAVING distance < 25 ORDER BY distance LIMIT 0, 20" , nativeQuery = true)
public List<UserDistanceDTO> getNearsetLoactionOfSitter(String address, String latitude, String longitude);

3. 在Service层将DTO转换为User对象并设置distance：

public List<User> getNearsetLoactionOfSitter(String address, String latitude, String longitude) {
    List<UserDistanceDTO> dtoList = userRepository.getNearsetLoactionOfSitter(address, latitude, longitude);
    return dtoList.stream().map(dto -> {
        User user = new User();
        user.setId(dto.getId());
        user.setContactname(dto.getContactname());
        user.setEmail(dto.getEmail());
        user.setPassword(dto.getPassword());
        user.setCompany(dto.getCompany());
        user.setLocation(dto.getLocation());
        user.setAddress(dto.getAddress());
        user.setLatitude(dto.getLatitude());
        user.setLongitude(dto.getLongitude());
        user.setOpen(dto.getOpen());
        user.setClose(dto.getClose());
        user.setChargesperhour(dto.getChargesperhour());
        user.setLogo(dto.getLogo());
        user.setEnabled(dto.isEnabled());
        user.setResetPasswordToken(dto.getResetPasswordToken());
        user.setDistance(dto.getDistance());
        // roles字段需单独处理，若查询包含关联数据需额外逻辑
        return user;
    }).collect(Collectors.toList());
}

方案二：使用EntityManager手动映射结果集 #

通过EntityManager执行原生查询，手动提取distance值并设置到User对象：

@Autowired
private EntityManager entityManager;

public List<User> getNearsetLoactionOfSitter(String address, String latitude, String longitude) {
    String sql = "SELECT a.* , " + countDistanceQuery + " AS distance FROM user a WHERE a.address LIKE ?1 HAVING distance < 25 ORDER BY distance LIMIT 0, 20";
    
    // 获取User实体列表
    Query entityQuery = entityManager.createNativeQuery(sql, User.class);
    entityQuery.setParameter(1, "%" + address + "%");
    entityQuery.setParameter(2, latitude);
    entityQuery.setParameter(3, longitude);
    List<User> users = entityQuery.getResultList();
    
    // 获取包含distance的原始结果集
    Query rawQuery = entityManager.createNativeQuery(sql);
    rawQuery.setParameter(1, "%" + address + "%");
    rawQuery.setParameter(2, latitude);
    rawQuery.setParameter(3, longitude);
    List<Object[]> rawResults = rawQuery.getResultList();
    
    // 手动设置distance属性
    for (int i = 0; i < users.size(); i++) {
        Object[] row = rawResults.get(i);
        // distance是结果数组的最后一个元素（a.*对应前面的所有列）
        double distance = (double) row[row.length - 1];
        users.get(i).setDistance(distance);
    }
    
    return users;
}

额外注意事项 #

• 确保JSON序列化框架（如Jackson）会序列化@Transient属性，若未序列化可添加@JsonInclude(JsonInclude.Include.ALWAYS)注解到User类或distance字段。
• 若使用Lombok，需确保distance字段的getter/setter已正确生成（可通过@Data注解或手动编写）。

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