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.

Example:-

//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 – https://forum.hibernate.org/viewtopic.php?p=2243712&sid=9a97d9c78515e5a9e5df17d73ded0d74

About these ads

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 result.it should return the no of records as set in setMaxResults(). kindly give some examples.

    thanx in advance
    Utpal

    • 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”);
      query.setMaxResults(10);
      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 :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s