How to introduce dynamic sorting in Query annotated JPQL

Question

I have defined the following method to retrieve application matching supplied criteria with Pagination support as well as sorting by dynamic column name.

@Repository
public interface ApplicationsRepository extends JpaRepository<Applications, Long> 
 @Query(name = "Applications.searchApplication")
Page<Application> search(@Param("roleId")Long userTypeId, @Param("appStatusId")Long appStatusId,
        @Param("searchParam") String searchParam, Pageable pageRequest, 
        @Param("queueType") String queueType);

Here, I found "Finder method %s is backed " + "by a NamedQuery and must " + "not contain a sort parameter as we cannot modify the query! Use @Query instead!"

I am creating Pageable in the following way:

Direction direction = inputDirection == null ? Sort.Direction.DESC : Sort.Direction.ASC;
Sort sort = inputOrderBy == null ? null : new Sort(direction, inputOrderBy);
Pageable pageable =new PageRequest(inPageNo, pagination.inPageSize(), sort);

My concern is that the generated Oracle Query contains "fetch first ? rows only" but no order by clause.

Any help in this issue will be really helpful.

Environment:

spring-data-jpa:1.11.4.RELEASE

hibernate-entitymanager:5.0.12.Final

Java version: 1.8.0_60, vendor: Oracle Corporation

OS name: "windows 7", version: "6.1", arch: "amd64", family: "dos"

Cant we use a query and add the order by clause on the basis of the flag you get from request — Mudassar
and if yet we have to do it this way can you share the entire code then maybe we can think of the solution — Mudassar
@lab bhattacharjee would you share with your @Query ? I mean Applications.searchApplication — user3529850

Jens Schauder Jens Schauder · Accepted Answer · 2017-10-25T05:51:27

You already found the error message. Just do what it says:

Specify your query using @Query including the count query that needs to be executed in order to create a Page

How to introduce dynamic sorting in Query annotated JPQL

1 Answers