Java how to transform List<Object> to Page from JPA Paggination-CodePudding

I have a problem with my custom search method from Jpa Repository. I implement this method to custom serach and in this method i have List from my SQL base and i try to transform it to Page object from JPA Paggination & Sorting, but the way im trying to do this doesnt work. That is just an ilusion of pagination because in postman/internet browser i see all elements from list and when i change page and size value in endpoint that change nothing. Anybody know how to help?

GitHub project: https://github.com/s0bieskii/DemoCarRental

My endpoint from Controller:

@GetMapping("/find")
ResponseEntity<Page<CarDTO>> readAllCarsFiltered(@RequestParam(defaultValue = "0") int page,
                                                 @RequestParam(defaultValue = "5") int size,
                                                 Pageable pageable,
                                                 @RequestParam(required = false) String brand,
                                                 @RequestParam(required = false) String model,
                                                 @RequestParam(required = false) String type,
                                                 @RequestParam(required = false) Integer registrationYear,
                                                 @RequestParam(required = false) String color,
                                                 @RequestParam(required = false) String fuel,
                                                 @RequestParam(required = false) String transmission,
                                                 @RequestParam(required = false) String doors,
                                                 @RequestParam(required = false, defaultValue = "9999999999") double price) {
    return ResponseEntity.ok(carService.customerSearch(pageable,
            brand, model, type, registrationYear, color, fuel, transmission, doors, price));
}

My service

public Page<CarDTO> customerSearch(Pageable pageable, String brand, String model, String type, Integer registrationNumber, String color,
                                   String fuel, String transmission, String doors, double price){

    return carRepository.search(pageable, brand, model, type, registrationNumber, color, fuel, transmission, doors, price)
            .map(car -> carMapper.carToDto(car));
}

My custom search method from RepositoryImplementation

 @Override
    public Page<Car> search(Pageable pageable, String brand, String model, String type, Integer registrationYear, String color,
                            String fuel, String transmission, String doors, double price) {
        int var = 0;
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Car> query = cb.createQuery(Car.class);
        Root<Car> car = query.from(Car.class);
        query.select(car);
        Predicate predicate = cb.greaterThan(car.get("id"), var);

        if (brand != null) predicate = cb.and(predicate, cb.equal(car.get("brand"), brand));
        if (model != null) predicate = cb.and(predicate, cb.equal(car.get("model"), model));
        if (type != null) predicate = cb.and(predicate, cb.equal(car.get("type"), type));
        if (registrationYear != null)
            predicate = cb.and(predicate, cb.equal(car.get("registrationNumber"), registrationYear));
        if (color != null) predicate = cb.and(predicate, cb.equal(car.get("color"), color));
        if (fuel != null) predicate = cb.and(predicate, cb.equal(car.get("fuel"), Fuel.stringToFuelEnum(fuel)));
        if (transmission != null)
            predicate = cb.and(predicate, cb.equal(car.get("transmission"), Transmission.stringToTransmissionEnum(transmission)));
        if (doors != null) predicate = cb.and(predicate, cb.equal(car.get("doors"), doors));
        if (price != 0) predicate = cb.and(predicate, cb.lessThan(car.get("price"), price));
        //predicate = cb.and(predicate, cb.equal(car.get("available"), available));
        List<Car> carList=entityManager.createQuery(query.where(predicate)).getResultList();

        return new PageImpl<Car>(carList, pageable,carList.size());
    }
}

CodePudding user response：

When you do custom queries and want to use Pageable, it's up to you to implement a logic to create a paging for your result list. In your case you aren't doing that so it's probably (you said it isn't working, but didn't say what isn't working so I can only guess) just returning the entire result list without paging applied.

A simple solution would be to use sublist and use the result of that operation as your parameter for PageImpl. Big problem is that you are still getting the entire result list from the database in the background which creates uneceassary overhead.

You could also adjust your query using

em.createQuery(query.where(predicate))
      .setFirstResult(offset)
     .setMaxResults(limit)
     .getResultList()

Now the paging is done in the database which is a lot better.

Or you save yourself some trouble and use Springs Specification interface instead of creating your own search method.

First your CarRepository needs to extend JpaSpecificationExecutor. Now spring will generate a findAll(Specification, Pageable) method for your repository.

Second step is to create your search specification:

public class SearchSpecification implements Specification<Car> {
    private final String brand;
    private final String model
    private final String type
    private final Integer registrationYear
    private final String color
    private final String fuel
    private final String transmission
    private final String doors
    private final double price

    // constructor & maybe getter
    public Predicate toPredicate(Root<Car> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        // Create your search predicate
    }
}

Now you can simply call carRepository.findAll(new SearchSpecification (/* parameters */), pageable); and spring does the paging for you.