Java 使用 JPA 条件 API 将条件表达式与“AND”和“OR”谓词组合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29719321/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combining conditional expressions with "AND" and "OR" predicates using the JPA criteria API
提问by Steven X
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)
我有一个 MySQL 查询，它看起来像这样（2015-05-04 和 2015-05-06 是动态的，象征一个时间范围）
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 bookingstable, and a carstable. I'd like to find out which car is available in a time range. The SQL query works like a charm.
我有一张bookings桌子，还有一张cars桌子。我想知道在某个时间范围内哪辆车可用。SQL 查询就像一个魅力。
I'd like to "convert" this one into a CriteriaBuilderoutput. 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输出。我在过去 3 小时内使用此输出阅读了文档（显然，这不起作用）。我什至跳过了子查询中的 where 部分。
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 fkCarIdis not defined as a foreign key, it's just an integer. Any way to get it fixed that way?
另一个问题：fkCarId没有定义为外键，它只是一个整数。有什么办法可以把它修好？
采纳答案by Tiny
I have created the following two tables in MySQL database with only the necessary fields.
我在 MySQL 数据库中创建了以下两个表，只有必要的字段。
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_idin the bookingstable is a primary key and fk_car_idis a foreign key that references the primary key (car_id) of the carstable.
booking_id在bookings表是一个主键，fk_car_id是一个外键引用的主键（car_id中的）cars表。
The corresponding JPA criteria query using an IN()sub-query goes like the following.
使用IN()子查询的相应 JPA 条件查询如下所示。
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).
它会生成您感兴趣的以下 SQL 查询（在 Hibernate 4.3.6 final 上测试，但在此上下文中平均 ORM 框架不应有任何差异）。
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 WHEREclause 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.
WHERE上述查询子句中条件表达式周围的括号在技术上完全是多余的，只是为了提高 Hibernate 忽略的可读性才需要 - Hibernate 不必考虑它们。
I personally however, prefer to use the EXISTSoperator. Accordingly, the query can be reconstructed as follows.
然而，我个人更喜欢使用EXISTS运营商。因此，可以如下重构查询。
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.
它生成以下 SQL 查询。
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 confusedand causes an exception. Therefore, it may need to be taken into account while writing complex sub-queries on EclipseLink. Just select an idin that case such as
在这里subquery.select(criteriaBuilder.literal(1L));，当criteriaBuilder.literal(1L)在 EclipseLink 上使用类似复杂子查询语句的表达式时，EclipseLink会感到困惑并导致异常。因此，在 EclipseLink 上编写复杂的子查询时可能需要考虑到这一点。id在这种情况下只需选择一个，例如
subquery.select(subRoot.get(Bookings_.fkCarId).get(Cars_.carId));
as in the first case. Note : You will see an odd behaviourin SQL query generation, if you run an expression as above on EclipseLink though the result list will be identical.
如第一种情况。注意：如果您在 EclipseLink 上运行上述表达式，您会在 SQL 查询生成中看到一个奇怪的行为，尽管结果列表将是相同的。
You may also use joins which turn out to be more efficient on back-end database systems in which case, you need to use DISTINCTto 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 - bookingsfor a corresponding parent row cars. I am leaving it to you. :)This is how it goes here.
您还可以使用在后端数据库系统上更有效的连接，在这种情况下，您需要使用DISTINCT来过滤掉可能的重复行，因为您需要来自父表的结果列表。结果列表可能包含重复行，如果明细表中存在多个子行 -bookings对应的父行 cars。我把它留给你。:)这就是它在这里的方式。
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.
它生成以下 SQL 查询。
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 WHEREclause 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.
可以注意到，Hibernate 提供程序反转了WHERE子句中的条件语句以响应WHERE NOT(...). 其他提供者也可能生成精确的，WHERE NOT(...)但毕竟，这与问题中所写的相同，并产生与前一种情况相同的结果列表。
Right joins are not specified. Hence, JPA providers do not have to implement them. Most of them do not support right joins.
未指定右连接。因此，JPA 提供者不必实现它们。它们中的大多数不支持右连接。
Respective JPQL just for the sake of completeness :)
各自的 JPQL 只是为了完整性:)
The IN()query :
该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 <=? )
The EXISTS()query :
该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.
如您所知，上述所有 JPQL 语句都可以使用以下方法运行。
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.
所有这些 JPQL 语句也生成与上述标准 API 生成的 SQL 语句相同的 SQL 语句。
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.
IN()在这种情况下，尤其是在使用 MySQL 时，我总是会避免使用子查询。IN()当且仅当在我们需要确定结果集或删除基于静态值列表的行列表等情况下绝对需要它们时，我才会使用子查询，例如SELECT * FROM table_name WHERE id IN (1, 2, 3, 4, 5);` DELETE FROM table_name WHERE id IN(1, 2, 3, 4, 5);和一样。
EXISTS在这种情况下，我总是更喜欢使用运算符进行查询，因为结果列表只涉及基于另一个表中条件的单个表。在这种情况下，联接将产生前面提到的重复行，需要使用DISTINCT上述查询之一来过滤掉这些行。- 当要检索的结果集从多个数据库表中组合时，我最好使用连接 - 无论如何都必须使用很明显。
Everything is dependent upon many things after all. Those are not milestones at all.
毕竟，一切都取决于很多事情。这些根本不是里程碑。
Disclaimer :I have a very little knowledge on RDBMS.
免责声明：我对 RDBMS 知之甚少。
Note :I have used the parameterized/overloaded deprecateddate 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.SimpleDateFormatnoise 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.
注意：我使用了参数化/重载的弃用日期构造函数 - Date(String s)在所有情况下用于与 SQL 查询相关的索引/位置参数，仅用于纯粹的测试目的，只是为了避免java.util.SimpleDateFormat您已经知道的一团乱麻。您还可以在需要/需要时使用其他更好的 API，例如 Joda Time（Hibernate 支持它）、java.sql.*（那些是 的子类java.util.Date）、Java 8 中的 Java Time（目前大多数情况下不支持，除非定制）。
Hope that helps.
希望有帮助。
回答by Rick James
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 bookingsonce per.
这种形式仍然不是很有效，因为它必须扫描 all cars，然后bookings每次进入一次。
You do need an index on fkCarId. "fk" smells like it is a FOREIGN KEY, which implies an index. Please provide SHOW CREATE TABLEfor confirmation.
你确实需要一个索引fkCarId。“fk”闻起来像是一个FOREIGN KEY，这意味着一个索引。请提供SHOW CREATE TABLE确认。
If CriteriaBuilder can't build that, complain to them or get it out of your way.
如果 CriteriaBuilder 无法构建它，请向他们投诉或摆脱它。
Flipping it around mightrun 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 carsfor the desired rows. This might be especially faster if there are very few available cars.
在这个公式中，我希望对 进行表扫描bookings，过滤掉保留的字符，然后才进入cars所需的行。如果可用汽车很少，这可能会特别快。
