If/Case statement in JPA Criteria Builder

criteria-api, java, jpa, jpa-2.0

Solution

Here would be a solution, not sure if it works, but we will manage to get it work with your help :):

ParameterExpression<Date> inputDateExp = criteriaBuilder.parameter(Date.class, "inputDate");
Predicate employeeCreationDate = criteriaBuilder.lessThanOrEqualTo(
        employee.<Date>get("creationDate"), inputDateExp);
Predicate contractStartDate = criteriaBuilder.lessThanOrEqualTo(
        employee.join("contract").<Date>get("fromDate"), inputDateExp);

criteria.add(
        criteriaBuilder.selectCase().when(employee.get("contract").isNull(), employeeCreationDate).otherwise(contractStartDate));

I do not understand why use "inputDate" as Expression instead of a Date? Also, I suggest renaming `criteria` to `c` and `criteriaBuilder` to `cb`, this would save space and I think it is more comfortable.

Problem

I want to build up a query which search dates on different entites. My structure is: - Contract has date (non nullable) - Employee has date (non nullable) - Employee may have a contract id (nullable) If a Employee has a contract I want to retrieve the contract date. If an employee does not have a contract then I want to return the Employee date. My code so far is: ``` if (inputDate!= null) { ParameterExpression<Date> exp = criteriaBuilder.parameter(Date.class, "inputDate"); criteria.add(criteriaBuilder.or( criteriaBuilder.isNull(employee.get("contract")), criteriaBuilder.lessThanOrEqualTo(employee.<Date>get("creationDate"), exp), criteriaBuilder.lessThanOrEqualTo((employee.join("contract").<Date>get("fromDate")), exp) ));} ``` This does not seem to work though. I always appear to go into the isNull which I do not expect. I am happy to look into this some more but I guess my question is whether this is the correct way of going about it. Is it? I have seen a selectCase as well in criteriaBuilder so perhaps that may be a better solution. Any pointers would be greatly received. Thanks

Original source