All geek questions in one place

How to use LIMIT in spring in sql query?

Somehow, I cannot use the LIMIT qualifier in 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
+6
source share
1 answer

LIMIT not part of JPQL. The mechanism available in the current version (1.6.0.RELEASE at 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 query results defined in the @Query annotation.

The current main branch of Spring Data JPA already contains a new function that will allow you to rewrite the above query as follows:

 interface PersonRepository extends Repository<Person, Long> { List<Person> findTop3ByCompanyOrderByName(Company company); }

Starting with version 1.7.0.M1 (the function is already available in snapshots ), the request definition mechanism will understand Top and First in the subject section. To limit the number of returned results.

+19
source

Source: https://habr.com/ru/post/971239/

More articles:


All Articles