如何在JPA中结合自定义SQL与动态过滤SQL？

问题描述 #

我有如下自定义SQL：

select * from 
    (
        select shp.id, shp.status, shp.establish_date, shp.type  
        from shop shp
        inner join book bk 
        on shp.id = bk.shop_id 
        where 
        and shp.id not in 
        (
            select distinct bl.shop_id from banned_list bl 
            where 
            bl.country_risk = 'HIGH'
        )
    ) t1 

但我还需为shp.id、shp.status、shp.establish_date、shp.type字段添加动态过滤，同时要实现分页与排序功能。
若使用Specification，实现复杂且难以维护；若使用原生SQL查询，需大量字符串拼接且存在SQL注入风险。
请问在JPA中最适合的解决方案是什么？

推荐解决方案 #

方案1：Querydsl JPA（最适合复杂场景） #

Querydsl提供类型安全的动态查询API，完全避免字符串拼接带来的SQL注入风险，同时能优雅处理关联查询、子查询、动态过滤、分页排序，维护成本低。

实现步骤：

1. 引入依赖：
   以Maven为例，添加Querydsl相关依赖：

   <dependency>
       <groupId>com.querydsl</groupId>
       <artifactId>querydsl-jpa</artifactId>
       <version>5.0.0</version>
   </dependency>
   <dependency>
       <groupId>com.querydsl</groupId>
       <artifactId>querydsl-apt</artifactId>
       <version>5.0.0</version>
       <scope>provided</scope>
   </dependency>
   

   同时配置APT插件生成实体对应的Q类（如QShop、QBook）。

2. 编写动态查询逻辑：

   @Autowired
   private JPAQueryFactory queryFactory;
   
   public Page<ShopDTO> queryShops(ShopFilter filter, Pageable pageable) {
       QShop shp = QShop.shop;
       QBook bk = QBook.book;
       QBannedList bl = QBannedList.bannedList;
   
       // 构建黑名单店铺子查询
       SubQueryExpression<Long> bannedShopSubQuery = queryFactory
               .select(bl.shopId.distinct())
               .from(bl)
               .where(bl.countryRisk.eq("HIGH"));
   
       // 主查询：关联表+动态过滤
       JPAQuery<ShopDTO> query = queryFactory
               .select(Projections.bean(ShopDTO.class,
                       shp.id, shp.status, shp.establishDate, shp.type))
               .from(shp)
               .innerJoin(bk).on(shp.id.eq(bk.shopId))
               .where(shp.id.notIn(bannedShopSubQuery))
               // 动态添加过滤条件
               .where(filter.getId() != null ? shp.id.eq(filter.getId()) : null)
               .where(filter.getStatus() != null ? shp.status.eq(filter.getStatus()) : null)
               .where(filter.getEstablishDateStart() != null ? shp.establishDate.goe(filter.getEstablishDateStart()) : null)
               .where(filter.getEstablishDateEnd() != null ? shp.establishDate.loe(filter.getEstablishDateEnd()) : null)
               .where(filter.getType() != null ? shp.type.eq(filter.getType()) : null)
               // 分页设置
               .offset(pageable.getOffset())
               .limit(pageable.getPageSize())
               // 动态排序
               .orderBy(pageable.getSort().stream()
                       .map(order -> order.isAscending() ?
                               new OrderSpecifier<>(Order.ASC, getSortPath(shp, order.getProperty())) :
                               new OrderSpecifier<>(Order.DESC, getSortPath(shp, order.getProperty())))
                       .toArray(OrderSpecifier[]::new));
   
       // 查询总条数
       long totalCount = queryFactory
               .select(shp.id.countDistinct())
               .from(shp)
               .innerJoin(bk).on(shp.id.eq(bk.shopId))
               .where(shp.id.notIn(bannedShopSubQuery))
               .where(filter.getId() != null ? shp.id.eq(filter.getId()) : null)
               .where(filter.getStatus() != null ? shp.status.eq(filter.getStatus()) : null)
               .where(filter.getEstablishDateStart() != null ? shp.establishDate.goe(filter.getEstablishDateStart()) : null)
               .where(filter.getEstablishDateEnd() != null ? shp.establishDate.loe(filter.getEstablishDateEnd()) : null)
               .where(filter.getType() != null ? shp.type.eq(filter.getType()) : null)
               .fetchOne();
   
       return new PageImpl<>(query.fetch(), pageable, totalCount);
   }
   
   // 辅助方法：映射排序字段到Q类属性
   private Path<?> getSortPath(QShop shp, String property) {
       return switch (property) {
           case "id" -> shp.id;
           case "status" -> shp.status;
           case "establishDate" -> shp.establishDate;
           case "type" -> shp.type;
           default -> shp.id;
       };
   }
   

   • 核心优势：编译期类型检查，避免字段名写错；动态条件添加直观；分页排序无缝整合；子查询和关联逻辑清晰，长期维护成本低。

