0

I am trying to build the CriteriaQuery equivalent of the following SQL where clause, using OpenJPA 2.4.0:

where employee_status is null or employee_status not in ('Inactive','Terminated')

I have created a list of employeeStatuses, and this is how I am adding the predicate:

predicates.add(
   criteriaBuilder.or(
      criteriaBuilder.isNull(domainEntity.get("employeeStatus")),   
      criteriaBuilder.not(domainEntity.get("employeeStatus").in(employeeStatuses))
   )
);

The generated SQL looks like this:

AND (t0.EMPLOYEE_STATUS IS NULL OR NOT (t0.EMPLOYEE_STATUS = ? OR t0.EMPLOYEE_STATUS = ?) AND t0.EMPLOYEE_STATUS IS NOT NULL)

As you can see, the 'not in' statement is being transformed into multiple comparisons, with 't0.EMPLOYEE_STATUS IS NOT NULL' added at the end. In order for this to work, the translated clause should be contained in another set of parenthesis.

Any ideas about how I can get this to work?

D.E.
  • 1

0 Answers0