[FIXED] How to get data based on 'AND' operator instead of 'LIKE' by using JpaSpecificationExecutorWithProjection's findAll method?

Issue

I am new to Hibernate.My senior used 'findAll' method of JpaSpecificationExecutorWithProjection (interface) which is returning result based on a 'LIKE' operator but I required result based on 'AND' operator. Please guide me how I can solve this?.

Below is the part of code which displays it is hitting query based on 'LIKE' operator

where
        (
            upper(course0_.course_subject) like ?
        ) 
        and (
            upper(course0_.course_sub_category) like ?
        ) 
        and course0_.course_status=? 
        and (
            upper(course0_.course_exam_segment) like ?
        ) 
        and (
            upper(course0_.course_category) like ?
        ) 

Solution

It is using AND operator. The LIKE is for evaluating your individual params.

If you don't like the JPA-methodqueries, you can always write your own in your repository. Below is a quick example incorporating a left join and returning a Page-object. Please take a look here for some basic JPQL

@Query("SELECT p FROM Product p "
            + "LEFT JOIN p.categories category "
            + "WHERE UPPER(p.name) LIKE UPPER(CONCAT('%', COALESCE(:searchRequest, ''), '%')) "
            + "AND UPPER(p.description) LIKE UPPER(CONCAT('%', COALESCE(:description, ''), '%')) "
            + "AND p.price BETWEEN :priceLow AND :priceHigh "
            + "AND p.averageRating >= :averageRating "
            + "AND p.archived = :archived "
            + "AND ((category.name IN :selectedCategories) "
            + "OR (:amountOfSelectedCategories = 0 AND category IN (SELECT c FROM Category c))) "
            + "GROUP BY p "
            + "HAVING SIZE(p.categories) >= :amountOfSelectedCategories"
    )
    Page<Product> findAllBySearchModel(
            Pageable pageable,
            @Param("searchRequest") String searchRequest,
            @Param("description") String description,
            @Param("priceLow") BigDecimal priceLow,
            @Param("priceHigh") BigDecimal priceHigh,
            @Param("averageRating") double averageRating,
            @Param("archived") boolean archived,
            @Param("selectedCategories") List<String> selectedCategories,
            @Param("amountOfSelectedCategories") int amountOfSelectedCategories
    );

Answered By - Paul