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?

JPQL does not support "LIMIT", as any basic JPA documentation would tell you — Neil Stockton, May 24 '17 at 08:56
As `limit` key word is not supported by few database vendors, mainly Oracle, I guess this is the reason it is not included in `JPQL`. Use `Pageable`, it can be easily converted to `ArrayList`...:) — harshavmb, May 25 '17 at 07:09

score 13 · Accepted Answer · answered May 24 '17 at 08:54

13

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

answered May 24 '17 at 08:54

Blank

12,308
1
14
32

@Query(nativeQuery = true, value = "select * from user_security_question order by question_id asc limit 0, 3") public List findQuestion(); this is working fine – May 24 '17 at 09:42
1

This is by far the best answer I have seen on this topic. Native query is always the least preferred option as it defeats the purpose of JPA. Consider that the “limit” functionality differs signlificantly between databases (postgres vs oracle for example) – Jan Zyka Jul 24 '18 at 07:46
Assuming you are using Java 8, you can add a `default List findQuestion() { return findQuestion(new PageRequest(0, 3)); }` so that the caller does not need to specify the `Pageable` argument. – Julien Kronegg Jul 16 '19 at 13:49

score 6 · Answer 2 · answered May 24 '17 at 11:34

6

There is a simpler variant:

public interface SecurityQuestionRepository extends JpaRepository<SecurityQuestion, Integer>{

    List<SecurityQuestion> findTop3ByOrderByIdAsc();
}

More info is here.

answered May 24 '17 at 11:34

Cepr0

28,144
8
75
101

1

This should be the top answer. No need to set up pageable results. Simple – thonnor Jul 16 '21 at 13:59

score 0 · Answer 3 · answered Jul 31 '17 at 05:56

0

Add a parameter nativeQuery = true, value = "your query" inside @Query annotation.

answered Jul 31 '17 at 05:56

Nithyananth

329
2
5
17

Spring Data JPA Custom Query limit functionality not working

3 Answers3