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