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

Question

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

I need to adapt the following code example.

I have a MySQL query that looks like this (2015-05-04 and 2015-05-06 are dynamic and symbolize the 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 have a bookings table and a cars table. I would like to know which car is available in the time range. SQL query works like a charm.

I would like to "convert" this to the output of CriteriaBuilder . I read the documentation in the last 3 hours with this output (which obviously does not work). And I even skipped those parts in the subqueries.

 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 problem: fkCarId not defined as a foreign key, it is just an integer. Any way to make it fixed this way?

+7

java mysql jpa criteria

Steven x Apr 18 '15 at 15:31

source share

2 answers

It will work faster if you follow 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 will still not be very effective, as it will have to scan all cars , and then reach bookings once per.

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

If CriteriaBuilder cannot build it, complain about them or get them out of your way.

Flipping around can work faster:

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

In this statement, I hope to do a table scan on bookings , filter out the reserved characters, and then d only reach cars for the necessary rows. This can be especially fast if there are very few cars available.

+2

Rick james Apr 21 '15 at 2:27

source share

Tiny · Accepted Answer · 2015-04-22T00:27:32+0000

I created the following two tables in a MySQL database with only the required 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 the primary key, and fk_car_id is the foreign key that refers to the primary key ( car_id ) of the cars table.

The corresponding JPA criteria query using the IN() subquery is 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(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 the final version of Hibernate 4.3.6, but in this context there should be no discrepancies in the middle ORM structures).

 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<=? )

_{The brackets around conditional expressions in the WHERE above query are technically redundant, which are only necessary for better readability, which Hibernate ignores.} _{Hibernate should not take them into account.}

I personally, however, prefer to use the EXISTS statement. Accordingly, the request can be restored 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))

Returns the same list of results.

Additionally:

Here subquery.select(criteriaBuilder.literal(1L)); Using expressions like criteriaBuilder.literal(1L) in complex subquery statements on EclipseLink, EclipseLink gets confused and throws an exception. Therefore, perhaps this should be considered when writing complex subqueries in EclipseLink. Just select id in this case e.g.

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

as in the first case. Note: in the generation of SQL queries, you will see odd behavior if you run the expression as described above in EclipseLink, although the list of results will be identical.

You can also use joins that are more efficient for server database systems, in which case you need to use DISTINCT to filter possible duplicate rows, since you need a list of results from the parent table. The list of results may contain duplicate rows if the detailed table contains more than one child row - bookings for the corresponding parent row cars . ~~I leave it to you. :)~~ This is how it happens.

 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 you can see, the Hibernate provider accesses conditional statements in the WHERE in response to WHERE NOT(...) . Other providers can also generate exact WHERE NOT(...) , but in the end it is the same as the one written in the question and gives the same list of results as in the previous cases.

_{The correct connections are not indicated.} _{Therefore, JPA providers should not implement them.} _{Most of them do not support the correct connection.}

Corresponding JPQL just for the sake of completeness :)

Request IN() :

 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 <=? )

Request EXISTS() :

 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 appropriate indexed / positional parameters as necessary and required.

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

I have always avoided the IN() subqueries in such situations and especially during using MySQL. I would use IN() subqueries if and only if they are absolutely necessary for situations, for example, when we need to define 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); 
```
etc.
I always prefer queries using the EXISTS operator in such situations, since the list of results includes only one table, based on a condition in another table (s). joins in this case duplicate lines will be produced, as mentioned earlier, which must be filtered using DISTINCT , as shown in one of the queries above.
I would prefer to use joins when the result set to be restored, joins from several database tables, should still be used as obvious.

In any case, it all depends on many things. This is not a milestone.

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

_{Note: I used the parameterized / overloaded obsolete date constructor - Date(String s) for indexed / positional parameters associated with the SQL query in all cases for the pure purpose of testing, only to avoid all the jumble of java.util.SimpleDateFormat noise that you already you know.} _{You can also use other better APIs such as Joda Time (Hibernate supports it), java.sql.* (These are subclasses of java.util.Date ), Java Time in Java 8 (most often they are not supported, unless configured) as and when required / necessary.}

Hope this helps.

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

More articles: