为什么Hibernate执行原生SQL的结果与SQL Server直接执行结果不一致

问题根因 #

该现象本质是JPA实体的关联映射逻辑覆盖了原生SQL的查询结果：
你的Client实体类中配置了@OneToMany类型的payments关联属性，即使你通过原生SQL的OUTER APPLY只查询了最新1条支付记录的字段，JPA实现（默认为Hibernate）在将结果映射到Client实体时，会自动触发额外的关联查询，拉取当前客户对应的所有支付记录填充payments集合，完全忽略你原生SQL中查询得到的p.Credit、p.Debit结果。

解决方案（最稳妥的DTO投影方案） #

不要直接将查询结果映射到带关联集合的Client实体，专门定义一个仅承载所需字段的DTO类，避免触发额外关联查询：

1. 定义DTO类

// 字段和查询返回的列一一对应即可
public class ClientWithLastPaymentDTO {
    private Long id;
    private String customerNumber;
    private String name;
    private String surname;
    private String area;
    private String city;
    private String address;
    private String phoneNumber;
    private Long customerTypeID;
    private Boolean enabled;
    private LocalDateTime dateCreated;
    private BigDecimal credit;
    private BigDecimal debit;
    
    // 全参构造器、getter方法
    public ClientWithLastPaymentDTO(Long id, String customerNumber, String name, String surname, String area, String city, String address, String phoneNumber, Long customerTypeID, Boolean enabled, LocalDateTime dateCreated, BigDecimal credit, BigDecimal debit) {
        this.id = id;
        this.customerNumber = customerNumber;
        this.name = name;
        this.surname = surname;
        this.area = area;
        this.city = city;
        this.address = address;
        this.phoneNumber = phoneNumber;
        this.customerTypeID = customerTypeID;
        this.enabled = enabled;
        this.dateCreated = dateCreated;
        this.credit = credit;
        this.debit = debit;
    }
    // 省略getter
}

2. 修改查询逻辑，手动转换查询结果为DTO

public List<ClientWithLastPaymentDTO> getClients() {
    List<Object[]> results = entityManager.createNativeQuery("SELECT top 17 c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Credit, p.Debit\n" +
                    "FROM tblCustomer c OUTER APPLY\n"+
                    "     (SELECT TOP 1 p.*\n" +
                    "      FROM tblPayments p\n" +
                    "      WHERE c.CustomerNumber = p.CustomerNumber\n" +
                    "      ORDER BY p.id DESC\n" +
                    "     ) p;")
            .getResultList();
    // 手动转换为DTO
    return results.stream().map(row -> new ClientWithLastPaymentDTO(
            (Long) row[0],
            (String) row[1],
            (String) row[2],
            (String) row[3],
            (String) row[4],
            (String) row[5],
            (String) row[6],
            (String) row[7],
            (Long) row[8],
            (Boolean) row[9],
            (LocalDateTime) row[10],
            (BigDecimal) row[11],
            (BigDecimal) row[12]
    )).toList();
}

其他可选方案 #

如果一定要复用Client实体，可以采用以下两种方式：

• 在Client的payments关联属性上添加Hibernate专有注解@Where(clause = "id = (SELECT max(id) FROM tblPayments p WHERE p.customerNumber = customerNumber)")，强制关联只拉取最新的支付记录，缺点是所有查询Client的场景都会生效
• 修改Client实体，新增lastPaymentCredit、lastPaymentDebit两个单独字段，原生SQL查询到的结果直接映射到这两个字段，移除不需要的全量payments关联

内容的提问来源于stack exchange，提问作者Qëndrim Izairi