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? — dev4Fun
– dev4Fun, Commented May 16, 2017 at 14:45
I added some clearification on why I think it is not a duplicate/is not answered there — portux
– portux, Commented May 20, 2017 at 12:19

Cepr0 · Accepted Answer · 2017-05-16 20:26:51Z

3

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.

answered May 16, 2017 at 20:26

Cepr0

31.1k9 gold badges82 silver badges105 bronze badges

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

Comments

Tim Mickelson · Accepted Answer · 2017-05-16 20:39:40Z

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, 2017 at 20:39

Tim Mickelson

1649 bronze badges

1 Comment

Anand Over a year ago

Question is related to How to Limit Query Result Size and not about SORT

Oleg Baranenko · Accepted Answer · 2020-03-26 09:10:03Z

1

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

List<Person> findTop100By();

answered Mar 26, 2020 at 9:10

Oleg Baranenko

3982 silver badges16 bronze badges

Comments

MAC · Accepted Answer · 2021-06-02 21:04:24Z

1

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

answered Jun 2, 2021 at 21:04

MAC

112 bronze badges

Comments

StanislavL · Accepted Answer · 2017-05-16 14:40:18Z

0

You need to extend PagingAndSortingRepository

and add method

Page<Person> listAllByPage(Pageable pageable)

See the example

answered May 16, 2017 at 14:40

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Collectives™ on Stack Overflow

Spring Data: limit result for custom query

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related