Sorting is not working with Pageable in Springboot

Question

I am trying to implement Pagination and sorting by using Pageable in Springboot, JPARepository. Somehow sorting is not working. I am including my code below where I have controller, service class, repository, entity etc. I've also posted my console output where you can see only limit is appended but not "order by" to sql query. I dont know what I am missing here as I've followed everything as documented in Spring.io for pagination&sorting.

TestController:

    @RestController
    @RequestMapping("/test")
    public class TestController {

        @Autowired
        private TestService testService;

        @GetMapping("/list/{fileId}")
        public Page<Test> list(@PathVariable Integer fileId, @RequestParam Map<String, String> queryMap) throws Exception {
            return testService.getTestList(fileId, queryMap);
        }

    }

TestEntity:

public class Test implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @NotNull
    @Column(name = "id")
    private Integer id;

    @Column(name = "fileId")
    private Integer fileId;

    @Column(name = "fname")
    private String fname;

    @Column(name = "lname")
    private String lname;

    @Column(name = "email")
    private String email;

    @Column(name = "address")
    private String address;

    public Test() {
    }

    public Test(Integer id) {
        this.id = id;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getFileId() {
        return fileId;
    }

    public void setFileId(Integer fileId) {
        this.fileId = fileId;
    }

    public String getFname() {
        return fname;
    }

    public void setFname(String fname) {
        this.fname = fname;
    }

    public String getLname() {
        return lname;
    }

    public void setLname(String lname) {
        this.lname = lname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }


}

TestRepository:

public interface TestRepo extends JpaRepository<Test, Integer> {

    Page<Test> findByFileId(@Param("fileId") int fileId, Pageable pageable);

}

TestService:

@Service
public class TestService {

    @Autowired
    private TestRepo testRepo;

    public Page<Test> getTestList(Integer fileId, Map<String, String> queryMap) {

        Sort sort = Sort.by(Direction.valueOf(queryMap.get("direction")), queryMap.get("property"));

        Pageable pageable = PageRequest.of(Integer.parseInt(queryMap.get("pageNo")) - 1,
                Integer.parseInt(queryMap.get("pageSize")), sort);

        Page<Test> testDetails = testRepo.findById(id, pageable);

        return testDetails;
    }

}

GetRequest:

http://localhost:8080/cms/test/list/0?pageNo=1&pageSize=5&direction=DESC&property=fname

ConsoleOutput:

As we see in the console output there is no order by appended in the sql query even though the sort object is passed to the JPARepository Query. Can I get some help here.

[nio-8080-exec-3] org.hibernate.SQL                        : select test0_.id as id1_21_, test0_.address as address2_21_, test0_.email as email3_21_, test0_.fname as fname4_21_, test0_.lname as lname5_21_ from test test0_ where test0_.fileId=? limit ?

[nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [0]

Answer 1

I have encountered the same issue trying to page and sort a named query

Only paging a named query works in my case, paging + sorting not.

So I have added sorting directly in my named query, being the sorting column not dynamic, and the paging from my @Service:

Pageable pageable = PageRequest.of(page, perPage);
Page<Customer> pageCustomer = prospectRepository.findAllNotDeletedSortByName(pageable);

My named query in my orm.xml:

<named-query name="Prospect.findAllNotDeletedSortByDenomAzienda">
    <query>
        <![CDATA[SELECT cr 
                   FROM Customer cr 
                  WHERE cr.deleted = false
                  ORDER BY cr.name ASC ]]>
    </query>
</named-query>

Answer 2

On your Repository extend PagingAndSortingRepository as below:

public interface TestRepo extends PagingAndSortingRepository<Test, Integer> {
    // ...
}