Implement search by COALESCE of two columns via Hibernate criteria query

Question

I'm trying to convert below query in CriteriaBuilder:

select * from bau_case where coalesce(actual_category, suggested_category ) = 'TC_1'; 

All examples that I've seen so far are based on COALESCEing the search value instead of search fields and hence are not of much use. This is what I'm stuck at:

static Predicate buildCaseCategoryPredicate(Root<BauCaseEntity> root, CriteriaBuilder cb, RefDataDto caseCategory) {
    CriteriaBuilder.Coalesce<String> coalesce = cb.coalesce();
    coalesce.value(root.get(FIELD_ACTUAL_CASE_CATEGORY));
    coalesce.value(root.get(FIELD_SUGGESTED_CASE_CATEGORY));
    c.select(coalesce);
       ????
}

Answer 1

You can try to use something like this:

static Predicate buildCaseCategoryPredicate(Root<BauCaseEntity> root, CriteriaBuilder cb, RefDataDto caseCategory) {
    return cb.equal(
       cb.coalesce(
          root.get(FIELD_ACTUAL_CASE_CATEGORY),
          root.get(FIELD_SUGGESTED_CASE_CATEGORY)
       ),
       "TC_1"
    );
}