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)

Answer 1

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.

Answer 2

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

Answer 3

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

List<Person> findTop100By();

Answer 4

You need to extend PagingAndSortingRepository

and add method

Page<Person> listAllByPage(Pageable pageable)

See the example

Answer 5

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