CriteriaQuery Implementation

I have 4 tables in DB, such as

• Product => PK productid
• Variant => PK variantid FK productid
• Images => PK imageid FK variantid
• Attribute => PK attributid FK variantid

So need to perform following action in Java Rest API

1. Pagination with sorting and filtering
2. One Product Object has different Variant with there images and attributes.

Below is the @Repository code for single table pagination i.e Product

@Repository public class PaginProductCriteriaRepository {

private final EntityManager entityManager;
private final CriteriaBuilder criteriaBuilder;


public PaginProductCriteriaRepository(EntityManager entityManager) {
    this.entityManager = entityManager;
    this.criteriaBuilder = entityManager.getCriteriaBuilder();
}

public Page<Product> findAllWithFilter(PaginProductPage paginPage, PaginProductSearchCriteria paginSearchCriteria){
    
    CriteriaQuery<Product> productCriteriaQuery = criteriaBuilder.createQuery(Product.class); 
    Root<Product> rootProduct = productCriteriaQuery.from(Product.class); 
     
    Predicate predicate = getPrediate(paginSearchCriteria,rootProduct); 
    productCriteriaQuery.where(predicate);
    setOrder(paginPage,productCriteriaQuery,rootProduct);
    
    TypedQuery<Product> typedQuery = entityManager.createQuery(productCriteriaQuery);
    typedQuery.setFirstResult(paginPage.getPageNumber() * paginPage.getPageSize());
    typedQuery.setMaxResults(paginPage.getPageSize());
    
    Pageable pageable = getPageable(paginPage);
    
    long paginCount = getPaginCountMethod(predicate);
    
    return new PageImpl<>(typedQuery.getResultList(),pageable,paginCount);
}


private Predicate getPrediate(PaginProductSearchCriteria paginSearchCriteria, Root<Product> root) {
    List<Predicate>  predicates = new ArrayList<>();
    
    if (Objects.nonNull(paginSearchCriteria.getpName())) {
        predicates.add(criteriaBuilder.like(root.get("pName"),"%" + paginSearchCriteria.getpName() + "%"));
    }
    return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
}

private void setOrder(PaginProductPage paginPage, CriteriaQuery<Product> criteriaQuery, Root<Product> root) {
    if (paginPage.getSortDirection().equals(Sort.Direction.ASC)) {
    criteriaQuery.orderBy(criteriaBuilder.asc(root.get(paginPage.getSortBy())));
    } else {
    criteriaQuery.orderBy(criteriaBuilder.desc(root.get(paginPage.getSortBy())));
    }
}


private Pageable getPageable(PaginProductPage paginPage) {
     Sort sort  = Sort.by(paginPage.getSortDirection(),paginPage.getSortBy());
     return PageRequest.of(paginPage.getPageNumber(),paginPage.getPageSize(),sort);
}

private long getPaginCountMethod(Predicate predicate) {
    CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
    Root<Product> countRoot =  countQuery.from(Product.class);
    countQuery.select(criteriaBuilder.count(countRoot)).where(predicate);
    return entityManager.createQuery(countQuery).getSingleResult();
}

}

Expectation: Using above code pagination is working fine using single Table i.e Product BUT I need to join multiple tables with product and get full product object contain variants, images and attributes.

Note: Single Product has multiple variants, variant has muluple attribute and images