JPA 2 CriteriaQuery, using a limit

java sql jpa persistence limit
Menno picture Menno · Jul 25, 2012 · Viewed 81.6k times · Source

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

Francisco Spaeth picture Francisco Spaeth · Jul 25, 2012

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.

Related questions

How to call a stored procedure from Java and JPA

I am writing a simple web application to call a stored procedure and retrieve some data. Its a very simple application, which interacts with client's database. We pass employee id and company id and the stored procedure will return employee …

Read More
JPA Native Query select and cast object

I have got an Object Admin which extends User. By default both Objects are in the table User_ of my Derby Database (included fields from Admin). Normally I'd select an User like this: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<User&…

Read More
JPQL SELECT between date statement

I would like to convert this SQL statement to a JPQL equivalent. SELECT * FROM events WHERE events_date BETWEEN '2011-01-01' AND '2011-03-31'; This correctly retrieves the information from the table events. In my Events …

Read More