Only Last Record From Custom Query

The following query return a list but I am only interested in the last element of the list. @Query('SELECT r FROM Reservation r WHERE r.reservationSeance.id=:seanceId AND r.seance

Solution 1:

One possible solution is to use 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);

and because there are no way to use limit in JPQL, you can use Pageable

Pageablepageable=newPageRequest(0, 1);
Reservationreservation= r.findReservationBySeanceDateAndSeanceId(seanceId, seanceDate, pageable);

---

Another possible solution without Query :

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

In this second solution you have to pass the ReservationSeance Object and not the id of ReservationSeance, the query can be read as :

Find top 1 (firstone) by `ReservationSeance` and `SeanceDate` orderby `Id` Descorder

Solution 2:

You need to provide a couple more parameters to your query, especially an ORDER BY clause.

To get the latest seanceId, you'll want to order your results by that id, but in reverse order. Then, just tell the query to return only the first result:

SELECT r FROM Reservation r 
WHERE r.reservationSeance.id=:seanceId 
AND r.seanceDate=:seanceDate 
ORDERBY seanceId 
DESC LIMIT 1;

Solution 3:

You can try the following, if you are using mysql as your database:

SELECT r 
FROM Reservation r 
WHERE r.reservationSeance.id=:seanceId 
  AND r.seanceDate=:seanceDate 
orderby r.reservationSeance.id desc limit 0,1