Handling a large recordset

scawa · **Joined:** Sat Feb 21, 2009 12:30 am **Posts:** 7

I have a very large recordset that I need to read through sequentially and perform some process on each entry.

Rather than reading the ENTIRE recordset into memory how do I use Hibernate to read, let's say, one hundred records at a time? I know how to do this with a normal SQL call ( would create a query starts with the ID = 0 and reads one hundred records, saving the highest ID and then once I've processed that info, redo the query for the next 100 records > than the ID).

But How do I do that in Hibernate.... should I use HQL? or what?

Thanks for the help in advance

littypreethkr · **Posted:** Mon Feb 23, 2009 2:39 pm

Yes you can use the HQL. session.createQuery(HQLStr).setMaxResults(100).setFirstResult(0);
This will fetch the first 100 records.

scawa · **Joined:** Sat Feb 21, 2009 12:30 am **Posts:** 7

Thank you very much.

I had been testing a possible solution and was about to post it and reply to my own post when I saw your answer. I modified my code to incorporate your stuff and it worked.

In my solution, I needed to move through all the records in the table (there are over 8000) about 100 at a time for memory purposes. I wound up having to save off the lastId of each group processed and issue another query starting from there. It's like paging through in a JSP.... something I had done tons of times before, but didn't know how to do it in Hibernate. Here's my solution:

Code:

    public void test(){
        Session session = HibernateUtil.currentSession();

        Query query = session.createQuery("from Stocks as stock where stock.id > :idValue");
        Stocks stock = null;
        List stocks = null;
        long lastId = 0;
        query.setLong("idValue", lastId).setMaxResults(20);
        stocks = query.list();
        while(stocks.size() != 0){
            for(int i = 0; i < stocks.size(); i++){
                stock = (Stocks)stocks.get(i);

               // DO ANY PROCESSING ON THE STOCK HERE


                log.debug((i + 1) + "Stock name = " + stock.getSymbol() + " id = " + stock.getId());
                lastId = stock.getId();
            }
            stocks = query.setLong("idValue", lastId).list();
        }


        HibernateUtil.closeSession();
    }

It's not very pretty or object oriented, but I'm in the process of creating a threadsafe Iterator class that gets each next record by paging through the database. It may be overkill, but I'm sort of a purist.

Stephen McConnell

"You are either part of the problem or part of the solution..... or maybe a precipitant or colloidal suspension..."
-- Lamar Stephens