How to limit the number of records in a Query with JPA ?

This article will teach you how to limit the number of records that your Hibernate/JPA Application returns from a Resultset of data.

Fetching a large result set can be a challenge for your User Interface and inefficient for your Back end systems. There are two main approaches to solve this issue:

• use Native SQL
• use the setMaxResults method

Using Native SQL to control the max number of records

Hibernate and JPA applications are able to use native SQL statements. Therefore, you can use the SQL SELECT LIMIT statement to limit the number of records from a resultset.

Firstly, here is the syntax for the SELECT LIMIT expression:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows [ OFFSET offset_value ];

Therefore, you can place the SELECT LIMIT expression with the createNativeQuery. For example:

String sql ="SELECT contact_id, last_name, first_name FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id DESC LIMIT 5;"
Query q = em.createNativeQuery(sql);
List<Object[]> contacts = q.getResultList();

Use setMaxResult

Both the Query and TypedQuery interfaces provide support for pagination via the setFirstResult() and setMaxResults() methods.

• setFirstResult: specifies the first result to be received (numbered from zero)
• setMaxResults: specifies the maximum number of results to return relative to that point.

Let’s see an example:

@PersistenceContext 
private EntityManager em;

public List getCurrentResults() {

        return em.createQuery("select a from Activity a").setMaxResults(10).getResultList();
 

}

The above example returns the first 10 records based on the Query order. On the other hand, if you want to return a limited pagesize starting from an offset, then you can combine setFirstResult with setMaxResult:

public List getCurrentResults() {
    return em.createQuery("select a from Activity a")
            .setFirstResult(currentPage * pageSize)
            .setMaxResults(pageSize)
            .getResultList();
}

Be careful! You should not use the setFirstResult() and setMaxResults() methods with queries that join across collection relationships. For example with OneToMany or Many-To-Many relationships. If you do that, these queries may return duplicate values!

Use setMaxResults in Criteria Queries

A CriteriaQuery offers a set of restrictions that you can apply by using the where() method. To learn more about CriterieQuery check this article: How to use JPA Criteria API

On the other hand, you cannot limit the query to a particular number of results with such a restriction simply because a limit is not a Criterion. Therefore it’s not available in this API.

To circumvent this issue, you can however use your CriteriaQuery object as a foundation for a JPQL query. For example, start by creating your CriteriaQuery as follows:

CriteriaQuery<Article> criteriaQuery = 
    getEntityManager().getCriteriaBuilder().createQuery(Article.class);
Root<Article> article = criteriaQuery.from(Article.class);
criteriaQuery.select(article);

Then, build your JPA Query using the CriteriaQuery object in its constructor:

Query queryWithLimit = getEntityManager().createQuery(criteriaQuery)
     .setMaxResults(10);  
return queryWithLimit.getResultList();

Conclusion

This article discussed how to limit the number of records from JPA applications using different approaches. the native SQL approach and the JPA built-in setMaxResult method.