Jpa: What Is the Proper Pattern for Iterating Over Large Result Sets

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

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();
    }
}

How to iterate over large number of records in MySQL with memory efficient manner in Spring Boot

Don't use findAll if there is a lot of entities.

If you want to use pagination you can do something like this:

    Pageable pageRequest = PageRequest.of(0, 200);
    Page<Qmail> onePage = repository.findAll(pageRequest);

    while (!onePage.isEmpty()) {
        pageRequest = pageRequest.next();

        //DO SOMETHING WITH ENTITIES
        onePage.forEach(entity -> System.out.println(entity.getId()));

        onePage = repository.findAll(pageRequest);
    }

Modifying a large set of objects using JPA / EclipseLink

Use pagination + entityManager.clear() after each page. Also execute every page in a single transaction OR you will have to create/get a new EntityManager after an exception occurs (ar least with Hibernate: the EntityManager instance could be in an inconsistent state after an exception).

Iterate JPA query result

No, there is not such a construct in JPA. Also not in JPA 2.1.

JPA 2.2 provides TypedQuery.getResultStream(), but default implementation does not have expected effect (calls getResultList). Also specific implementations do not always lead to performance improvements, as can be seen in this article.

How to stream results from Hibernate / JPA and release resources as processed?

You can use setFirstResult and setMaxResults to iterate over a large resultset. You can find an example in this related question.

Another issue which comes to my mind is that you might have eager fetch set by default. This means you might be getting all related entities and if they have related entities they will be fetched as well. You should switch on the sql statements in your log file to check if this is happening.

--- EDIT to answer the comment

If the objects are not being referenced in java, then you can clear the first level cache with flush and clear (in entitymanager). That should clear all the loaded objects.

Getting records by groups in JPA

You should set the first result and the number of results in your javax.persistence.Query with methods setFirstResult() and setMaxResults().

What is the idiomatic way to iterate over a result set without loading all results at once using Hibernate?

You can use setMaxResults() and setFirstResult() methods from SQLQuery class. They allow you to emulate both offset and limit SQL clauses.

If you wish to load results from rownum = 0 to rownum = 50, for instance, you would use setFirstResult(0) and setMaxResults(50) and so on.

Source: Hibernate Javadocs

How to map the result set of a JPA NativeQuery to a POJO using SqlResultSetMapping

@SqlResultSetMapping annotation should not be put on a POJO. Put it at (any) @Entity class. "Unknown SqlResultSetMapping [foo]" tells you, that JPA provider doesn't see any mapping under name 'foo'. Please see another answer of mine for the correct example

• JPA- Joining two tables in non-entity class

---

Related Topics