PostgreSQL与Spring Boot JPA查询结果不一致问题及解决

在DataGrip中执行PostgreSQL查询时，前3条数据均能正确返回翻译结果；但在Spring Boot项目中，使用带nativeQuery=true的JPA查询时，仅前2条数据翻译正常，第3条无法返回翻译结果；添加order by后仅第一条正常。尝试固定参数无效，最终通过投影查询并移除nativeQuery=true参数解决问题。

原生SQL查询语句 #

select box_dr.id,
       case when tbox_dr.name is null then box_dr.name else tbox_dr.name end,
       case when tbox_dr.description is null then box_dr.description else tbox_dr.description end,
       case when tbox_dr.hint1 is null then box_dr.hint1 else tbox_dr.hint1 end,
       case when tbox_dr.hint2 is null then box_dr.hint2 else tbox_dr.hint2 end,
       case when tbox_dr.hint3 is null then box_dr.hint3 else tbox_dr.hint3 end,
       type,
       era,
       num
from box_door box_dr
         left join translate_box_door tbox_dr
                   on tbox_dr.box_id = box_dr.box_id
                       and tbox_dr.door_id = box_dr.id
                       and tbox_dr.locale = 'en'
where box_dr.box_id = 0
order by box_dr.id

实体类代码 #

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "box_door")
public class BoxDoorModel {

    @Id
    @GeneratedValue
    private Integer id;
    private String name;

    @JsonIgnoreProperties("box")
    @ManyToOne()
    @JoinColumn(name = "box_id")
    private BoxModel boxModel;

    @Column(columnDefinition = "TEXT")
    private String description;

    @Column(columnDefinition = "TEXT")
    private String hint1;
    @Column(columnDefinition = "TEXT")
    private String hint2;
    @Column(columnDefinition = "TEXT")
    private String hint3;

    @JsonIgnoreProperties("nextBox")
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "next_id", nullable = true)
    private BoxDoorModel nextBoxDoor;

    @Enumerated(EnumType.STRING)
    private BoxDoorType type;

    private int box_num;

    @NotNull
    @ColumnDefault("0.0")
    private Float maxPoints;
    @NotNull
    @ColumnDefault("0.0")
    private Float minPoints;

    @NotNull
    @ColumnDefault("2024")
    private int boxEra;
}

原JPA原生查询代码 #

@Query(value = "select box_dr.id, " +
       "case when tbox_dr.name is null then box_dr.name else tbox_dr.name end, " +
       "case when tbox_dr.description is null then box_dr.description else tbox_dr.description end, " +
       "case when tbox_dr.hint1 is null then box_dr.hint1 else tbox_dr.hint1 end, " +
       "case when tbox_dr.hint2 is null then box_dr.hint2 else tbox_dr.hint2 end, " +
       "case when tbox_dr.hint3 is null then box_dr.hint3 else tbox_dr.hint3 end, " +
       "type, " +
       "era, " +
       "num " +
"from box_door box_dr " +
         "left join translate_box_door tbox_dr " +
                   "on tbox_dr.box_id = box_dr.box_id " +
                       "and tbox_dr.door_id = box_dr.id " +
                       "and tbox_dr.locale = :locale " +
"where box_dr.box_id = :boxId " +
"order by box_dr.id",
            nativeQuery = true)
Optional<List<BoxDoorModel>> findAllDoorByBoxId(
            @Param("boxId") Integer boxId,
            @Param("locale") String locale)

解决方法 #

放弃使用nativeQuery=true的原生查询方式，改用投影查询：

1. 定义包含所需字段的DTO接口或类（例如包含id、name、description等字段）；
2. 使用JPQL编写查询语句，将查询结果映射到该DTO；
3. 移除nativeQuery=true参数，让JPA负责结果的正确映射。

这种方式避免了原生查询中字段映射可能出现的异常，确保所有数据的翻译结果都能正确返回。

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