How to limit result in @Query used in Spring Data Repository

Question

I am retrieving data by CrudRepository in Spring Data JPA. I want to filter my records those are retrieved from my custom query provided in @Query annotation. I tried .setMaxResults(20); for select rows.. But it gives errors. I want to filter first 20 rows from my table

this is my repository

package lk.slsi.repository;

import java.util.Date;
import lk.slsi.domain.SLSNotification;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;

/**
 * Created by ignotus on 2/10/2017.
 */
public interface SLSNotificationRepository extends CrudRepository<SLSNotification, Integer> {


    @Override
    SLSNotification save(SLSNotification slsNotification);

    @Override
    SLSNotification findOne(Integer snumber);

    @Override
    long count();

    @Override
    void delete(Integer integer);

    @Override
    void delete(SLSNotification slsNotification);

    @Override
    void delete(Iterable<? extends SLSNotification> iterable);

    @Override
    List<SLSNotification> findAll();

    @Query("select a from SLSNotification a where a.slsiUnit in :unitList order by snumber desc")
    List<SLSNotification> getApplicationsByUnit(@Param("unitList") List<String> unitList);

    @Query("select a from SLSNotification a where a.userId = :userId")
    List<SLSNotification> getApplicationsByUserId(@Param("userId") String userId);

    @Query("select a.snumber, a.date, a.slsNo, a.slsiUnit, a.productDesc, a.status from SLSNotification a where a.userId = :userId ORDER BY snumber desc")
    List<SLSNotification> getApplicationsByUserIdforManage(@Param("userId") String userId);

    @Query("select a from SLSNotification a where a.slsNo = :slsNo")
    SLSNotification getApplicationBySLSNumber(@Param("slsNo") String slsNo);

}

I want my List<SLSNotification> getApplicationsByUserIdforManage(@Param("userId") String userId); method to retrieve a limited set of data. How can I call entity manager or something or anything to do this ?

Please help me doing this.

Answer 1

You can provide limitations by limitstatement in your SQL. And have nativeQuery = true in @Query annotation to set JPA provider(like Hibernate) to consider this as a native SQL query.

@Query(nativeQuery = true, value = "SELECT * FROM SLSNotification s WHERE s.userId = :userId ORDER BY snumber DESC LIMIT 20")
List<SLSNotification> getUserIdforManage(@Param("userId") String userId);

Or

Additionally if you want to exploit the handy features from Spring Data JPA, you can do it by proper method naming

List<SLSNotification> findByUserIdOrderBySNumber(@Param("userId") String userId, Pageable pageable);

If you dont know already, Spring Data JPA constructs Query from the method names. Amazing, right? Read this documentation for better understanding.

Now just call this method like

Pageable topTwenty = PageRequest.of(0, 20);
List<SLSNotification> notifications = repository.findByUserIdOrderBySNumber("101", topTwenty);

Besides, If you are using Java 8

You have option for having default method in interface and make life a bit easier

 List<SLSNotification> findByUserIdOrderBySNumber(@Param("userId") String userId, Pageable pageable);

 default List<User> findTop20ByUserIdOrderBySNumber(String userId) {
    return findByUserIdOrderBySNumber(userId, PageRequest.of(0,20));
 }

Answer 2

As per the feature provided by Spring Data JPA to create queries via method names you can use this. For more Supported query method predicate keywords and modifiers check this.

Optional<List<SLSNotification>> findTop20ByUserId(String id); 

or

Optional<List<SLSNotification>> findFirst20ByUserId(String id); 

This will limit the query results to the first of results.

If you want to limit the ordered result set then use Desc, Asc with OrderBy in the method names as below.

// return the first 20 records which are ordered by SNumber. If you want Asc order, no need to add that keyword since it is the default of `OrderBy` 
Optional<List<SLSNotification>> findFirst20ByUserIdOrderBySNumberDesc(String id);  

Suggestion -- It is better and safe from code side to use return type of the Optional in queries like find, get where there is doubt of having at least single result.

Answer 3

I found that LIMIT can only be used with the nativeQuery which can be used in some cases.

Ideally the query method name can be used like:

EntityA findTopByParamXAndParamYAndParamZOrderByIdDesc();

and it will restrict the resut to one entity (one record)

In my case as I have a inner join, I cannot really use the method naming and I would also avoid to use the Paging as it delegate the logic to the caller.

I found this quite elegant solution using the MAX(a) in the query:

@Query(value = "SELECT MAX(a) FROM EntityA a " +
        "INNER JOIN EntityB b on a.Bid = b.id " +
        "WHERE b.paramY = :paramY " +
        "AND b.paramZ = :paramZ " +
        "AND a.paramX = :paramX " +
        "ORDER BY a.id DESC")
EntityA findLastEntityAByParamsXYZ(
        @Param("paramX") String paramX,
        @Param("paramY") String paramY,
        @Param("paramZ") String paramZ);

Answer 4

If you do not want to define the limit hard coded in the repository method name, you could use Page e Pageable in the service that calls the repository:

Page<Passenger> page = repository.findAll(
  PageRequest.of(0, 20, Sort.by(Sort.Direction.ASC, "seatNumber")));

More examples at: https://www.baeldung.com/jpa-limit-query-results

Answer 5

public interface ICategoriaDao extends CrudRepository<Categoria, Long>{

    @Query(value="select * from categoria c order by nombre limit ?1 offset ?2", nativeQuery=true)
    public List<Categoria> findPagina(Integer limit,  Integer offset );
}