CODEWITHSUNDEEP
javaspringspring-data-jpa
membersound
membersound

Reputation: 86747

How to use LIMIT in spring within sql query?

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

Upvotes: 35

Views: 39708

Answers (3)

Sofiane
Sofiane

Reputation: 693

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)

Upvotes: 27

Augusto Peres
Augusto Peres

Reputation: 285

An alternative is to use HQL instead of the Spring-data-jpa and then use setMaxResults().

For example:

SessionFactory sessionFactory = new Configuration().configure("hibernate.cfg.xml").buildSessionFactory();
Session session = sessionFactory.openSession();
session.beginTransaction();

Query query = session.createQuery("FROM Person WHERE company.id = :id ORDER BY name DESC");
query.setParameter("id", id);
query.setMaxResults(3); // this replaces the LIMIT in the query
List<Person> personList = query.getResultList();

session.getTransaction().commit();
session.close();

Upvotes: 2

Oliver Drotbohm
Oliver Drotbohm

Reputation: 83081

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

Upvotes: 58

Related Questions