Why i'm getting 'QuerySyntaxException'?

Question

@Query("SELECT new com.ethihas.v1.blog.dto.PostDto(p.title, p.image, p.content, p.location,p.city, p.state, p.country) FROM Post p order by id desc limit 10")
List<PostDto> getAllPosts(@Param("from") Integer from, @Param("to") Integer to);

above is my query and below is constructor of PostDto

    public PostDto(String title, Integer image, String content, String location, String city, String state, String country) {
    this.title = title;
    this.image = image;
    this.content = content;
    this.location = location;
    this.city = city;
    this.state = state;
    this.country = country;
}

everything looks fine only for me but its causing below exception

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: limit near line 1, column 167 [SELECT new com.ethihas.v1.blog.dto.PostDto(p.title, p.image, p.content, p.location,p.city, p.state, p.country) FROM com.ethihas.v1.blog.model.Post p order by id desc limit 10]

[it looks like JPQL doesn't support `LIMIT`](https://stackoverflow.com/questions/20679237/jpql-limit-query). you can use `Pageable` as workaround. — MarcoLucidi, May 30 '20 at 13:37
tried with PageRequest PageRequest(int,int,org.springframework.data.domain.Sort) has protected access in org.springframework.data.domain.PageRequest — SameerShaik, May 30 '20 at 14:19
Not exact solution but can help , check this - https://stackoverflow.com/questions/3479128/limit-number-of-results-in-jpql — Hemant, May 30 '20 at 14:25
Also a solution with Pageable - https://stackoverflow.com/a/36790914/7505731 — Hemant, May 30 '20 at 14:31

score 0 · Answer 1 · answered Jun 03 '20 at 08:35

Limit will not work JPQL, we can use Pageable to get records based on order

    Pageable firstPageWithTwoElements = PageRequest.of(0, 2, Sort.by("id").descending());
    Page<Post> pagedResult = postRepository.findAll(firstPageWithTwoElements);
    List<Post> postList = pagedResult.getContent();

above code will be give recent 2 entries sorted based on ID,

Why i'm getting 'QuerySyntaxException'?

1 Answers1