[FIXED] How to limit result in @Query used in Spring Data Repository

Issue

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.

Solution

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));
 }

Answered By - Shafin Mahmud
Answer Checked By - Marilyn (JavaFixing Volunteer)