JPA原生查询结果与pgAdmin不一致问题求助

我需要查询指定入住（check_in）和退房（check_out）日期内无预订记录的房间，编写了JPA原生查询，在pgAdmin中测试逻辑正常，但在Repository中调用时返回的Room实体结果却不一致。

我尝试过以下方法，但问题仍未解决：

• 使用左连接筛选无预订的行
• 修改Reservation实体中Room属性的FetchType为EAGER

相关代码 #

查询代码 #

@Query(value = "SELECT * FROM \"room\" r " +
            "WHERE r.\"id\" NOT IN (" +
            "SELECT rv.\"room_id\" " +
            "FROM \"reservation\" rv " +
            "WHERE :checkInDate < rv.\"check_out\" " +
            "AND :checkOutDate > rv.\"check_in\") " +
            "AND r.\"kids\" = :kids " +
            "AND r.\"adult\" = :adults",
            nativeQuery = true)
List<Room> findAvailableRooms(@Param("checkInDate") LocalDate checkInDate,
                              @Param("checkOutDate") LocalDate checkOutDate,
                              @Param("kids") int kids,
                              @Param("adults") int adults);

Reservation实体 #

package com.example.hotelManagmentSystem.dataproviders.entity;

import jakarta.persistence.*;
import lombok.*;

import java.time.LocalDate;

@Builder
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Entity
@Table(name = "reservation")
public class Reservation {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "hotel_id")
    private Hotel hotel;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "client_id")
    private User client;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "room_id")
    private Room room;

    @Column(name = "check_in")
    private LocalDate checkIn;

    @Column(name = "check_out")
    private LocalDate checkOut;

    @Column(name = "adults")
    private Integer adults;

    @Column(name = "kids")
    private Integer kids;

    @Column(name = "net_value")
    private Double netValue;

    @Column(name = "tax_rate")
    private Double taxRate;

    @Column(name = "discount")
    private Double discount;

    @Column(name = "created_at")
    private LocalDate createdAt;

    @Column(name = "total")
    private Double total;
}

Room实体 #

package com.example.hotelManagmentSystem.dataproviders.entity;

import jakarta.persistence.*;
import lombok.*;

import java.util.LinkedHashSet;
import java.util.Set;

@Builder
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Entity
@Table(name = "room")
public class Room {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "hotel_id")
    private Hotel hotel;

    @Column(name = "type")
    private String type;

    @Column(name = "adult")
    private Integer adult;

    @Column(name = "kids")
    private Integer kids;

    @Column(name = "description", length = Integer.MAX_VALUE)
    private String description;

    @OneToMany(mappedBy = "room")
    private Set<RoomPrice> roomPrices;
}

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