Fetching only first/last element using Spring Data JPA and @Query annotation

Question

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.

Why does your method return List<X> 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
– manish, Commented Sep 9, 2015 at 9:44
@manish: 1. regarding the List<X>, 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 stackoverflow.com/questions/9314078/… which pretty much solves the problem. — Ueli Hofstetter
– Ueli Hofstetter, Commented Sep 9, 2015 at 12:53
Found this to be covered by the second and fourth answer in [stackoverflow.com/questions/9314078/… [1]: stackoverflow.com/questions/9314078/… — Ueli Hofstetter
– Ueli Hofstetter, Commented Sep 9, 2015 at 12:55
Possible duplicate of setMaxResults for Spring-Data-JPA annotation? — emilyk
– emilyk, Commented Jan 29, 2017 at 6:51
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
– Martin Bonner supports Monica, Commented Mar 8, 2017 at 15:39

ahmetcetin · Accepted Answer · 2019-07-29 11:08:25Z

20

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)

edited Jul 29, 2019 at 11:08

answered Jun 15, 2017 at 19:28

ahmetcetin

3,0201 gold badge26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

andy Over a year ago

As of spring-data 2.0 it's PagaRequest.of(0, 1) instead of new PageRequest(0, 1).

Vasiliki M. Over a year ago

The parameter in the function should be findFirstElement(PageRequest limit) and not Pageable, right? At least for me it doesn't work with Pageable.

comiventor · Accepted Answer · 2018-01-02 12:58:09Z

9

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

answered Jan 2, 2018 at 12:58

comiventor

4,1725 gold badges59 silver badges80 bronze badges

Comments

Martin Bonner supports Monica · Accepted Answer · 2017-03-08 15:38:07Z

-2

Try to do this:

@Query(value = "SELECT xelement FROM x xelement ORDER BY xelement.z  LIMIT 1",
       nativeQuery = true)

edited Mar 8, 2017 at 15:38

Martin Bonner supports Monica

29.1k3 gold badges56 silver badges91 bronze badges

answered Mar 8, 2017 at 15:06

fjgarzon

384 bronze badges

1 Comment

kidnan1991 Over a year ago

It just does not work. U are mixing between native query with JPQL syntax.

Collectives™ on Stack Overflow

Fetching only first/last element using Spring Data JPA and @Query annotation

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related