CODEWITHSUNDEEP
javajpaone-to-manycriteriapredicate
Taras Svidnytskyy
Taras Svidnytskyy

Reputation: 143

Dynamic query with CriteriaBuilder: one-to-many

I want to build a query using CriteriaBuilder and Predicate, which will return a list of only those products that have "images". But given the fact that there is relation between the Product and Image @OneToMany, I do not know how to implement it. For example, using the JpaRepository, I know that this query can be constructed this way: List<Product> findAllByImagesIsNotNull().

But I need a dynamic query so, as I understand, it's better to use CriteriaBuilder.

So, I have two entities:

    @Entity
    @Table(name = "products")
    public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    long id;

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "product_details_id")
    ProductDetails productDetails;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "category_id")
    Category category;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "lens_color_id")
    LensColor lensColor;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "frame_color_id")
    FrameColor frameColor;

    @OneToMany(fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    List<Image> images = new ArrayList<>();

    @Column(unique = true)
    String productNumber;
}

and 

    @Entity
    @Table(name = "images")    
    public class Image {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;

    String imageName;

    String imageType;

    boolean isMainImage;
}

I have two metamodels: 

@StaticMetamodel(Product.class)
public abstract class Product_ {

    public static volatile SingularAttribute<Product, LensColor> lensColor;
    public static volatile ListAttribute<Product, Image> images;
    public static volatile SingularAttribute<Product, FrameColor> frameColor;
    public static volatile SingularAttribute<Product, Long> id;
    public static volatile SingularAttribute<Product, String> productNumber;
    public static volatile SingularAttribute<Product, ProductDetails> productDetails;
    public static volatile SingularAttribute<Product, Category> category;

}

and

@StaticMetamodel(Image.class)
public abstract class Image_ {

    public static volatile SingularAttribute<Image, String> imageName;
    public static volatile SingularAttribute<Image, Boolean> isMainImage;
    public static volatile SingularAttribute<Image, Long> id;
    public static volatile SingularAttribute<Image, String> imageType;

}

And this is my query code:

    public List<Product> findByCriteria(Integer minPrice, Integer maxPrice, List<LensColor> lensColor, List<FrameColor> frameColor) {
        return productDAO.findAll(new Specification<Product>() {
            @Override
            public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                Join<Product, ProductDetails> productDetailsJoin = root.join(Product_.productDetails, JoinType.INNER);

                List<Predicate> predicates = new ArrayList<>();

// MY PREDICATE CODE FOR FIND ALL PRODUCTS WITH IMAGES IS NOT NULL

                if (!CollectionUtils.isEmpty(lensColor)) {
                    predicates.add(criteriaBuilder.and(root.get("lensColor").in(lensColor)));
                }
                if (!CollectionUtils.isEmpty(frameColor)) {
                    predicates.add(criteriaBuilder.and(root.get("frameColor").in(frameColor)));
                }
                if (minPrice != null && maxPrice != null) {
                    predicates.add(criteriaBuilder.between(productDetailsJoin.get(ProductDetails_.price), minPrice, maxPrice));
                }

                   return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        });
    }

Upvotes: 0

Views: 1128

Answers (1)

Alex Salauyou
Alex Salauyou

Reputation: 14338

For *-to-many relation, use a PluralJoin depending on collection type, kind of:

Join<Product, Image> joinImages = root.joinList("images", JoinType.INNER);
predicates.add(cb.isNotNull(joinImages.get(Image_.id)));

Upvotes: 1

Related Questions