JPARepository原生查询添加动态WHERE条件的实现方法

方案1：直接改造现有@Query原生SQL（仅需少量改动，适合动态条件少的场景） #

你可以直接在原生SQL的WHERE子句中加入参数非空判断逻辑，参数为空时该条件自动恒真，不会触发过滤，非空时才执行匹配。

改造步骤： #

• 给查询方法新增idBrand参数，用@Param绑定参数名
• 在主查询、countQuery的JOIN之后、GROUP BY之前，新增动态判断的WHERE条件
• 条件写法为WHERE (:idBrand IS NULL OR brand.id_brand = :idBrand)，传入null时自动跳过该过滤规则

改造后完整代码： #

@Query(value = "SELECT product.id_product as idProduct," +
            " product.product_name as productName," +
            " brand.id_brand as idBrand," +
            " brand.name_brand as nameBrand," +
            " type.name_type as nameType," +
            " type.short_type as shortType," +
            " product_image.url_image as img," +
            " product_image.alt_image as alt," +
            " NVL(AVG(product_review.score),0) as score," +
            " COUNT(product_review.textreview) as countComments" +
            " FROM product" +
            " LEFT JOIN product_review ON product.id_product=product_review.id_product" +
            " JOIN brand ON product.id_brand=brand.id_brand" +
            " JOIN type ON product.id_type=type.id_type" +
            " JOIN product_image ON product.id_product=product_image.id_product" +
            " WHERE (:idBrand IS NULL OR brand.id_brand = :idBrand)" +
            " GROUP BY product.id_product",
            countQuery = "SELECT count(*) FROM product WHERE (:idBrand IS NULL OR id_brand = :idBrand)",
            nativeQuery = true)
Page<ProductsToListing> getAllProductsToListing(Pageable pageable, @Param("idBrand") Long idBrand);

注意事项： #

• 如果idBrand是整数类型，业务中存在传0作为默认值的场景，可以把判断条件改成WHERE (:idBrand = 0 OR brand.id_brand = :idBrand)即可
• 该写法兼容所有主流关系型数据库，无需额外依赖

方案2：使用动态查询API（适合后续会新增多个动态过滤条件的场景） #

如果后续还要加更多动态过滤规则（比如按类型、价格区间筛选），推荐使用JPA Criteria API 或者 QueryDSL 来实现完全动态的SQL拼接，避免@Query注解里的SQL过度臃肿，可维护性更高。

内容的提问来源于stack exchange，提问作者Wojciech Dąbrowski