Omar-Alfred Salah Omar-Alfred Salah - 1 month ago 6
Java Question

What is wrong with this Hibernate paging mechanism?

I made a dynamic batch selector based on the count of rows in the table in order to avoid memory leaks. However, this snippet of code seems to fail to retrieve the last few thousand rows in the database.

Essentially, the code is shown below:

System.out.println("Loading the query...");
Session session = HibernateUtil.getSessionFactory().openSession();

int i = 0;
int batch = 5000; //process in batches if table is too big
Long rowCount = (Long) session.createQuery("select count(*) " + cacheDetail.getQuery()).list().get(0);

System.out.println("Starting execution on " + cacheDetail.getQuery());
if (rowCount > batch) {
List<Object> list = session.createQuery(cacheDetail.getQuery())
.setFirstResult(i).setMaxResults(batch).list();
while(list.size() == batch || list.size() < batch){
i+=batch;
if (list.size() < batch) {
list = session.createQuery(cacheDetail.getQuery())
.setFirstResult(i).setMaxResults(list.size()).list();
} else {
list = session.createQuery(cacheDetail.getQuery())
.setFirstResult(i).setMaxResults(batch).list();
}
//Do some computation, close session
}
}


For clarification, cacheDetail.getQuery() is just a HQL select statement: "FROM tableX".

So where in the code am I going wrong?

Answer

Thanks for the input javaguy and ST-DDT. In response to your comments, I am loading hibernate objects into a in-memory distributed map. I combined your solutions and tailored it to something like this (using rowCount itself, which should not make it prone to DB modifications). Seems like it works fine, also would like your inputs:

int i = 0;
int batch = 5000; //process in batches if table is too big

Long rowCount = (Long) session.createQuery("select count(*) " + cacheDetail.getQuery()).list().get(0);

System.out.println("Starting execution on " + cacheDetail.getQuery());
if (rowCount > batch) { //do batch processing
    List<Object> list = session.createQuery(cacheDetail.getQuery())
            .setFirstResult(i).setMaxResults(batch).list();
    //Load objects from list to a map   

    while(list.size() == batch){
        i += batch;
        rowCount -= batch;
        list = session.createQuery(cacheDetail.getQuery())
                .setFirstResult(i).setMaxResults(batch).list();
    //Load objects from list to a map

    }
    if (rowCount < batch){
        System.out.println("Loading the last " + rowCount + " records.");
        list = session.createQuery(cacheDetail.getQuery())
                .setFirstResult(i).setMaxResults((int)(long)rowCount).list(); 
   //load objects from list to map
    }

    list = null;
    session.flush();
    session.clear();
}
 //Else, use a non paging mechanism to select from DB (trivial)

Though I'm really not keen on using the Total list you posted, as I do not want a duplicate and would rather process it the way ST-DDT said.