方案2：Spring Data JPA @Query + SpEL（轻量无额外依赖） #

如果不想引入第三方依赖，可利用Spring Data JPA的@Query结合SpEL表达式实现动态过滤，同时原生支持分页排序。

实现步骤：

1. 定义Repository接口：

   public interface ShopRepository extends JpaRepository<Shop, Long> {
   
       @Query(value = """
               select shp.id, shp.status, shp.establish_date, shp.type  
               from shop shp
               inner join book bk on shp.id = bk.shop_id 
               where shp.id not in (
                   select distinct bl.shop_id from banned_list bl where bl.country_risk = 'HIGH'
               )
               and (:id is null or shp.id = :id)
               and (:status is null or shp.status = :status)
               and (:establishDateStart is null or shp.establish_date >= :establishDateStart)
               and (:establishDateEnd is null or shp.establish_date <= :establishDateEnd)
               and (:type is null or shp.type = :type)
               """,
               countQuery = """
               select count(distinct shp.id)
               from shop shp
               inner join book bk on shp.id = bk.shop_id 
               where shp.id not in (
                   select distinct bl.shop_id from banned_list bl where bl.country_risk = 'HIGH'
               )
               and (:id is null or shp.id = :id)
               and (:status is null or shp.status = :status)
               and (:establishDateStart is null or shp.establish_date >= :establishDateStart)
               and (:establishDateEnd is null or shp.establish_date <= :establishDateEnd)
               and (:type is null or shp.type = :type)
               """,
               nativeQuery = true)
       Page<Object[]> queryShops(@Param("id") Long id,
                                 @Param("status") String status,
                                 @Param("establishDateStart") LocalDate establishDateStart,
                                 @Param("establishDateEnd") LocalDate establishDateEnd,
                                 @Param("type") String type,
                                 Pageable pageable);
   }
   

2. 调用并转换为DTO：

   @Autowired
   private ShopRepository shopRepository;
   
   public Page<ShopDTO> getShops(ShopFilter filter, Pageable pageable) {
       Page<Object[]> rawResult = shopRepository.queryShops(
               filter.getId(),
               filter.getStatus(),
               filter.getEstablishDateStart(),
               filter.getEstablishDateEnd(),
               filter.getType(),
               pageable);
   
       List<ShopDTO> dtoList = rawResult.stream()
               .map(arr -> new ShopDTO(
                       (Long) arr[0],
                       (String) arr[1],
                       (LocalDate) arr[2],
                       (String) arr[3]
               ))
               .collect(Collectors.toList());
   
       return new PageImpl<>(dtoList, pageable, rawResult.getTotalElements());
   }
   

   • 核心优势：无需额外依赖，快速实现；参数绑定自动防SQL注入；分页直接复用Spring Data的Pageable。
   • 局限性：SQL语句会随过滤字段增多而冗长；排序字段需与数据库列名严格对应，映射成本略高。

方案选择建议 #

• 若业务需求迭代频繁、查询逻辑复杂，优先选Querydsl JPA，长期维护更省心。
• 若需求简单、不想引入第三方依赖，选**@Query + SpEL**即可快速落地。

内容的提问来源于stack exchange，提问作者just_code_dog