或者“predicate inside”和“predicate using JPQL”

Question

我想使用JPQL执行select查询，但我的代码有一个错误，下面是代码

private CriteriaQuery<TbCase> getQueryParams(TbCaseSearchParams params, CriteriaBuilder cb, CriteriaQuery<TbCase> cq,Root<TbCase> root){

    List<Predicate> criteria = new ArrayList<Predicate>();

    if(params.getEqColumns() != null && params.getEqValues() != null){
        criteria = getEqParamsCriteria(criteria, cb, root, params.getEqColumns(), params.getEqValues());
    }else if(params.getLikeColumns() != null && params.getLikeValues() != null){
        criteria = getLikeParamsCriteria(criteria, cb, root, params.getLikeColumns(), params.getLikeValues());
    }else{  

        if(params.getCaseNumber() != null){
            criteria.add(cb.equal(root.get("caseNumber"), params.getCaseNumber()));
        }

        if(params.getCaseStatusIds() != null){
            List<String> caseStatusIds = new ArrayList<String>();
            caseStatusIds = params.getCaseStatusIds();
            for(int i=0 ; i<caseStatusIds.size() ; i++){
                criteria.add(cb.or(cb.equal(root.get("caseStatusByCaseStatusId"), Integer.valueOf(params.getCaseStatusIds().get(i)))));
            }
        }
    }

    cq.select(root).where(cb.and(criteria.toArray(new Predicate[0])));

    return cq;
}

这是SQL格式的查询结果

select something from TABLENAME as something where ( something.caseNumber=:param0 ) and ( something.caseStatusByCaseStatusId=1 ) and ( something.caseStatusByCaseStatusId=-1 ) and ( something.caseStatusByCaseStatusId=1 ) and ( something.caseStatusByCaseStatusId=9 ) and ( something.caseStatusByCaseStatusId=4 ) and ( something.caseStatusByCaseStatusId=5 ) and ( something.caseStatusByCaseStatusId=10 ) and ( something.caseStatusByCaseStatusId=2 ) order by something.caseNumber desc

预期的查询如下所示

select something from TABLENAME as something where ( something.caseNumber=:param0 ) and ( something.caseStatusByCaseStatusId=1 or something.caseStatusByCaseStatusId=-1 or something.caseStatusByCaseStatusId=1 or something.caseStatusByCaseStatusId=9 or something.caseStatusByCaseStatusId=4 or something.caseStatusByCaseStatusId=5 or something.caseStatusByCaseStatusId=10 or something.caseStatusByCaseStatusId=2 ) order by something.someCriteria desc

如何处理我的代码以获得预期的查询结果?如果您需要其他详细信息，我将立即更新thx。

Answer 1

我自己还没有测试过下面的代码片段。但这应该是如何构造条件查询的方法：

 Predicate andCriteria = cb.conjunction();

if (params.getEqColumns() != null && params.getEqValues() != null) {
    andCriteria = cb.and(andCriteria, cb.equal(...);
    // ... furhter andCriteria
} else if (params.getLikeColumns() != null && params.getLikeValues() != null) {
    andCriteria = cb.and(andCriteria, cb.like(...);
    // ... furhter andCriteria
} else {  
    if(params.getCaseNumber() != null){
        andCriteria = cb.and(andCriteria, cb.equal(root.get("caseNumber"), params.getCaseNumber()));
    }
    if (params.getCaseStatusIds() != null) {
        List<String> caseStatusIds = new ArrayList<String>();
        caseStatusIds = params.getCaseStatusIds();
        Predicate orCriteria = cb.disjunction();
        for (int i=0 ; i < caseStatusIds.size() ; i++) {
            orCriteria = cb.or(orCriteria, cb.equal(root.get("caseStatusByCaseStatusId"), Integer.valueOf(params.getCaseStatusIds().get(i))));
        }
        andCriteria = cb.and(andCriteria, orCriteria);
    }
}

cq.select(root).where(andCriteria);

请注意，您必须调整getEqParamsCriteria()和getLikeParamsCriteria()中的代码。