Pagination in EclipseLink: Shifting Filtering from JVM to Database

The Memory Wall: Large Result Sets in EclipseLink

When working with EclipseLink and JPA, retrieving an entire dataset into the JVM heap can quickly lead to severe Garbage Collection pressure or an OutOfMemoryError. This is especially common when developer code implicitly fetches all rows, assuming the dataset remains small. As tables grow, the application faces performance collapse.

EclipseLink Query Pagination API

EclipseLink, like other JPA providers, provides mechanisms to offload data slicing to the database. The setFirstResult() and setMaxResults() methods on an EclipseLink Query object translate into database-specific LIMIT and OFFSET clauses, reducing the memory footprint of each request.

How setFirstResult and setMaxResults Work

• setFirstResult(int start): Specifies the zero-based offset. The database skips this many rows before returning results.
• setMaxResults(int limit): Restricts the maximum number of rows returned in a single query execution.

When these are set, EclipseLink generates SQL appropriate for the underlying database. For example, PostgreSQL and MySQL produce SELECT ... LIMIT ? OFFSET ?, while Oracle may use ROWNUM filtering or analytic functions depending on the version.

Worked Example: Paginated User Retrieval

// Example runs within a service method, using EclipseLink Session or EntityManager
public List getUsersPaginated(int pageNumber, int pageSize) {
    String jpql = "SELECT u FROM User u ORDER BY u.id ASC";
    Query query = session.createQuery(jpql, User.class);
    
    // Calculate offset: page 0 → offset 0, page 1 → offset pageSize, etc.
    int offset = pageNumber * pageSize;
    query.setFirstResult(offset);
    query.setMaxResults(pageSize);
    
    return query.getResultList();
}

Verification: Confirm Database-Level Filtering

Enable EclipseLink SQL logging by adding to persistence.xml or application.properties:

<property name="eclipselink.logging.level" value="FINE"/>
<property name="eclipselink.logging.parameters" value="true"/>

Run the method and inspect the log. Look for a LIMIT and OFFSET clause in the generated SQL. If the query returns all rows without a limit clause, EclipseLink is filtering in memory, which defeats the purpose.

Trade-Off: Deep Pagination and Keyset Alternative
As the offset value increases, the database must scan and discard more rows, degrading performance. For pages far into a large result set, consider keyset pagination (also called "seek-based pagination") using a WHERE id > :lastId clause. This approach indexes on the primary key and maintains consistent query speed regardless of page depth.

Limitations and Practical Checklist

  
Avoid JOIN FETCH on collections when paginating; EclipseLink may apply pagination in memory, similar to the Hibernate risk.
  
Verify SQL logs contain LIMIT/OFFSET to confirm database-level slicing.
  
Monitor JVM heap with Eclipse MAT or JConsole when comparing full-list versus paginated fetches.
  
Use keyset pagination for deep offsets to avoid scan-time degradation.