java - 只有来自自定义查询的最后一条记录

Question

以下查询返回一个列表，但我只对列表的最后一个元素感兴趣。

@Query("SELECT r FROM Reservation r WHERE r.reservationSeance.id=:seanceId AND r.seanceDate=:seanceDate")
public Reservation findReservationBySeanceDateAndSeanceId(@Param("seanceId") int seanceId, @Param("seanceDate") java.time.LocalDate seanceDate);

我应该如何重写 SQL 查询以实现我的想法？

Answer 1

一种可能的解决方案是使用 ORDER BY r.id DESC :

@Query("SELECT r FROM Reservation r  " +
        "WHERE r.reservationSeance.id=:seanceId AND r.seanceDate=:seanceDate " +
        "ORDER BY r.id DESC")
public Reservation findReservationBySeanceDateAndSeanceId(
        @Param("seanceId") int seanceId,
        @Param("seanceDate") java.time.LocalDate seanceDate, Pageable pageable);

并且由于在 JPQL 中无法使用限制，您可以使用 Pageable

Pageable pageable = new PageRequest(0, 1);
Reservation reservation = r.findReservationBySeanceDateAndSeanceId(seanceId, seanceDate, pageable);

---

没有查询的另一种可能的解决方案:

public Reservation findTop1ByReservationSeanceAndSeanceDateOrderByIdDesc(
               ReservationSeanceEntity reservationSenace, 
               java.time.LocalDate seanceDate
)

在第二个解决方案中，您必须传递 ReservationSeance 对象而不是 ReservationSeance 的 id，查询可以读作:

Find top 1 (first one) by `ReservationSeance` and `SeanceDate` order by `Id` Desc order