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

huangapple go评论79阅读模式
英文:

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

问题

我是新手对于Hibernate。我的前辈使用了JpaSpecificationExecutorWithProjection(接口)的findAll方法,该方法根据'LIKE'运算符返回结果,但我需要根据'AND'运算符返回结果。请指导我如何解决这个问题。

以下是显示它正在命中基于'LIKE'运算符的查询的代码部分:

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 ?
) 
英文:

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 ?
) 

答案1

得分: 1

这是使用AND运算符的。LIKE用于评估您的各个参数。

如果您不喜欢JPA方法查询,您总是可以在您的存储库中编写自己的查询。以下是一个快速示例,包含了左连接并返回一个Page对象。请在这里查看一些基本的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
    );
英文:

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(&quot;SELECT p FROM Product p &quot;
            + &quot;LEFT JOIN p.categories category &quot;
            + &quot;WHERE UPPER(p.name) LIKE UPPER(CONCAT(&#39;%&#39;, COALESCE(:searchRequest, &#39;&#39;), &#39;%&#39;)) &quot;
            + &quot;AND UPPER(p.description) LIKE UPPER(CONCAT(&#39;%&#39;, COALESCE(:description, &#39;&#39;), &#39;%&#39;)) &quot;
            + &quot;AND p.price BETWEEN :priceLow AND :priceHigh &quot;
            + &quot;AND p.averageRating &gt;= :averageRating &quot;
            + &quot;AND p.archived = :archived &quot;
            + &quot;AND ((category.name IN :selectedCategories) &quot;
            + &quot;OR (:amountOfSelectedCategories = 0 AND category IN (SELECT c FROM Category c))) &quot;
            + &quot;GROUP BY p &quot;
            + &quot;HAVING SIZE(p.categories) &gt;= :amountOfSelectedCategories&quot;
    )
    Page&lt;Product&gt; findAllBySearchModel(
            Pageable pageable,
            @Param(&quot;searchRequest&quot;) String searchRequest,
            @Param(&quot;description&quot;) String description,
            @Param(&quot;priceLow&quot;) BigDecimal priceLow,
            @Param(&quot;priceHigh&quot;) BigDecimal priceHigh,
            @Param(&quot;averageRating&quot;) double averageRating,
            @Param(&quot;archived&quot;) boolean archived,
            @Param(&quot;selectedCategories&quot;) List&lt;String&gt; selectedCategories,
            @Param(&quot;amountOfSelectedCategories&quot;) int amountOfSelectedCategories
    );

huangapple
  • 本文由 发表于 2020年7月31日 21:52:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/63193152.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定