JPQL Query Annotation with Limit and Offset

Question

I have a repository interface with some abstract methods where I use the @Query annotation. Now I would like to add limit and offset support to this queries.

example:

public interface ProductRepository
   extends CrudRepository<Product, Long> {

    @Query("from Product")
    List<Product> findAllProducts();
}

something like this would be nice

public interface ProductRepository
   extends CrudRepository<Product, Long> {

    @Query("from Product limit :limit ")
    List<Product> findAllProducts(@Param("limit") Integer limit);
}

But this doesn't work. There is a solution that I create an implementation of the interface (http://stackoverflow.com/questions/3479128/jpql-limit-number-of-results) But I wonder if there is not a possibility of adding offset and limit to the query or if there is an annotation for this.

Why don't you simply implement the method and use the JPA API? Annotations are cool, but they're not supposed to replace all your Java code. — JB Nizet, Feb 24 '12 at 14:16
It is the standard way to use and extend Spring's repository interfaces. — Hauke Ingmar Schmidt, Feb 24 '12 at 14:36
Writing this... the standard way to solve the fundamental problem is to use `PagingAndSortingRepository`. — Hauke Ingmar Schmidt, Feb 24 '12 at 14:38

score 11 · Answer 1 · edited Oct 08 '20 at 15:01

+1 to what user "his" said in the comment:

"the standard way to solve the fundamental problem is to use PagingAndSortingRepository"

Here's an example. I'm throwing in sorting just as an added bonus:

public interface ArtifactRepo extends JpaRepository<Artifact, Long> {
    Page<Artifact> findByComponentKey(String componentKey, Pageable pageable);
}

(You can use @Query above if you like, but JPQL doesn't itself support limits, as "his" noted.)

Then when calling it, use

PageRequest pageRequest =
    new PageRequest(0, 1, Sort.Direction.DESC, "buildNumber");
Page<Artifact> artifactsPage =
    artifactRepo.findByComponentKey(componentKey, pageRequest);

You can also check out this blogpost I wrote:

http://springinpractice.com/blog/categories/chapter-02-data/

score 8 · Accepted Answer · answered Feb 24 '12 at 14:15

8

limit is not supported by JPQL. Even without it your queries are not valid JPQL queries (but may be valid HQL - and may work if your JPA provider is tolerant).

A (partial) implementation is needed so you can use the Query interface or the criteria api.

answered Feb 24 '12 at 14:15

Hauke Ingmar Schmidt

11,559
1
42
50

JPQL Query Annotation with Limit and Offset

2 Answers2

Linked