Menu
Somehow I cannot use the LIMIT qualifier within a sql query using Spring-data-jpa:
@Query("SELECT p from Person p WHERE p.company.id = :id ORDER BY p.name DESC LIMIT 3") What is wrong here?
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: Limit near line 1, column 146 at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91) at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328) ... 48 more 
226
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.
hibernate. fetchSize", "100"); Or when using Spring Data JPA use a @QueryHints annotation on the interface method. Can be applied to both methods with and without @Query .
PagingAndSortingRepository is an extension of CrudRepository to provide additional methods to retrieve entities using pagination and sorting.
The simplest way to implement pagination is to use the Java Query Language – create a query and configure it via setMaxResults and setFirstResult: Query query = entityManager. createQuery("From Foo"); int pageNumber = 1; int pageSize = 10; query. setFirstResult((pageNumber-1) * pageSize); query.
LIMIT is not part of JPQL. The mechanism available in current release version (1.6.0.RELEASE as of the time of writing) is pagination:
interface PersonRepository extends Repository<Person, Long> { @Query("...") List<Person> findLimited(..., Pageable pageable); } This can then be used as follows:
repository.findLimited(..., new PageRequest(0, 10)); This will return the first ten results of the query defined in the @Query annotation.
The current master branch of Spring Data JPA already contains a new feature that would allow you to rewrite above query as follows:
interface PersonRepository extends Repository<Person, Long> { List<Person> findTop3ByCompanyOrderByName(Company company); } As of version 1.7.0.M1 (feature already available in snapshots) the query derivation mechanism will understand Top and First in the subject clause To limit the number of results returned.
Update as new PageRequest deprecated you need to usePageRequest.of(0, 10) instead
78
My response is perhaps very late, but if I can help anyone else, you can use the nativeQuery like this :
@Query(value="SELECT * from person p WHERE p.company_id = :id ORDER BY p.name DESC LIMIT 3", nativeQuery = true) 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
