Combining conditional expressions with "AND" and "OR" predicates using the JPA criteria API
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?
Answer
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 useIN()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 asSELECT * 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
EXISTSoperator 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 usingDISTINCTas 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.