27

EDIT: Solutions to this problem are provided in the second and fourth answer regarding this question setMaxResults for Spring-Data-JPA annotation?

Goal: Fetch the largest/smallest element by property z using a Spring Data JPA repository and the Spring Query annotation.

What I have so far

@Query("SELECT xelement FROM x xelement ORDER BY xelement.z")
public List<X> findFirstElement();

Problem: This query fetches all elements (which is not really effective). If I would use the EntityManager direcly, I could set the number of results using 

entityManager.setMaxResults(1)

to only get the first element.

Question: How do I specify the maximum number of results using the @Query annotation?

Idea: Is using a PageRequest of size 0 the way to go?

Constraints: I am aware of the "FindFirstBy...." query feature but I want/have to use the @Query annotation.

Community
  • 1
  • 1
Ueli Hofstetter
  • 2,409
  • 4
  • 29
  • 52
  • Why does your method return `List` when it is only expected to return a single element? If the query is really that simple, you can avoid using `@Query` and simply have `findFirstOrderByZ()`. Otherwise, you will have to use the `LIMIT` keyword to restrict the number of records returned `SELECT x FROM xelement x ORDER BY x.z LIMIT 1`. – manish Sep 09 '15 at 09:44
  • @manish: 1. regarding the List, this was just to be flexible enough in case I would switch an arbitrary number of items, but yes, in principle you are correct, returning a list is overkill in this case. 2. Regarding the "findFirst.." --> see constraints. 3. Are you sure the limit keyword is available in JPQL? I guess I have to check it .... I also found http://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation which pretty much solves the problem. – Ueli Hofstetter Sep 09 '15 at 12:53
  • Found this to be covered by the second and fourth answer in [http://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation][1] [1]: http://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation – Ueli Hofstetter Sep 09 '15 at 12:55
  • Possible duplicate of [setMaxResults for Spring-Data-JPA annotation?](http://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation) – emilyk Jan 29 '17 at 06:51
  • 2
    Please don't refer to answers by their order in the list. *This order changes*. Please link directly to the answers. – Martin Bonner supports Monica Mar 08 '17 at 15:39
  • https://stackoverflow.com/questions/38045439/technical-differences-between-spring-data-jpas-findfirst-and-findtop – Radhesh Khanna May 11 '21 at 10:54

3 Answers3

19

You can use the limit property of sql just by adding nativeQuery to @Query annotation. However, there is another and a better way of doing this. Pageable class in your repository method will solve your problem without touching your @Query annotation:

@Query(value = "SELECT xelement FROM x xelement ORDER BY xelement.z")
List<X> findFirstElement(Pageable limit);

To set the limit and offset, use should call this repository method as follows:

List<X> xValues = xRepository.findFirstElement(new PageRequest(0, 1));

Here 1 corresponds to the limit which you want.

UPDATE (SPRING DATA 2.0)

Use PageRequest.of(0, 1) instead of new PageRequest(0, 1)

ahmetcetin
  • 2,621
  • 1
  • 22
  • 38
  • 1
    As of spring-data 2.0 it's `PagaRequest.of(0, 1)` instead of `new PageRequest(0, 1)`. – andy Jul 29 '19 at 08:56
  • The parameter in the function should be `findFirstElement(PageRequest limit)` and not `Pageable`, right? At least for me it doesn't work with Pageable. – Vasiliki M. May 31 '21 at 14:40
8

The closest JPA query syntax I can think for your use case is findFirstByZIsNotNullOrderByZAsc. This should eliminate the need to write custom native query.

comiventor
  • 3,922
  • 5
  • 50
  • 77
-2

Try to do this:

@Query(value = "SELECT xelement FROM x xelement ORDER BY xelement.z  LIMIT 1",
       nativeQuery = true)
Martin Bonner supports Monica
  • 28,528
  • 3
  • 51
  • 88
fjgarzon
  • 44
  • 4