[SOLVED] Sorting a custom JPA query with pageable

Sorting a custom JPA query with pageable

So, I've already done this using the standard Spring Data JPA interface which extends PagingAndSortingRepository in order to achieve pagination and sorting for a REST API. The thing is, now I want to achieve the very same thing but now using just vanilla JPA and so far so good I managed to get my API to paginate but the sorting doesn't work at all. Every time I try to set the parameter (from a pageable object using pageable.getSort()) it ends with a query error (either if I just send a string as parameter like "name" or just send the sort object, it shows errors).

Here's some code:

My repo implementation:

    @Override
public List<Project> findByAll(Pageable pageable) {
    Query query = em.createQuery("SELECT project FROM Project project ORDER BY :sort");
    query.setParameter("sort", pageable.getSort());
    query.setMaxResults(pageable.getPageSize());
    query.setFirstResult(pageable.getPageSize() * pageable.getPageNumber());
    return query.getResultList();
}

My service:

@Override
public Page<Project> findAll(Pageable pageable) {
    objects = Lists.newArrayList(repository.findByAll(pageable));
    PageImpl<Project> pages= new PageImpl<Project>(objects, pageable, repository.count());
    return pages;
}

To be clear, I'm filling the Pageable object via URI and from the console I can say it's actually getting the data, so I assume the problem is with the repo.

Edit: This is the error I get when I replace the setParameter("sort", ...) for a hardcoded string aka query.setParameter("sort", "name"):

java.lang.NumberFormatException: For input string: "name"

And I think this method should stand for strings as well. If I use query.setParameter("sort", pageable.getSort()), the error is the same.

Solution

The order by cannot be set as a query parameter. Also, the Pageable.getSort().toString() likely won't return a string suitable for use in an order by clause as it will result in a String that represents the Order as property: ORDER, note the colon.

Here are some modifications that will work, assuming Java 8...

String order = StringUtils.collectionToCommaDelimitedString(
   StreamSupport.stream(sort.spliterator(), false)
      .map(o -> o.getProperty() + " " + o.getDirection())
      .collect(Collectors.toList()));

Query query = em.createQuery(
   String.format("SELECT project FROM Project project ORDER BY %s", order));