Spring Data JPA Custom Query limit functionality not working

Question

I have created a custom Query

@Repository
public interface SecurityQuestionRepository extends JpaRepository<SecurityQuestion, Integer>{

    @Query("select q from SecurityQuestion q order by q.questionId asc limit 0, 3 ")
    public List<SecurityQuestion> findQuestion();

}

When I call the service and invoked public List<SecurityQuestion> findQuestion() method limit 0,3 is not working

Here is the hibernate log,

Hibernate: select securityqu0_.question_id as question1_4_, securityqu0_.question_desc as question2_4_ from user_security_question securityqu0_ order by securityqu0_.question_id asc

How to made this limit functionality working in this?

Solution 1

Try to pass a method parameter with type Pageable:

@Query("select q from SecurityQuestion q order by q.questionId asc")
public List<SecurityQuestion> findQuestion(Pageable page);

and when you call this function, init page like this:

securityQuestionRepository.findQuestion(new PageRequest(0, 3));

Or just use native query:

@Query(nativeQuery = true,
       value = "select * from user_security_question order by question_id asc limit 0, 3")
public List<SecurityQuestion> findQuestion();

Solution 2

There is a simpler variant:

public interface SecurityQuestionRepository extends JpaRepository<SecurityQuestion, Integer>{

    List<SecurityQuestion> findTop3ByOrderByIdAsc();
}

More info is here.