[FIXED] JPA: what is the proper pattern for iterating over large result sets?

Issue

Let's say I have a table with millions of rows. Using JPA, what's the proper way to iterate over a query against that table, such that I don't have all an in-memory List with millions of objects?

For example, I suspect that the following will blow up if the table is large:

List<Model> models = entityManager().createQuery("from Model m", Model.class).getResultList();

for (Model model : models)
{
     System.out.println(model.getId());
}

Is pagination (looping and manually updating setFirstResult()/setMaxResult()) really the best solution?

Edit: the primary use-case I'm targeting is a kind of batch job. It's fine if it takes a long time to run. There is no web client involved; I just need to "do something" for each row, one (or some small N) at a time. I'm just trying to avoid having them all in memory at the same time.

Solution

Page 537 of Java Persistence with Hibernate gives a solution using ScrollableResults, but alas it's only for Hibernate.

So it seems that using setFirstResult/setMaxResults and manual iteration really is necessary. Here's my solution using JPA:

private List<Model> getAllModelsIterable(int offset, int max)
{
    return entityManager.createQuery("from Model m", Model.class).setFirstResult(offset).setMaxResults(max).getResultList();
}

then, use it like this:

private void iterateAll()
{
    int offset = 0;

    List<Model> models;
    while ((models = Model.getAllModelsIterable(offset, 100)).size() > 0)
    {
        entityManager.getTransaction().begin();
        for (Model model : models)
        {
            log.info("do something with model: " + model.getId());
        }

        entityManager.flush();
        entityManager.clear();
        em.getTransaction().commit();
        offset += models.size();
    }
}

Answered By - George Armhold
Answer Checked By - Senaida (JavaFixing Volunteer)