3

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.

eyeprime
  • 75
  • 1
  • 8

1 Answers1

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"));
Forketyfork
  • 7,416
  • 1
  • 26
  • 33
  • Sorry, I have missed that I use Spring Data JDBC. So, I won´t be able to use Page as return value inside the repository. I will get back a List of Customers. I think one option could be to use a customized Comparator, but maby there is a more convenienced way to do. – eyeprime Aug 07 '20 at 08:21
  • @eyeprime actually, sorting and paging is supported starting from Spring Data JDBC 2.0: https://docs.spring.io/spring-data/jdbc/docs/2.0.2.RELEASE/reference/html/#new-features.2-0-0 – Forketyfork Aug 07 '20 at 09:08
  • Sergei, I am using Release 2.1. of Spring Data. Your described solution results in an java.lang.ClassCastException. I created a small GitHub project based on spring boot data jdbc and h2 database for Demonstration. The repo works directly, nothing special to set up. Feel free to make a pull request and show your solution there. – eyeprime Aug 09 '20 at 18:58
  • @eyeprime thanks for the reproducer. I've only changed the version of `spring-boot-starter-parent` to the latest release `2.3.2.RELEASE`. I observed not `ClassCastException`, but `IncorrectResultSizeDataAccessException`, which seems to be a known issue https://jira.spring.io/projects/DATAJDBC/issues/DATAJDBC-554. But I've seen you've already applied the proposed workaround in your `CustomerController.findByNameList()` method, which seems to work nicely. Hopefully, this answers your question. – Forketyfork Aug 10 '20 at 12:34