How to perform limit query in HQL when table size is not known?

Question

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?

score 1 · Answer 1 · answered Sep 28 '12 at 16:22

1

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.

answered Sep 28 '12 at 16:22

Mukul Goel

8,387
6
37
77

score 1 · Answer 2 · answered Sep 28 '12 at 16:40

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);
  page++;
} while(currentPage.size() == pageSize);

Yogendra Singh · Answer 3 · 2012-09-28T18:44:22.087

0

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!!

edited Sep 28 '12 at 18:44

answered Sep 28 '12 at 16:29

Yogendra Singh

33,927
6
63
73

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

How to perform limit query in HQL when table size is not known?

3 Answers3