Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 ?

Please help me doing this. output img

like image 807
Nipun Vidarshana Avatar asked Dec 03 '17 08:12

Nipun Vidarshana


People also ask

How do I limit records in JPA repository?

JPA Setup In our repository method, we use the EntityManager to create a Query on which we call the setMaxResults() method. This call to Query#setMaxResults will eventually result in the limit statement appended to the generated SQL: select passenger0_.id as id1_15_, passenger0_.

Can we use limit in JPQL?

JPQL does not provide a mechanism to limit queries. This is most often achieved by using the setMaxResults() method on the Query . If you must avoid specifying this in Java code, you could make a view in the database that contains your query and performs the limit.

What is @query used for spring data?

Spring Data JPA @Query 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 set the fetch size in JPA?

hibernate. fetchSize", "100"); Or when using Spring Data JPA use a @QueryHints annotation on the interface method. Can be applied to both methods with and without @Query .


2 Answers

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));
 }
like image 110
Shafin Mahmud Avatar answered Nov 01 '22 12:11

Shafin Mahmud


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.

like image 38
DevThiman Avatar answered Nov 01 '22 13:11

DevThiman