java - JPA CriteriaBuilder: ListJoin with IN query on joined cloumn

Question

I have a use-case where I need to search a table on a list of values. Below is the schema:

CASE table

Case_Id

CASE_CIN table

Case_Id

cin

CASE & CASE_CIN table are joined via Many to Many.

I need to search cases based on provided list of CINs. This is the SQL that I'm trying to implement:

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");

This is how I designed my filter criteria based on just a single 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;
}

I'd also like to have the exact match for every CIN rather that startWithString.

Can anyone help modify the code to allow search by multiple values?

question from:https://stackoverflow.com/questions/65847954/jpa-criteriabuilder-listjoin-with-in-query-on-joined-cloumn

Answer 1

It turns out to be way simpler than I thought. Just need to replace the condition with 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;
}