I've recently noticed that JPQL doesn't support LIMIT keyword to set a limit in the query when there's more than one result. In my model I have two entities which have a relation with each other. By the way of illustration, suppose we have two entities, Bag and Student. One bag is related to a student but an student could have more than one bag. In this way, I have the following instructions inside my bag class in order to create that relationship between both entities:
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "id_student") // Column to relate bags with students.
private Student associated_student;
Now, suppose each bag has a creation date (creation_date) and I'm interested on getting the bag with most recent creation date that is related to a given student.
As I mentioned at the beginning, knowing that LIMIT keyword is not supported in JPQL, I solved the problem using the following @Query.
@Query("select f from Bag f where f.associated_student.id_student = ?1 order by f.creation_date asc")
List<Bag> findBagsByStudentId(Long id_student);
Now, in order to select the bag with the most creation date, I do the following steps:
Bag recent_bag = BagRepository.findBagsByStudentId(id_student).get(0);
And here is the deal. I'd like not to use get(0) to access the bag I'm interested on, I've tried using nativeQuery like this
@Query(value = "select * from bags f where f.id_student = ?1 order by f.creation_date asc limit 1", nativeQuery = true)
but it doesn't work. Any solution/suggestion will be appreciated, thanks in advance!
