Menu
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?
380
Conclusion. Limiting query results in JPA is slightly different to SQL; we don't include the limit keyword directly into our JPQL. Instead, we just make a single method call to Query#maxResults, or include the keyword first or top in our Spring Data JPA method name. As always, the code is available over on GitHub.
Description. The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value.
Let's see it step by step: Create an instance of Session from the SessionFactory object. Create an instance of CriteriaBuilder by calling the getCriteriaBuilder() method. Create an instance of CriteriaQuery by calling the CriteriaBuilder createQuery() method.
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.
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
