Merge multiple Spring JPA Page objects results

Question

My initial requirement was to fetch Car details as:

List<String> carMake = getUserCarMake();
Page<Car> carDetails = carRepository.findAllCarsInfo(carMake, pageRequest);

CarRepository.java

@Query(value="SELECT A FROM Cars A WHERE A.model = ?1 
 public Page<Cars> findAllCarsInfo(List<String> carMake, Pageable pageRequest);

Now my requirement has changed to fetch car details based car models for each car make. So I have changed the code as shown

for (Cars car : userCars) {
    String carMake = car.getCarMake();
       List<String> carModelForMake = new ArrayList<>();
       List <CarModels> carModelList  = car.getCarModels();
        for (CarModels carModel : carModelList) {
            carModelForMake.add(carModel.getModelName());

    Page<Car> carDetails = carRepository.findAllCarsInfo(carModelForMake, carMake, pageRequest)
        }

    }

CarRepository.java

@Query(value="SELECT A FROM Cars A WHERE A.model IN ?1 AND A.make = ?2” 

public Page<Car> findAllCarsInfo(List<String> carModel, String carMake,Pageable pageRequest);

So for each car i have a carMake and corresponding carModels for that make which i then pass to the query to fetch carDetails which is a Page Object. As a result same query is called multiple times for different carMake. The problem is how do I manage the Page object here. In the above scenario the Page object will contain only the details of last car from the carModelList, rest will be overwritten as I do not have an option of carDetails.addAll() as in case of List.

Note: I cannot use the below query as the model can overlap across different makes.

SELECT A FROM Cars A WHERE A.model IN ?1 AND A.make IN ?2

Also my pageRequest has size as (0, 20 )

I have tried to modify the query to remove pageRequest and use findAll to fetch the results in List and then convert them to PageObject but that breaks the pagination because the page.getContent() has the entire result set and not just 20 records.

Page<Car> carDetails = new PageImpl <>(carDetailsList, pageRequest, carDetailsList.size());

How can I effectively get Page object or merge different page objects here so that my pagination works as it did in my previous requirement.

Homs

The question is unclear. Could you explain your need a bit better? I understood your code but I didn't understand your requirement. – Inapt

Answer 1

Sometimes it is a good idea to create a special "query entity" class that includes everything that is needed to respond to a certain kind of client request.

The general idea is like this:

Let's say you'd have two classes in you domain:

@Entity
@Table(name = "table_a")
public class A {

  @Id int id;
  String propertyA;
  int bId;
}

@Entity
public class B {
  @Id int id;
  String propertyB;
}

And then you'd combine the two two the mentioned "query entity" (outside of the domain).

@Entity
@Table(name = "table_a")
public class QueryEntity {
  private @Id private int aId;
  private String propertyA;
  private B b;

  public String propertyA() {
    return propertyA;
  }

  public String propertyB() {
    return b.propertyB;
  }
}

I'm not quite sure whether this approach is applicable in your case, but hopefully it makes the idea clear.

Grindstone

Answer 2

Didn't understand the requirement 100%. But generally answering to your problem, stick into one type of approach, either query language or ORM. Things will be difficult when you try to use both.

Since your requirement is bit complex and you need more granular control, it is better to use the query language. Write an optimized query to fetch from the tables as you need. Then you can map them into the models you need. Do not use page model, use limit and offset in the query and handle pagination in your code using a do while loop which is the classic way of handling pagination.

    int limit = 20;
    int pageNo = 0;
    boolean hasMore = true;
    List<Car> carDetails = new ArrayList<>();

    do {
        int offset = limit * pageNo;
        List<Car> carDetailsBatch = carRepository.findAllCarsInfo(carModelForMake, carMake, limit, offset);
        if (!carDetailsBatch.isEmpty()) {
            carDetails.addAll(carDetails);
            pageNo += 1;
        }  else{
            hasMore = false;
        }
    } while (hasMore);

Catnip