具有聚合内连接的 JPA Criteria 查询

Question

我正在尝试编写一个 CriteriaQuery 它将查询每个城市的最新观察结果。城市由city_code字段定义，最新记录由observation_time字段定义。

我可以轻松地用普通 SQL 编写它，但我无法理解如何使用 jpa criteria api 来做到这一点。

select distinct m.* from 
 (select city_code cc, max(observation_time) mo
 from observations group by city_code) mx, observations m 
 where m.city_code = mx.cc and m.observation_time = mx.mo`

Answer 1

当你对宽松的效率持开放态度时，这是可能的。因此，首先让我们将查询转换为逻辑等效查询：select distinct m.* from observations m where&nbsp;m.observation_time = (select max(inn. observation_time) from observations inn&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where inn.city_code = m.city_code);然后我们将其转换为 JPA CriteriaQuery：public List<Observation> maxForEveryWithSubquery() {&nbsp; &nbsp; CriteriaBuilder builder = entityManager.getCriteriaBuilder();&nbsp; &nbsp; CriteriaQuery<Observation> query = builder.createQuery(Observation.class);&nbsp; &nbsp; Root<Observation> observation = query.from(Observation.class);&nbsp; &nbsp; query.select(observation);&nbsp; &nbsp; Subquery<LocalDateTime> subQuery = query.subquery(LocalDateTime.class);&nbsp; &nbsp; Root<Observation> observationInner = subQuery.from(Observation.class);&nbsp; &nbsp; subQuery.where(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.equal(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; observation.get(Observation_.cityCode),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; observationInner.get(Observation_.cityCode)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; );&nbsp; &nbsp; Subquery<LocalDateTime> subSelect = subQuery.select(builder.greatest(observationInner.get(Observation_.observationTime)));&nbsp; &nbsp; query.where(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.equal(subSelect.getSelection(), observation.get(Observation_.observationTime))&nbsp; &nbsp; );&nbsp; &nbsp; TypedQuery<Observation> typedQuery = entityManager.createQuery(query);&nbsp; &nbsp; return typedQuery.getResultList();}

Answer 2

不幸的是，JPA 不支持FROM子句中的子查询。您需要编写本机查询或使用像FluentJPA这样的框架。