How to use the LIMIT parameter in HQL

If you have used MySQL, at least with phpmyadmin(like me :D), then you will be familiar with the LIMIT keyword used in the SQL queries. The “LIMIT” keyword tells the query processor about the minimum number of records to be fetched for a particular query condition.  If I wanted just the first 10 values from my list of mobile phones, then I would type the following query in the console “select * from mobilephones LIMIT 0,10”. This would bring 10 rows from the top of the table.

How to use LIMIT parameter in HQL?

Actually in HQL, there is no parameter or keyword called “LIMIT”. This link to the basic syntax of HQL reveals that there is no keyword called LIMIT. The LIMIT constraint is successfullly imposed by calling a function of the Query class.


//First Initialise the query object with the call to the method containing the query string.

Query query = em.createNamedQuery(“mobilephone.details”); // em is of type Entity Manager

//Now invoke the setMaxResults function on this query object. This is what it would look like:-

query.setMaxResults(10); //it takes only an integer parameter

 Thats it. So that is how you limit the number of records returned by a query in HQL.

I understood this by reading this Link –

About vatsalad

Hi, I'm Vatsala
This entry was posted in hibernate, How To... and tagged , , , , . Bookmark the permalink.

6 Responses to How to use the LIMIT parameter in HQL

  1. utpal says:

    I want how to get distinct records in hql and setMaxResults() is set to return max should return the no of records as set in setMaxResults(). kindly give some examples.

    thanx in advance

    • vatsalad says:

      HQL allows us(programmers) to use the distinct keyword just like its done in normal SQL. So we could say

      “select distinct f from Foo f”

      and Hibernate would fetch distinct (non repeating) records from the data base.
      So the answer to your question would be – construct a query which will contain the distinct keyword and then use the setMaxResults() function on that query object.
      A code snippet for this would look like

      Query query = em.createQuery(“select distinct f from Foo f”);
      List resultData = query.getResultList();

  2. Ivano says:

    ow… thank u.. very helpfull for me :D

  3. Usil says:

    Try to use setMaxResults on query which has subqueries… It won’t work, i.e.: select … from (select …). The sql query sent to DB won’t have limit (or rownum etc) attached.
    Do you know any solution?

    • vatsalad says:

      @Usil Very interesting observation you have made. let me check this. meanwhile, do let me know if you find a work around.

  4. Soorej says:

    Thanks u :)

Comments are closed.