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)

Solution 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.

Solution 2

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

List<Person> findTop100By();

Solution 3

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

Comments

[dev4Fun]

Possible duplicate of setMaxResults for Spring-Data-JPA annotation?

[portux]

I added some clearification on why I think it is not a duplicate/is not answered there

[Anand]

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