Combining conditional expressions with "AND" and "OR" predicates using the JPA criteria API

Question

I need to adapt the following code example.

I've got a MySQL query, which looks like this (2015-05-04 and 2015-05-06 are dynamic and symbolize a time range)

SELECT * FROM cars c WHERE c.id NOT IN ( SELECT fkCarId FROM bookings WHERE 
    (fromDate <= '2015-05-04' AND toDate >= '2015-05-04') OR
    (fromDate <= '2015-05-06' AND toDate >= '2015-05-06') OR
    (fromDate >= '2015-05-04' AND toDate <= '2015-05-06'))

I've got a bookings table, and a cars table. I'd like to find out which car is available in a time range. The SQL query works like a charm.

I'd like to "convert" this one into a CriteriaBuilder output. I've read documentation during the last 3 hours with this output (which, obviously, does not work). And I even skipped the where parts in the sub queries.

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Cars> query = cb.createQuery(Cars.class);
Root<Cars> poRoot = query.from(Cars.class);
query.select(poRoot);

Subquery<Bookings> subquery = query.subquery(Bookings.class);
Root<Bookings> subRoot = subquery.from(Bookings.class);
subquery.select(subRoot);
Predicate p = cb.equal(subRoot.get(Bookings_.fkCarId),poRoot);
subquery.where(p);

TypedQuery<Cars> typedQuery = getEntityManager().createQuery(query);

List<Cars> result = typedQuery.getResultList();

Another issue: the fkCarId is not defined as a foreign key, it's just an integer. Any way to get it fixed that way?

Solution 1

I have created the following two tables in MySQL database with only the necessary fields.

mysql> desc cars;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| car_id       | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| manufacturer | varchar(100)        | YES  |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> desc bookings;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| booking_id | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| fk_car_id  | bigint(20) unsigned | NO   | MUL | NULL    |                |
| from_date  | date                | YES  |     | NULL    |                |
| to_date    | date                | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

booking_id in the bookings table is a primary key and fk_car_id is a foreign key that references the primary key (car_id) of the cars table.

---

