How to set limit for a hibernate query

Question

How can I set a limit to this hql query? When I add the limit keyword in the query, an error is thrown.

 @Query("from voucher v where  v.voucherType.typeDescription = :typeDescription and v.denomination = :denomination")
 public List<Voucher> findByVoucherTypeAndDenomination(@Param("typeDescription") String typeDescription,@Param("denomination") BigDecimal denomination);

@Query(nativeQuery="true", value=...) – Mihail Kolomiets Sep 16 '20 at 11:00 — Mihail Kolomiets, Sep 16 '20 at 11:00

score 15 · Answer 1 · edited Apr 14 '21 at 18:25

15

When you call your query add the following:

.setFirstResult(firstResult).setMaxResults(limit);

setFirstResult is the (optional) offset, setMaxResults is the limit.

UPDATE

Docs:
http://docs.jboss.org/hibernate/orm/3.6/javadocs/org/hibernate/Query.html#setMaxResults(int)

If you use entityManager, it can be like:

entityManager.createQuery("yourQuery").setFirstResult(0).setMaxResults(5);

edited Apr 14 '21 at 18:25

rogerdpack

62,887
36
269
388

answered Jul 18 '13 at 23:51

Alex

11,451
6
37
52

Please wat does the firstresult in setfirstresult(firstresult) represent – Samuel Anertey Jul 19 '13 at 00:19
@SamuelAnertey it says which result row to start with. In this case, that call is saying "give me up to 5 results, starting with result 0". In the case of MySQL, it would be the equivalent of ending your query with "limit 0,5". – Matt Passell Apr 18 '14 at 13:52

score 3 · Answer 2 · edited Apr 14 '21 at 18:26

3

You can use two method of Query object.

setFirstResult() // offset

setMaxResults() // limit

but you can not use it in HQL because limit is database vendor dependent so hibernate doesn't allow it through hql query

edited Apr 14 '21 at 18:26

rogerdpack

62,887
36
269
388

answered Jun 25 '14 at 11:10

Ankit Katiyar

2,631
2
20
30

score 1 · Answer 3 · edited Aug 06 '16 at 00:19

1

Limit was never a supported clause in HQL. You are meant to use setMaxResults().

edited Aug 06 '16 at 00:19

John Smith

7,243
6
49
61

answered Aug 05 '16 at 04:50

Vu Ruby

56
3

How to set limit for a hibernate query

3 Answers3

Linked