考虑以下两个表：
>项目(id,project_name)
>状态(id,id_project,status_name)
Status包含项目所在的所有状态.
让我们说我们想要查询最新状态名为“new”的所有项目.我想出的SQL查询是：
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,但类Subquery没有.
我到目前为止提出的标准查询是：
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没有任何方法可以对结果进行排序并只返回最新的结果.
解决方法
可以使用SELECT MAX而不是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.
遗憾的是,Criteria Queries不支持子查询中的排序 – 请参阅以下相关答案：
https://stackoverflow.com/questions/19549616#28233425
https://stackoverflow.com/questions/5551459#5551571
如果您确实需要ORDER BY(例如,在下面的示例中允许一系列值),则可以使用本机查询而不是CriteriaQuery：
Query query = entityManager.createNativeQuery(
"SELECT bar FROM foo WHERE bar IN (SELECT TOP 10 baz FROM quux ORDER BY quuux)");