The corresponding JPA criteria query using an IN() sub-query goes like the following.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Cars> criteriaQuery = criteriaBuilder.createQuery(Cars.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<Cars> root = criteriaQuery.from(metamodel.entity(Cars.class));

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Bookings> subRoot = subquery.from(metamodel.entity(Bookings.class));
subquery.select(subRoot.get(Bookings_.fkCarId).get(Cars_.carId));

List<Predicate> predicates = new ArrayList<Predicate>();

ParameterExpression<Date> fromDate1 = criteriaBuilder.parameter(Date.class);
Predicate exp1 = criteriaBuilder.lessThanOrEqualTo(subRoot.get(Bookings_.fromDate), fromDate1);
ParameterExpression<Date> toDate1 = criteriaBuilder.parameter(Date.class);
Predicate exp2 = criteriaBuilder.greaterThanOrEqualTo(subRoot.get(Bookings_.toDate), toDate1);
Predicate and1 = criteriaBuilder.and(exp1, exp2);

ParameterExpression<Date> fromDate2 = criteriaBuilder.parameter(Date.class);
Predicate exp3 = criteriaBuilder.lessThanOrEqualTo(subRoot.get(Bookings_.fromDate), fromDate2);
ParameterExpression<Date> toDate2 = criteriaBuilder.parameter(Date.class);
Predicate exp4 = criteriaBuilder.greaterThanOrEqualTo(subRoot.get(Bookings_.toDate), toDate2);
Predicate and2 = criteriaBuilder.and(exp3, exp4);

ParameterExpression<Date> fromDate3 = criteriaBuilder.parameter(Date.class);
Predicate exp5 = criteriaBuilder.greaterThanOrEqualTo(subRoot.get(Bookings_.fromDate), fromDate3);
ParameterExpression<Date> toDate3 = criteriaBuilder.parameter(Date.class);
Predicate exp6 = criteriaBuilder.lessThanOrEqualTo(subRoot.get(Bookings_.toDate), toDate3);
Predicate and3 = criteriaBuilder.and(exp5, exp6);

Predicate or = criteriaBuilder.or(and1, and2, and3);
predicates.add(or);
subquery.where(predicates.toArray(new Predicate[0]));

criteriaQuery.where(criteriaBuilder.in(root.get(Cars_.carId)).value(subquery).not());

List<Cars> list = entityManager.createQuery(criteriaQuery)
        .setParameter(fromDate1, new Date("2015/05/04"))
        .setParameter(toDate1, new Date("2015/05/04"))
        .setParameter(fromDate2, new Date("2015/05/06"))
        .setParameter(toDate2, new Date("2015/05/06"))
        .setParameter(fromDate3, new Date("2015/05/04"))
        .setParameter(toDate3, new Date("2015/05/06"))
        .getResultList();

It produces the following SQL query of your interest (tested on Hibernate 4.3.6 final but there should not be any discrepancy on average ORM frameworks in this context).

SELECT
    cars0_.car_id AS car_id1_7_,
    cars0_.manufacturer AS manufact2_7_ 
FROM
    project.cars cars0_ 
WHERE
    cars0_.car_id NOT IN  (
        SELECT
            bookings1_.fk_car_id 
        FROM
            project.bookings bookings1_ 
        WHERE
            bookings1_.from_date<=? 
            AND bookings1_.to_date>=? 
            OR bookings1_.from_date<=? 
            AND bookings1_.to_date>=? 
            OR bookings1_.from_date>=? 
            AND bookings1_.to_date<=?
    )

_{Brackets around the conditional expressions in the WHERE clause of the above query are technically utterly superfluous which are only needed for better a readability which Hibernate disregards - Hibernate does not have to take them into consideration.}

---

I personally however, prefer to use the EXISTS operator. Accordingly, the query can be reconstructed as follows.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Cars> criteriaQuery = criteriaBuilder.createQuery(Cars.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<Cars> root = criteriaQuery.from(metamodel.entity(Cars.class));

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Bookings> subRoot = subquery.from(metamodel.entity(Bookings.class));
subquery.select(criteriaBuilder.literal(1L));

List<Predicate> predicates = new ArrayList<Predicate>();

ParameterExpression<Date> fromDate1 = criteriaBuilder.parameter(Date.class);
Predicate exp1 = criteriaBuilder.lessThanOrEqualTo(subRoot.get(Bookings_.fromDate), fromDate1);
ParameterExpression<Date> toDate1 = criteriaBuilder.parameter(Date.class);
Predicate exp2 = criteriaBuilder.greaterThanOrEqualTo(subRoot.get(Bookings_.toDate), toDate1);
Predicate and1 = criteriaBuilder.and(exp1, exp2);

ParameterExpression<Date> fromDate2 = criteriaBuilder.parameter(Date.class);
Predicate exp3 = criteriaBuilder.lessThanOrEqualTo(subRoot.get(Bookings_.fromDate), fromDate2);
ParameterExpression<Date> toDate2 = criteriaBuilder.parameter(Date.class);
Predicate exp4 = criteriaBuilder.greaterThanOrEqualTo(subRoot.get(Bookings_.toDate), toDate2);
Predicate and2 = criteriaBuilder.and(exp3, exp4);

ParameterExpression<Date> fromDate3 = criteriaBuilder.parameter(Date.class);
Predicate exp5 = criteriaBuilder.greaterThanOrEqualTo(subRoot.get(Bookings_.fromDate), fromDate3);
ParameterExpression<Date> toDate3 = criteriaBuilder.parameter(Date.class);
Predicate exp6 = criteriaBuilder.lessThanOrEqualTo(subRoot.get(Bookings_.toDate), toDate3);
Predicate and3 = criteriaBuilder.and(exp5, exp6);

Predicate equal = criteriaBuilder.equal(root, subRoot.get(Bookings_.fkCarId));
Predicate or = criteriaBuilder.or(and1, and2, and3);
predicates.add(criteriaBuilder.and(or, equal));
subquery.where(predicates.toArray(new Predicate[0]));

criteriaQuery.where(criteriaBuilder.exists(subquery).not());

List<Cars> list = entityManager.createQuery(criteriaQuery)
        .setParameter(fromDate1, new Date("2015/05/04"))
        .setParameter(toDate1, new Date("2015/05/04"))
        .setParameter(fromDate2, new Date("2015/05/06"))
        .setParameter(toDate2, new Date("2015/05/06"))
        .setParameter(fromDate3, new Date("2015/05/04"))
        .setParameter(toDate3, new Date("2015/05/06"))
        .getResultList();

It produces the following SQL query.

SELECT
    cars0_.car_id AS car_id1_7_,
    cars0_.manufacturer AS manufact2_7_ 
FROM
    project.cars cars0_ 
WHERE
    NOT (EXISTS (SELECT
        1 
    FROM
        project.bookings bookings1_ 
    WHERE
        (bookings1_.from_date<=? 
        AND bookings1_.to_date>=? 
        OR bookings1_.from_date<=? 
        AND bookings1_.to_date>=? 
        OR bookings1_.from_date>=? 
        AND bookings1_.to_date<=?) 
        AND cars0_.car_id=bookings1_.fk_car_id))

Which returns the same result list.

---

Additional:

Here subquery.select(criteriaBuilder.literal(1L));, while using expressions like criteriaBuilder.literal(1L) in complex sub-query statements on EclipseLink, EclipseLink gets confused and causes an exception. Therefore, it may need to be taken into account while writing complex sub-queries on EclipseLink. Just select an id in that case such as

subquery.select(subRoot.get(Bookings_.fkCarId).get(Cars_.carId));

as in the first case. Note : You will see an odd behaviour in SQL query generation, if you run an expression as above on EclipseLink though the result list will be identical.

You may also use joins which turn out to be more efficient on back-end database systems in which case, you need to use DISTINCT to filter out possible duplicate rows, since you need a result list from the parent table. The result list may contain duplicate rows, if there exists more than one child row in the detailed table - bookings for a corresponding parent row cars. I am leaving it to you. :) This is how it goes here.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Cars> criteriaQuery = criteriaBuilder.createQuery(Cars.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<Cars> root = criteriaQuery.from(metamodel.entity(Cars.class));
criteriaQuery.select(root).distinct(true);

ListJoin<Cars, Bookings> join = root.join(Cars_.bookingsList, JoinType.LEFT);

ParameterExpression<Date> fromDate1 = criteriaBuilder.parameter(Date.class);
Predicate exp1 = criteriaBuilder.lessThanOrEqualTo(join.get(Bookings_.fromDate), fromDate1);
ParameterExpression<Date> toDate1 = criteriaBuilder.parameter(Date.class);
Predicate exp2 = criteriaBuilder.greaterThanOrEqualTo(join.get(Bookings_.toDate), toDate1);
Predicate and1 = criteriaBuilder.and(exp1, exp2);

ParameterExpression<Date> fromDate2 = criteriaBuilder.parameter(Date.class);
Predicate exp3 = criteriaBuilder.lessThanOrEqualTo(join.get(Bookings_.fromDate), fromDate2);
ParameterExpression<Date> toDate2 = criteriaBuilder.parameter(Date.class);
Predicate exp4 = criteriaBuilder.greaterThanOrEqualTo(join.get(Bookings_.toDate), toDate2);
Predicate and2 = criteriaBuilder.and(exp3, exp4);

ParameterExpression<Date> fromDate3 = criteriaBuilder.parameter(Date.class);
Predicate exp5 = criteriaBuilder.greaterThanOrEqualTo(join.get(Bookings_.fromDate), fromDate3);
ParameterExpression<Date> toDate3 = criteriaBuilder.parameter(Date.class);
Predicate exp6 = criteriaBuilder.lessThanOrEqualTo(join.get(Bookings_.toDate), toDate3);
Predicate and3 = criteriaBuilder.and(exp5, exp6);

Predicate or = criteriaBuilder.not(criteriaBuilder.or(and1, and2, and3));
Predicate isNull = criteriaBuilder.or(criteriaBuilder.isNull(join.get(Bookings_.fkCarId)));
criteriaQuery.where(criteriaBuilder.or(or, isNull));

List<Cars> list = entityManager.createQuery(criteriaQuery)
        .setParameter(fromDate1, new Date("2015/05/04"))
        .setParameter(toDate1, new Date("2015/05/04"))
        .setParameter(fromDate2, new Date("2015/05/06"))
        .setParameter(toDate2, new Date("2015/05/06"))
        .setParameter(fromDate3, new Date("2015/05/04"))
        .setParameter(toDate3, new Date("2015/05/06"))
        .getResultList();

It produces the following SQL query.

SELECT
    DISTINCT cars0_.car_id AS car_id1_7_,
    cars0_.manufacturer AS manufact2_7_ 
FROM
    project.cars cars0_ 
LEFT OUTER JOIN
    project.bookings bookingsli1_ 
        ON cars0_.car_id=bookingsli1_.fk_car_id 
WHERE
    (
        bookingsli1_.from_date>? 
        OR bookingsli1_.to_date<?
    ) 
    AND (
        bookingsli1_.from_date>? 
        OR bookingsli1_.to_date<?
    ) 
    AND (
        bookingsli1_.from_date<? 
        OR bookingsli1_.to_date>?
    ) 
    OR bookingsli1_.fk_car_id IS NULL

As can be noticed the Hibernate provider inverses the conditional statements in the WHERE clause in response to WHERE NOT(...). Other providers may also generate the exact WHERE NOT(...) but after all, this is the same as the one written in the question and yields the same result list as in the previous cases.

_{Right joins are not specified. Hence, JPA providers do not have to implement them. Most of them do not support right joins.}

---

Respective JPQL just for the sake of completeness :)

The IN() query :

SELECT c 
FROM   cars AS c 
WHERE  c.carid NOT IN (SELECT b.fkcarid.carid 
                       FROM   bookings AS b 
                       WHERE  b.fromdate <=? 
                              AND b.todate >=? 
                              OR b.fromdate <=? 
                                 AND b.todate >=? 
                              OR b.fromdate >=? 
                                 AND b.todate <=? ) 

The EXISTS() query :

SELECT c 
FROM   cars AS c 
WHERE  NOT ( EXISTS (SELECT 1 
                     FROM   bookings AS b 
                     WHERE  ( b.fromdate <=? 
                              AND b.todate >=? 
                              OR b.fromdate <=? 
                                 AND b.todate >=? 
                              OR b.fromdate >=? 
                                 AND b.todate <=? ) 
                            AND c.carid = b.fkcarid) ) 

The last one that uses the left join (with named parameters):

SELECT DISTINCT c FROM Cars AS c 
LEFT JOIN c.bookingsList AS b 
WHERE NOT (b.fromDate <=:d1 AND b.toDate >=:d2
           OR b.fromDate <=:d3 AND b.toDate >=:d4
           OR b.fromDate >=:d5 AND b.toDate <=:d6)
           OR b.fkCarId IS NULL

All of the above JPQL statements can be run using the following method as you already know.

List<Cars> list=entityManager.createQuery("Put any of the above statements", Cars.class)
                .setParameter("d1", new Date("2015/05/04"))
                .setParameter("d2", new Date("2015/05/04"))
                .setParameter("d3", new Date("2015/05/06"))
                .setParameter("d4", new Date("2015/05/06"))
                .setParameter("d5", new Date("2015/05/04"))
                .setParameter("d6", new Date("2015/05/06"))
                .getResultList();

Replace named parameters with corresponding indexed/positional parameters as and when needed/required.

All of these JPQL statements also generate the identical SQL statements as those generated by the criteria API as above.

---

• I would always avoid IN() sub-queries in such situations and especially while using MySQL. I would use IN() sub-queries if and only if they are absolutely needed for situations such as when we need to determine a result set or delete a list of rows based on a list of static values such as

  SELECT * FROM table_name WHERE id IN (1, 2, 3, 4, 5);`
  DELETE FROM table_name WHERE id IN(1, 2, 3, 4, 5);
  

  and alike.

• I would always prefer queries using the EXISTS operator in such situations, since the result list involves only a single table based on a condition in another table(s). Joins in this case will produce duplicate rows as mentioned earlier that need to be filtered out using DISTINCT as shown in one of the queries above.

• I would preferably use joins, when the result set to be retrieved is combined from multiple database tables - have to be used anyway as obvious.

Everything is dependent upon many things after all. Those are not milestones at all.

_{Disclaimer : I have a very little knowledge on RDBMS.}

---

_{Note : I have used the parameterized/overloaded deprecated date constructor - Date(String s) for indexed/positional parameters associated with the SQL query in all the cases for a pure testing purpose only to avoid the whole mess of java.util.SimpleDateFormat noise which you already know. You may also use other better APIs like Joda Time (Hibernate has support for it), java.sql.* (those are sub-classes of java.util.Date), Java Time in Java 8 (mostly not supported as of now unless customized) as and when required/needed.}

Hope that helps.

Solution 2

It will run faster if you do this format:

SELECT c.*
    FROM cars c
    LEFT JOIN bookings b 
             ON b.fkCarId = c.id
            AND (b.fromDate ... )
    WHERE b.fkCarId IS NULL;

This form still won't be very efficient since it will have to scan all cars, then reach into bookings once per.

You do need an index on fkCarId. "fk" smells like it is a FOREIGN KEY, which implies an index. Please provide SHOW CREATE TABLE for confirmation.

If CriteriaBuilder can't build that, complain to them or get it out of your way.

Flipping it around might run faster:

SELECT c.*
    FROM bookings b 
    JOIN cars c
           ON b.fkCarId = c.id
    WHERE NOT (b.fromDate ... );

In this formulation, I am hoping to do a table scan on bookings, filtering out the reserved chars, an d only then reach into cars for the desired rows. This might be especially faster if there are very few available cars.