Search code examples
javajpa-2.0java-ee-6criteria-api

How to use Criteria API objects for multiple and/or conditions

I'm having problems with multiple and/or conditions. Here's the situation, I have WorkTime entity with user, dateFrom and dateTo fields and User entity with organizationUnit field. I'm constructing following query to get work time between dates including those that overlap these dates:

// main condition to get only users from given organizational unit
Predicate restriction = root.get(WorkTime_.user).get(User_.organizationalUnit).in((Object[]) orgUnits);

// first date condition to get work time between border dates
Predicate dateInRestr = builder.greaterThanOrEqualTo(root.get(WorkTime_.dateFrom), dateFrom);
dateInRestr = builder.and(dateInRestr, builder.lessThanOrEqualTo(root.get(WorkTime_.dateTo), dateTo));

// second date condition to get work time overlap border dates 
ParameterExpression<Date> dateFromParam = builder.parameter(Date.class);
ParameterExpression<Date> dateToParam = builder.parameter(Date.class);
Predicate dateOutRestr = builder.between(dateFromParam, root.get(WorkTime_.dateFrom), root.get(WorkTime_.dateTo));
dateOutRestr = builder.or(dateOutRestr, builder.between(dateToParam, root.get(WorkTime_.dateFrom), root.get(WorkTime_.dateTo)));

// OR for dates condition 
Predicate dateRestr = builder.or(dateInRestr, dateOutRestr);
// AND with unit condition
query.where(restriction, dateRestr);

Generally it work OK, yet resulting query in PostgreSQL looks like this (only showing the condition part):

and (user1_.ORG_UNIT_ID in (? , ? , ? , ? , ? , ?)) 
and (worktime0_.DATEFROM>=? and worktime0_.DATETO<=? 
    or ? between worktime0_.DATEFROM and worktime0_.DATETO
    or ? between worktime0_.DATEFROM and worktime0_.DATETO)

Is there a chance I can add additional parenthesis around OR condition elements, so that the query would look like this:

and (user1_.ORG_UNIT_ID in (? , ? , ? , ? , ? , ?)) 
and ((worktime0_.DATEFROM>=? and worktime0_.DATETO<=?) 
    or (? between worktime0_.DATEFROM and worktime0_.DATETO)
    or (? between worktime0_.DATEFROM and worktime0_.DATETO))

Or it doesn't matter from SQL point of view? I'm concerned that it won't work on other RDMBS.

Solution

  • The Criteria API has returned for you valid SQL that works. It has added parentheses where it needs to and from my perusal I can not see that it would cause on trouble on any other RDBMS.