在JPA Criteria API的子查询中使用ORDER BY的替代方法是什么?

问题描述

请考虑以下两个表:

1. Project ( id, project_name)
2. Status ( id, id_project, status_name)

1. Project ( id, project_name)
2. Status ( id, id_project, status_name)

Status的位置包含Project处于的所有状态.

Where Status contains all statuses in which a Project has been.

假设我们要查询所有状态为新"的项目.我想出的Sql查询是:

Lets say we want to query all projects for which the latest status has the name "new". The Sql query that I come up with is:

SELECT q.id_project FROM status q
WHERE q.status_name like 'new'
AND q.id IN (
    SELECT TOP 1 sq.id from status sq
    WHERE q.id_project = sq.id_project
    ORDER BY sq.id DESC )

我正在尝试使用Criteria API复制以上查询，并且我注意到类 CriteriaQuery 具有方法orderBy，但类子查询没有.

I'm trying to replicate the above query using Criteria API and I noticed that the class CriteriaQuery has the method orderBy but the class Subquery doesn't.

到目前为止，我提出的条件查询是:

The Criteria Query that I have come up with so far is:

CriteriaQuery<Project> q = criteriaBuilder.createQuery(Project.class);
Root<Status> qFrom       = q.from(Status.class);
Subquery<Integer> sq     = q.subquery(Integer.class);
Root<Status> sqFrom      = sq.from(Status.class);

sq.select(sqFrom.get(Status_.id))
  .where(criteriaBuilder.equal(sqFrom.get(Status_.project), qFrom.get(Status_.project))

我被困在这里是因为Subquery sq没有任何方法可以对结果进行排序并仅返回最新的结果.

I got stuck here because Subquery sq doesn't have any method for sorting its results and returning only the latest one.

在上述情况下，为获得所需结果而对子查询进行排序的替代方法是什么?

What are the alternatives to sorting the subquery in order to get the desired result in the scenario described above?

推荐答案

可以使用SELECT MAX而不是SELECT TOP 1 ... ORDER BY ...来编写子查询:

The subquery could be written using SELECT MAX instead of SELECT TOP 1 ... ORDER BY ...:

SELECT q.id_project FROM status q
WHERE q.status_name like 'new'
AND q.id = (
    SELECT MAX(sq.id) from status sq
    WHERE q.id_project = sq.id_project)

这也将更快，因为与查找最大值相比，对所有记录的排序较慢.如已经发布的那样，可以将其转换为CriteriaQuery.

This would also be faster as ordering all records is slow compared to finding a maximum value. As already posted, it can be translated into a CriteriaQuery.

不幸的是，条件查询不支持子查询中的排序-请参阅以下相关答案:

Unfortunately Criteria Queries do not support ordering in a subquery - see these related answers:

https://stackoverflow.com/questions/19549616#28233425 https://stackoverflow.com/questions/5551459#5551571

如果您真的需要ORDER BY(例如，在下面的示例中允许一定范围的值)，则可以使用本机查询而不是CriteriaQuery:

An alternative if you really need an ORDER BY (e.g. to allow a range of values in the example below) is to use a native query instead of a CriteriaQuery:

Query query = entityManager.createNativeQuery(
    "SELECT bar FROM foo WHERE bar IN (SELECT TOP 10 baz FROM quux ORDER BY quuux)");

这篇关于在JPA Criteria API的子查询中使用ORDER BY的替代方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！