JPA 2 CriteriaQuery, using a limit

Question

I am using JPA 2. For safety reasons, I am working type safe with CriteriaQuery's (and thus, I am not searching for any solutions to typed queries and so on).

I recently came across an issue in which I needed to set a SQL-LIMIT.

After a lot of searching, I was still not successful in finding a solution.

CriteriaQuery<Product> query = getEntityManager().getCriteriaBuilder().createQuery(Product.class);
Root<Product> product = query.from(Product.class);
query.select(product);

return em.createQuery(query).getResultList();

Can anyone help me?

Answer 1

Define limit and offset on the Query:

return em.createQuery(query)
         .setFirstResult(offset) // offset
         .setMaxResults(limit) // limit
         .getResultList();

From the documentation:

TypedQuery setFirstResult(int startPosition)

Set the position of the first result to retrieve. Parameters: startPosition - position of the first result, numbered from 0

TypedQuery setMaxResults(int maxResult)

Set the maximum number of results to retrieve.

Answer 2

For the sake of completeness, I want to answer the initial question with regards to the JPA Criteria API.

First of all, you might clarify for yourself beforehand when to use JPQL and when to use the Criteria API depending on the use case. There is a nice article on this at the ObjectDB documentation website which states:

A major advantage of using the criteria API is that errors can be detected earlier, during compilation rather than at runtime. On the other hand, for many developers string based JPQL queries, which are very similar to SQL queries, are easier to use and understand.

I recommend this article in general because it describes concisely how to use the JPA Criteria API. There is a similar article about the Query API.

Back to the question:

A CriteriaQuery offers a set of restrictions that are accessible - for instance - by using the where() method. As you might intuitively guess: you cannot limit the query to a particular number of results with such a restriction - except you have a trivial case like limiting on a unique identifier (which would make the usage of the Criteria API obsolete). Simply explained: a limit is not a criterion and therefore not covered by that api. See also the old but gold Java EE docs for more details.

Solution

However, you can of course use your CriteriaQuery object as a foundation for a JPQL query. So first, you create your CriteriaQuery as is:

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

Then use the JPA Query constructor for CriteriaQuery objects:

Query limitedCriteriaQuery = getEntityManager().createQuery(criteriaQuery)
     .setMaxResults(resultLimit); // this is the important part     
return limitedCriteriaQuery.getResultList();

And that is basically how you should use both APIs according to the documentation and the provided articles.