How to do Pagination with filter-CodePudding

I want to make paginable service with filter but its fail to resultset

Here is my controller

 @GetMapping(path = "/PageFilter")
    public DataResponsePagination<HistoryBankWrapper, HistoryBank> pageFilter(
        @RequestParam("filter") String keyfilter ,
        @RequestParam("sortField") String field ,
        @RequestParam("sortOrder") String order ,
        @RequestParam("page") int page, 
        @RequestParam("size") int size) {
        return new DataResponsePagination<HistoryBankWrapper, HistoryBank>(historyBankService.findByFilter(keyfilter,field,order, page, size));
    }

Here is my service

public PaginationList<HistoryBankWrapper, HistoryBank> findByFilter(String keyfilter,String sortField,String sortOrder, int page, int size) {
    Pageable paging = PageRequest.of(page, size);
    Page<HistoryBank> historyPage = historyBankRepository.findAllFilter(keyfilter, sortField, sortOrder, paging);
    List<HistoryBank> historyList =  historyPage.getContent();
    List<HistoryBankWrapper> historyWrapperList = toWrapperList(historyList);
    return new PaginationList<HistoryBankWrapper, HistoryBank>(historyWrapperList, historyPage);
    }

here is my Repository

@Query(value = "SELECT * FROM HISTORY_BANK WHERE :sortField LIKE '%' || :keyFilter || '%' ORDER BY :sortField :sortOrder",
          countQuery = "SELECT count(*) FROM HISTORY_BANK",
          nativeQuery = true)
Page<HistoryBank> findAllFilter(@Param("keyFilter") String keyfilter,  @Param("sortOrder") String sortOrder,@Param("sortField") String sortField, @Param("paging") Pageable paging);

CodePudding user response：

Ditch your own method and use the framework. Use specifications to create a dynamic query.

Your repository should extend the JpaSpecificationExecutor and then you should invoke the findAll(Specification, Pageable) from your service. Your service should prepare the Specification it needs to build the query.

public interface YourRepository extends JpaRepository<HistoryBank, Long>,
JpaSpecificationExecutor<HistoryBank> {}

Your service can then use the aforementioned findAll method.

public PaginationList<HistoryBankWrapper, HistoryBank> findByFilter(String keyfilter,String sortField,String sortOrder, int page, int size) {
    Sort sort = Sort.by(Sort.Direction.fromString(sortOrder), sortField); 
    Pageable paging = PageRequest.of(page, size);
    Specification<HistoryBank> spec = (r, c, b) -> b.like(r.get(sortField), "%"   keyFilter   "%"));
    Page<HistoryBank> historyPage = historyBankRepository.findAllFilter(spec, paging);
    List<HistoryBank> historyList =  historyPage.getContent();
    List<HistoryBankWrapper> historyWrapperList = toWrapperList(historyList);
    return new PaginationList<HistoryBankWrapper, HistoryBank>(historyWrapperList, historyPage);
    }

You can even make this easier by modifying your controller to directly bind to a Pageable object, that saves you creating it.

public DataResponsePagination<HistoryBankWrapper, HistoryBank> pageFilter(
        @RequestParam("filter") String keyfilter ,
        Pageable page) {
        {

Now you only need to create the Specification and can just pass along the Pageable.

CodePudding user response：

In the Jpa repository you use the symbol of OR (||) try with a keyword or and modified your query for filtering you have to use and instead of or