'How to limit result in @Query used in Spring Data Repository
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 ?
Solution 1:[1]
You can provide limitations by limit
statement 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));
}
Solution 2:[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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | |
Solution 2 | DevThiman |