首页 ❯

使用 JPA 条件 API 将条件表达式与“AND”和“OR”谓词组合

java mysql criteria jpa
2022-09-01 18:46:29

我需要调整下面的代码示例。

我有一个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'))

我有一张桌子，还有一张桌子。我想找出在某个时间范围内可用的汽车。SQL 查询的工作方式类似于超级按钮。bookingscars

我想把这个“转换”成一个输出。在过去的3个小时里，我用这个输出阅读了文档（显然，这不起作用）。我甚至跳过了子查询中的 where 部分。CriteriaBuilder

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();

另一个问题：没有被定义为外键，它只是一个整数。有什么方法可以以这种方式修复它吗？fkCarId


答案 1

我在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_id在表中是主键，并且是引用表的主键 （） 的外键。bookingsfk_car_idcar_idcars

使用子查询的相应 JPA 条件查询如下所示。IN()

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();

它会生成您感兴趣的以下 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<=?
    )

上述查询的 WHERE 子句中条件表达式的括号在技术上是完全多余的，只是为了提高 Hibernate 忽略的可读性 - Hibernate 不必考虑它们。

但是，我个人更喜欢使用运算符。因此，可以按如下方式重构查询。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();

它生成以下 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))

返回相同的结果列表。

附加：

在这里，当在EclipseLink上使用类似复杂子查询语句的表达式时，EclipseLink会感到困惑并导致异常。因此，在 EclipseLink 上编写复杂的子查询时，可能需要考虑这一点。在这种情况下，只需选择一个，例如subquery.select(criteriaBuilder.literal(1L));criteriaBuilder.literal(1L)id

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

与第一种情况一样。注意：如果您在EclipseLink上运行如上所述的表达式，您将在SQL查询生成中看到一个奇怪的行为，尽管结果列表将是相同的。

您还可以使用联接，这在后端数据库系统上效率更高，在这种情况下，您需要使用联接来过滤掉可能的重复行，因为您需要父表的结果列表。如果详细表中存在多个子行，则结果列表可能包含重复行 - 对于相应的父行。我把它留给你。:)这就是它在这里的方式。DISTINCTbookingscars

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();

它生成以下 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

可以看出，Hibernate 提供程序将子句中的条件语句反转为响应 。其他提供商也可能生成确切的结果，但毕竟，这与问题中写入的结果相同，并且产生与以前情况相同的结果列表。WHEREWHERE NOT(...)WHERE NOT(...)

未指定右联接。因此，JPA 提供程序不必实现它们。它们中的大多数不支持右联接。

各自的JPQL只是为了完整性:)

查询：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 <=? )

查询：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) )

最后一个使用左联接（带有命名参数）：

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

上述所有 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();

根据需要/需要，将命名参数替换为相应的索引/位置参数。

所有这些 JPQL 语句还生成与上述条件 API 生成的 SQL 语句相同的 SQL 语句。

  • 在这种情况下，我总是会避免子查询，尤其是在使用MySQL时。当且仅当绝对需要子查询时，例如当我们需要确定结果集或根据静态值列表删除行列表时，我才会使用子查询，例如IN()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);

    等等。

  • 在这种情况下，我总是更喜欢使用运算符进行查询，因为结果列表仅涉及基于另一个表中的条件的单个表。在这种情况下，联接将生成前面提到的重复行，需要使用上述查询之一所示的方法来筛选出这些行。EXISTSDISTINCT

  • 当要检索的结果集从多个数据库表中组合时，我最好使用联接 - 无论如何都必须使用显而易见的。

毕竟，一切都取决于许多事情。这些根本不是里程碑。

免責聲明：我对RDBMS知之甚少。

注意：我已经使用了参数化/重载的弃用日期构造函数 - Date（String s）用于与SQL查询关联的索引/位置参数，用于纯粹的测试目的，只是为了避免您已经知道的java.util.SimpleDateFormat噪音的整个混乱。你也可以使用其他更好的API，如Joda Time（Hibernate支持它），java.sql.*（这些是java.util.Date的子类），Java 8中的Java Time（目前大部分不支持，除非定制），当需要/需要时。

希望有所帮助。


答案 2

如果您采用以下格式，它将运行得更快：

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

这种形式仍然不是很有效，因为它必须扫描所有，然后达到一次。carsbookings

您确实需要一个 索引。“fk”闻起来像是一个，这意味着一个索引。请提供确认。fkCarIdFOREIGN KEYSHOW CREATE TABLE

如果 CriteriaBuilder 无法构建它，请向他们投诉或将其排除在外。

翻转它可能会运行得更快：

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

在这个公式中，我希望对 进行表扫描，过滤掉保留的字符，然后才进入所需的行。如果可用的汽车很少，这可能会特别快。bookingscars


推荐
更多 »
标签
更多 »
php pass-by-reference optional-parameters default-value javascript arrays dom function string object serialization tostring visibility syntax jslint use-strict operators equality equality-operator identity-operator datetime timestamp date-arithmetic redirect angularjs loops foreach iteration scope variables
推荐
更多 »