Spring Data: limit result for custom query

Question

In my Spring Data repository I (have to) use custom queries using the @Query annotation. I know I can limit the number of results in a named query like that

Iterable<Person> findFirst5OrderByLastName()

or that it is possible to limit the number of results by passing a pageable like that

Iterable<Person> findByLastName(String lastName, Pageable pageable)

But is it possible to achieve the same when using a custom @Query annotation?

TIA

EDIT

as I see my question is a little confusing, some clearification: What I want is to limit the number of the results I get, when using a custom query, so that I neither

1) need to specify the result-size through a pageable

2) need to use a named query to specify the result-size

In fact I want the limitation of the number of results to be completely transparent when invoking method (hence not passing a Pageable) and to not rely on the naming-scheme of Spring Data (as the meaning/function of the method is best conveyed through a custom name)

Possible duplicate of [setMaxResults for Spring-Data-JPA annotation?](http://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation) — dev4Fun, May 16 '17 at 14:45
I added some clearification on why I think it is not a duplicate/is not answered there — portux, May 20 '17 at 12:19

score 3 · Accepted Answer · answered May 16 '17 at 20:26

You can try this:

@Entity
@Table(name = "persons") 
public class Person {
    //...
}

@Query(value = "select * from persons limit 50", nativeQuery = true)
List<Person> getFirst50();

Don't forget to check whether your SQL server supports the limit keyword.

score 1 · Answer 2 · answered May 16 '17 at 20:39

1

Of course you can use @Query to order a result, it is an JPQL query e.g.

@Query("SELECT u FROM User u ORDER BY u.name ASC")

Further sorting can be done be either providing a PageRequest or using Sort directly.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.sorting

answered May 16 '17 at 20:39

Tim Mickelson

164
9

1

Question is related to How to Limit Query Result Size and not about SORT – Anand Jan 08 '18 at 09:58

score 1 · Answer 3 · answered Mar 26 '20 at 09:10

1

I would suggest to add "By" but without parameter, it would work:

List<Person> findTop100By();

answered Mar 26 '20 at 09:10

Oleg Baranenko

398
2
16

score 0 · Answer 4 · answered May 16 '17 at 14:40

0

You need to extend PagingAndSortingRepository

and add method

Page<Person> listAllByPage(Pageable pageable)

See the example

answered May 16 '17 at 14:40

StanislavL

56,971
9
68
98

score 0 · Answer 5 · answered Jun 02 '21 at 21:04

Hello you can try this

@Query("...")
List<YourDTO>getData(Pageable pageable)

In your serviceImpl

    List<YourDTO> getData(){
    int limit=10;
    Pageable pageable = PageRequest.of(0, limit);
    return this.repo.getData(pageable);
}

You should avoid nativeQuery

Spring Data: limit result for custom query

5 Answers5

Linked