If/Case statement in JPA Criteria Builder

Question

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

Solution 1

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.

Solution 2

I think what you want to do is use Case<Date>, and use Predicate only as first argument to Case#when. Case<Date> is an Expression<Date>, which is what you want to pass into CriteriaQuery#multiselect.

CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = query.from(Employee.class);
query.multiselect
(
    criteriaBuilder.selectCase()
        .when(criteriaBuilder.isNull(employee.get("contract")), employee.get("creationDate"))
        .otherwise(employee.join("contract").get("fromDate"))
);

Comments

[RNJ]

Hi JMelnik, Thanks for the reply. THe problem I have with this code is the selectCase returns an expression and not a predicate. It is actually returning predicates so tried to case the whole thing to predicate. Unfortunately it still does not compile which I find odd because I am casting. Do you know what this could be? Thanks

[d1e]

@user846476 I think what we need to do is compare Date with date in Case statement.

[Jelle Blaauw]

Perfect, thanks for pointing me to this. This should be the accepted answer.