如何实现支持空参数、分页排序的ConfigVO多条件JPA查询？

针对你的需求，推荐两种可行的实现方式，均能处理空参数、分页排序以及子项状态统计：

方案一：原生SQL + SpEL 动态条件 + 分页 #

这种方式适合熟悉SQL语法的场景，直接复用你已有的SQL逻辑，通过SpEL处理空参数，同时支持分页排序。

1. 改造Repository接口 #

在ConfigRepository中添加带原生SQL的查询方法，用SpEL判断参数是否为空，为空则跳过对应条件；同时指定countQuery用于分页统计总条数：

public interface ConfigRepository extends JpaRepository<Config, Long> {

    @Query(nativeQuery = true, value = """
        select a.id, a.code, a.name, a.state,
               coalesce(b._valid, 0) validItemCount,
               coalesce(b._invalid, 0) invalidItemCount,
               coalesce(b._deleted, 0) deletedItemCount
        from tb_sm_config a 
        left join (
            select config_id, 
                   sum(if(state = 'VALID', 1, 0)) _valid, 
                   sum(if(state = 'INVALID', 1, 0)) _invalid, 
                   sum(if(state = 'DELETED', 1, 0)) _deleted  
            from tb_sm_config_item 
            group by config_id
        ) b on a.id = b.config_id
        where 1=1
            and (:code is null or a.code like concat('%', :code, '%'))
            and (:name is null or a.name like concat('%', :name, '%'))
            and (:state is null or a.state = :state)
        order by ?#{#pageable.sort}
    """,
    countQuery = """
        select count(a.id)
        from tb_sm_config a 
        where 1=1
            and (:code is null or a.code like concat('%', :code, '%'))
            and (:name is null or a.name like concat('%', :name, '%'))
            and (:state is null or a.state = :state)
    """)
    Page<Object[]> findByConditions(@Param("code") String code, 
                                    @Param("name") String name, 
                                    @Param("state") State state, 
                                    Pageable pageable);
}

2. Service层转换为ConfigVO #

将查询返回的Object[]数组映射为ConfigVO对象：

@Service
public class ConfigService {

    @Autowired
    private ConfigRepository configRepository;

    public Page<ConfigVO> queryConfigList(String code, String name, State state, Pageable pageable) {
        Page<Object[]> resultPage = configRepository.findByConditions(code, name, state, pageable);
        return resultPage.map(items -> {
            ConfigVO vo = new ConfigVO();
            vo.setId((Long) items[0]);
            vo.setCode((String) items[1]);
            vo.setName((String) items[2]);
            vo.setState((State) items[3]);
            vo.setValidItemCount((Long) items[4]);
            vo.setInvalidItemCount((Long) items[5]);
            vo.setDeletedItemCount((Long) items[6]);
            return vo;
        });
    }
}

方案二：JpaSpecificationExecutor + CriteriaQuery 动态查询 #

这种方式采用面向对象的方式构建查询，跨数据库兼容，动态条件处理更灵活，无需编写原生SQL。

1. 让Repository继承JpaSpecificationExecutor #

public interface ConfigRepository extends JpaRepository<Config, Long>, JpaSpecificationExecutor<Config> {
}

2. 编写Specification实现类 #

构建动态查询条件，并通过子查询统计各状态的ConfigItem数量：

public class ConfigSpecification implements Specification<Config> {

    private final String code;
    private final String name;
    private final State state;

    public ConfigSpecification(String code, String name, State state) {
        this.code = code;
        this.name = name;
        this.state = state;
    }

    @Override
    public Predicate toPredicate(Root<Config> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        List<Predicate> predicates = new ArrayList<>();

        // 处理code模糊查询
        if (StringUtils.isNotBlank(code)) {
            predicates.add(cb.like(root.get("code"), "%" + code + "%"));
        }

        // 处理name模糊查询
        if (StringUtils.isNotBlank(name)) {
            predicates.add(cb.like(root.get("name"), "%" + name + "%"));
        }

        // 处理state精确查询
        if (state != null) {
            predicates.add(cb.equal(root.get("state"), state));
        }

        // 统计有效ConfigItem数量的子查询
        Subquery<Long> validSubquery = query.subquery(Long.class);
        Root<ConfigItem> validItemRoot = validSubquery.from(ConfigItem.class);
        validSubquery.select(cb.count(validItemRoot))
                .where(cb.equal(validItemRoot.get("config"), root),
                       cb.equal(validItemRoot.get("state"), State.VALID));

        // 统计无效ConfigItem数量的子查询
        Subquery<Long> invalidSubquery = query.subquery(Long.class);
        Root<ConfigItem> invalidItemRoot = invalidSubquery.from(ConfigItem.class);
        invalidSubquery.select(cb.count(invalidItemRoot))
                .where(cb.equal(invalidItemRoot.get("config"), root),
                       cb.equal(invalidItemRoot.get("state"), State.INVALID));

        // 统计已删除ConfigItem数量的子查询
        Subquery<Long> deletedSubquery = query.subquery(Long.class);
        Root<ConfigItem> deletedItemRoot = deletedSubquery.from(ConfigItem.class);
        deletedSubquery.select(cb.count(deletedItemRoot))
                .where(cb.equal(deletedItemRoot.get("config"), root),
                       cb.equal(deletedItemRoot.get("state"), State.DELETED));

        // 直接构造ConfigVO对象（需给ConfigVO添加对应构造函数）
        query.select(cb.construct(ConfigVO.class,
                root.get("id"),
                root.get("code"),
                root.get("name"),
                root.get("state"),
                cb.coalesce(validSubquery, 0L),
                cb.coalesce(invalidSubquery, 0L),
                cb.coalesce(deletedSubquery, 0L)
        ));

        return cb.and(predicates.toArray(new Predicate[0]));
    }
}

3. 给ConfigVO添加构造函数 #

用于CriteriaQuery直接映射结果：

public class ConfigVO {
    private Long id;
    private String code;
    private String name;
    private State state;
    private Long validItemCount;
    private Long invalidItemCount;
    private Long deletedItemCount;

    // 添加对应构造函数
    public ConfigVO(Long id, String code, String name, State state, Long validItemCount, Long invalidItemCount, Long deletedItemCount) {
        this.id = id;
        this.code = code;
        this.name = name;
        this.state = state;
        this.validItemCount = validItemCount;
        this.invalidItemCount = invalidItemCount;
        this.deletedItemCount = deletedItemCount;
    }

    // getter和setter省略
}

4. Service层调用查询 #

@Service
public class ConfigService {

    @Autowired
    private ConfigRepository configRepository;

    public Page<ConfigVO> queryConfigList(String code, String name, State state, Pageable pageable) {
        return configRepository.findAll(new ConfigSpecification(code, name, state), pageable);
    }
}

方案对比 #

• 方案一：SQL逻辑直观，性能可控，但需维护主查询和countQuery，排序语法依赖数据库类型。
• 方案二：面向对象，跨数据库兼容，动态条件扩展性强，但对JPA Criteria API有一定学习成本。

不推荐使用Example接口：Example仅支持简单的相等匹配，无法实现自定义模糊查询（如%xxx%）和聚合统计，不满足你的需求。

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