Take the 2-minute tour ×

Possible Duplicate:
How do you do a limit query in HQL

I have a table A of large yet unknown size. I need to retrieve results in batches, i.e. 100 at a time, from the table and perform operations on them. For this, I have a variable m so that I can execute the HQL equivalent of query "SELECT * from A LIMIT m,100" within a loop, which iterates m as 1, 101, 201, ... and so on. What shall be the terminating condition of the loop? What is the result when the above query is executed in a condition when m > number of rows in the table?

share|improve this question

marked as duplicate by Conrad Frix, Nambari, tereško, martin clayton, Eitan T Sep 28 '12 at 21:55


3 Answers 3

use select count(*) query first , this will give you total number of records. now you write java code to set limit accordingly. (count/m) int = x= number of full batches and count-x*m = rows for last batch.

share|improve this answer

The issue you have is more known as pagination.

And your problem is how many pages you can retrieve form a table ?

You must know the pages count to use pagination.

To set page count, you need to execute pre-query that will have same FROM clause but only SELECT count(*) FROM .... This will give you always correct answer.

If you can not or do not want to use extra statement. Then you should as database for pages until the result is not empty. So you execute statement for page 1, paget 2, ... page n until size of result is less then page size.

int pageSize = 100;
int page     = 1;  
do {     
  currentPage = loadPage(page, pageSize);
} while(currentPage.size() == pageSize); 
share|improve this answer

I suggest to use hibernate functions setFirstResult() and setMaxResults() to achieve your desired results e.g. below:

    Query query= session.createQuery("SELECT * from A");
    int resultSize = 100;
    List<?> pagedResults = null;
    //use any approriate number for iterations or do a result count first to get the expected result size
    for(int m=0; ; m++){ 
        pagedResults = (List<?>)query.setFirstResult(m*resultSize).setMaxResults((m+1)*resultSize).list();
        //do your processing
        if(pagedResults.isEmpty() || pagedResults.size() <resultSize){
            break; //no further iterations required

setMaxResults() restricts the result size only. If there are less records available, you will receive those record only in the result. Added on "if" condition to avoid any unnecessary iterations.

Hope this helps!!

share|improve this answer
Your code do not work if your dabase have more then 1000 pages. If you can not determine the end point try to find a condition for while loop. The condition for while is already in your for loop. –  Damian Leszczyński - Vash Sep 28 '12 at 16:43
Removed the comparison statement in for loop to make it open ended. Termination conditions is already there. Hope this helps in your scenario! –  Yogendra Singh Sep 28 '12 at 18:45
