Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to sort by multiple properties in Spring Data (JPA) derived queries?

I'm looking at the examples giving on this page (https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.repositories) about method naming, is it possible to create a complex chain method name such as

findByProgrammeAndDirectorAndProgDateBetweenOrderByProgDateStartTimeAsc 

In the example they give, they are only doing an OrderBy on one value. In the example above ProgDate and StartTime would be two separate values.

like image 791
PDStat Avatar asked Aug 19 '14 10:08

PDStat


People also ask

Is it possible to define a sort type in @query annotation using Spring JPA?

Using a Sort Object With Spring Data JPA, you can also add a parameter of type Sort to your method definition. Spring Data JPA will then generate the required ORDER BY clause. That is the same approach as you can use in a derived query.

Which of the following options can be used for sorting in Spring data JPA?

In Spring Data JPA query results can be sorted in two ways: using an ORDER BY clause in a JPQL query. adding a parameter of type Sort to the query method.

What is the difference between JpaRepository and PagingAndSortingRepository?

PagingAndSortingRepository provides methods to do pagination and sort records. JpaRepository provides JPA related methods such as flushing the persistence context and delete records in a batch.

In which order the results are sorted for JPQL query?

You can define the order in which the database shall return your query results with an ORDER BY clause. Its definition in JPQL is similar to SQL. You can provide one or more entity attributes to the ORDER BY clause and specify an ascending (ASC) or a descending (DESC) order.


2 Answers

The trick is to simply delimit the properties you want to sort by using the direction keywords Asc and Desc. So what you probably want in your query method is something like:

…OrderByProgDateAscStartTimeAsc 

Note, how we conclude the first property definition by Asc and keep going with the next property.

Generally speaking, we recommend switching to @Query based queries, once method names exceed a certain length or complexity. The main reason being that it's awkward for clients to call these very long methods. With @Query you rather get the full power of the query language plus a reasonably sized method name that might be of higher level language to express the intent of the query.

like image 174
Oliver Drotbohm Avatar answered Sep 28 '22 08:09

Oliver Drotbohm


Yes it's should be possible:

Try this:

findByProgrammeAndDirectorAndProgDateBetweenOrderByProgDateStartTimeAsc(String programme, String director, Date progStart, Date progEnd); 

I have not tested the code, but according to things I've already done, it should work.

like image 25
Paulo Fidalgo Avatar answered Sep 28 '22 08:09

Paulo Fidalgo