jpa criteriabuilder:listjoin with in query on joined cloumn

imzjd6km  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(357)

我有一个用例,需要在一个值列表上搜索一个表。模式如下:
病例表
案例id
病例对照表
案例id
cin公司
case&case\ cin表通过多对多连接。
我需要根据提供的CIN列表搜索案例。这是我尝试实现的sql:

select distinct c.* from case c left join case_cin cc on c.case_id = cc.case_id where cc.cin in ("cin1", "cin2", "cin3");

我就是这样根据一个cin来设计我的过滤标准的:

public static Specification<CaseEntity> buildSpecification(CaseSearchRequestDto criteria, String userName) {
    return (root, query, cb) -> {
        List<Predicate> predicates = new ArrayList<>();

        if (!isEmpty(criteria.getAssociatePartyCins())) {
            predicates.add(buildAssociatePartyCinsFilterPredicate(root, cb, query, criteria.getAssociatePartyCins());
        }

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

private static Predicate buildAssociatePartyCinsFilterPredicate(Root<CaseEntity> root, CriteriaBuilder cb, CriteriaQuery query, List<String> cins) {
    Predicate filterPredicate = cb.disjunction();

    ListJoin<CaseEntity, String> cinsJoin = root.joinList(FIELD_CASE_CINS, LEFT);
    filterPredicate.getExpressions().add(startWithString(cinsJoin, cb,**cins.get(0)**); // need to change logic here.

    query.distinct(true);
    return filterPredicate;
}

我也希望每个cin都能精确匹配 startWithString .
有人能帮助修改代码以允许按多个值搜索吗?

yk9xbfzb

yk9xbfzb1#

结果比我想象的简单多了。只需要用 cinsJoin.in(cins) .

private static Predicate buildAssociatePartyCinsFilterPredicate(Root<CaseEntity> root, CriteriaBuilder cb, CriteriaQuery query, List<String> cins) {
    Predicate filterPredicate = cb.disjunction();

    ListJoin<CaseEntity, String> cinsJoin = root.joinList(FIELD_CASE_CINS, LEFT);
    filterPredicate.getExpressions().add(cinsJoin.in(cins));

    query.distinct(true);
    return filterPredicate;
}

相关问题