Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

setMaxResults for Spring-Data-JPA annotation?

People also ask

How do I write a limit query in JPA Repository?

Limiting query results in JPA is slightly different to SQL; we don't include the limit keyword directly into our JPQL. Instead, we just make a single method call to Query#maxResults, or include the keyword first or top in our Spring Data JPA method name. As always, the code is available over on GitHub.

How do you write limits in JPA?

Spring Data JPA supports keywords 'first' or 'top' to limit the query results (e.g. findTopBy....). An optional numeric value can be appended after 'top' or 'first' to limit the maximum number of results to be returned (e.g. findTop3By....). If this number is not used then only one entity is returned.

What is @query annotation in spring boot?

The @Query annotation declares finder queries directly on repository methods. While similar @NamedQuery is used on domain classes, Spring Data JPA @Query annotation is used on Repository interface. This frees the domain classes from persistence specific information, which is a good thing.

How do I run a custom query in Spring data JPA?

Some time case arises, where we need a custom query to fulfil one test case. We can use @Query annotation to specify a query within a repository. Following is an example.


As of Spring Data JPA 1.7.0 (Evans release train).

You can use the newly introduced Top and First keywords that allow you to define query methods like this:

findTop10ByLastnameOrderByFirstnameAsc(String lastname);

Spring Data will automatically limit the results to the number you defined (defaulting to 1 if omitted). Note that the ordering of the results becomes relevant here (either through an OrderBy clause as seen in the example or by handing a Sort parameter into the method). Read more on that in the blog post covering new features of the Spring Data Evans release train or in the documentation.

For previous versions

To retrieve only slices of data, Spring Data uses the pagination abstraction which comes with a Pageable interface on the requesting side as well as a Page abstraction on the result side of things. So you could start with

public interface UserRepository extends Repository<User, Long> {

  List<User> findByUsername(String username, Pageable pageable);
}

and use it like this:

Pageable topTen = new PageRequest(0, 10);
List<User> result = repository.findByUsername("Matthews", topTen);

If you need to know the context of the result (which page is it actually? is it the first one? how many are there in total?), use Page as return type:

public interface UserRepository extends Repository<User, Long> {

  Page<User> findByUsername(String username, Pageable pageable);
}

The client code can then do something like this:

Pageable topTen = new PageRequest(0, 10);
Page<User> result = repository.findByUsername("Matthews", topTen);
Assert.assertThat(result.isFirstPage(), is(true));

Not that we will trigger a count projection of the actual query to be executed in case you use Page as return type as we need to find out how many elements there are in total to calculate the metadata. Beyond that, be sure you actually equip the PageRequest with sorting information to get stable results. Otherwise you might trigger the query twice and get different results even without the data having changed underneath.


If you are using Java 8 and Spring Data 1.7.0, you can use default methods if you want to combine a @Query annotation with setting maximum results:

public interface UserRepository extends PagingAndSortingRepository<User,Long> {
  @Query("from User u where ...")
  List<User> findAllUsersWhereFoo(@Param("foo") Foo foo, Pageable pageable);

  default List<User> findTop10UsersWhereFoo(Foo foo) {
    return findAllUsersWhereFoo(foo, new PageRequest(0,10));
  }

}

There is a way you can provide the equivalent of "a setMaxResults(n) by annotation" like in the following:

public interface ISomething extends JpaRepository<XYZ, Long>
{
    @Query("FROM XYZ a WHERE a.eventDateTime < :before ORDER BY a.eventDateTime DESC")
    List<XYZ> findXYZRecords(@Param("before") Date before, Pageable pageable);
}

This should do the trick, when a pageable is sent as parameter. For instance to fetch the first 10 records you need to set pageable to this value:

new PageRequest(0, 10)

Use Spring Data Evans (1.7.0 RELEASE)

the new release of Spring Data JPA with another list of modules together called Evans has the feature of using keywords Top20 and First to limit the query result,

so you could now write

List<User> findTop20ByLastname(String lastname, Sort sort);

or

List<User> findTop20ByLastnameOrderByIdDesc(String lastname);

or for a single result

List<User> findFirstByLastnameOrderByIdDesc(String lastname);