I'm working with PostgreSQL query implementing in JPQL.
This is a sample native psql query which works fine,
SELECT * FROM students ORDER BY id DESC LIMIT 1;
The same query in JPQL doesnt work,
@Query("SELECT s FROM Students s ORDER BY s.id DESC LIMIT 1") Students getLastStudentDetails();
seems like LIMIT clause doesn't work in JPQL.
According to JPA documentation we can use setMaxResults/setFirstResult
, Can anyone tell me how can I use that in my above query?
JPQL does not provide a mechanism to limit queries. This is most often achieved by using the setMaxResults() method on the Query . If you must avoid specifying this in Java code, you could make a view in the database that contains your query and performs the limit.
JPQL doesn't support the LIMIT keyword.
Spring Data JPA supports keywords 'first' or 'top' to limit the query results (e.g. findTopBy....). An optional numeric value can be appended after 'top' or 'first' to limit the maximum number of results to be returned (e.g. findTop3By....). If this number is not used then only one entity is returned.
JPQL can retrieve information or data using SELECT clause, can do bulk updates using UPDATE clause and DELETE clause.
You are using JPQL which doesn't support limiting results like this. When using native JPQL you should use setMaxResults
to limit the results.
However you are using Spring Data JPA which basically makes it pretty easy to do. See here in the reference guide on how to limit results based on a query. In your case the following, find method would do exactly what you want.
findFirstByOrderById();
You could also use a Pageable
argument with your query instead of a LIMIT
clause.
@Query("SELECT s FROM Students s ORDER BY s.id DESC") List<Students> getLastStudentDetails(Pageable pageable);
Then in your calling code do something like this (as explained here in the reference guide).
getLastStudentDetails(PageRequest.of(0,1));
Both should yield the same result, without needing to resort to plain SQL.
As stated in the comments, JPQL does not support the LIMIT
keyword.
You can achieve that using the setMaxResults
but if what you want is just a single item, then use the getSingleResult
- it throws an exception if no item is found.
So, your query would be something like:
TypedQuery<Student> query = entityManager.createQuery("SELECT s FROM Students s ORDER BY s.id DESC", Student.class); query.setMaxResults(1);
If you want to set a specific start offset, use query.setFirstResult(initPosition)
; too
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