Spring Data JDBC customized Query with order Parmeter not working

Question

I try to order my custom query with a custom parameter like so:

@Query("select * from person where firstname = :name" + " order by :order")
List<Customer> findByFirstNameCustomQuery2(@Param("name") String name, Pageable page,
@Param("order") String order);

but it does not work.

Does someone know how to use "order by" as parameter inside a @Query?

remark: As answered below I created a github repository. You can find it here: https://github.com/kremerkp/spring-data-jdbc-pageable-h2.

The CustomerRepository is having five functions. "findByLastName" is a full-working example (sortable and pageable) that is using query functions.

In my real project I have a query that is to complex to use it with query functions, so I need to get the result with a custom query.

Answer 1

This particular case can be solved without a custom query, just by using the Sort attribute of Pageable. You should be able to use a Page as a method return type, but due to the bug https://jira.spring.io/projects/DATAJDBC/issues/DATAJDBC-554 this is currently (as of Spring Data JDBC 2.0.2.RELEASE) impossible.

However, you could create a method returning a List, and pass it a Pageable object:

List<Customer> findByFirstName(String name, Pageable page);

While the method returns a List, the pagination and sorting should still be applied, so the list contains only the data from a single page, sorted as requested:

repository.findByFirstName("Foo", 
  PageRequest.of(0, 1, Sort.by(Sort.Direction.ASC, "someSortField"));