我正在嘗試編寫一個 CriteriaQuery 它將查詢每個城市的最新觀察結(jié)果。城市由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`
2 回答
翻過高山走不出你
TA貢獻1875條經(jīng)驗 獲得超3個贊
當你對寬松的效率持開放態(tài)度時，這是可能的。因此，首先讓我們將查詢轉(zhuǎn)換為邏輯等效查詢：
select distinct m.* from observations m where
m.observation_time = (select max(inn. observation_time) from observations inn
where inn.city_code = m.city_code);
然后我們將其轉(zhuǎn)換為 JPA CriteriaQuery：
public List<Observation> maxForEveryWithSubquery() {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Observation> query = builder.createQuery(Observation.class);
Root<Observation> observation = query.from(Observation.class);
query.select(observation);
Subquery<LocalDateTime> subQuery = query.subquery(LocalDateTime.class);
Root<Observation> observationInner = subQuery.from(Observation.class);
subQuery.where(
builder.equal(
observation.get(Observation_.cityCode),
observationInner.get(Observation_.cityCode)
)
);
Subquery<LocalDateTime> subSelect = subQuery.select(builder.greatest(observationInner.get(Observation_.observationTime)));
query.where(
builder.equal(subSelect.getSelection(), observation.get(Observation_.observationTime))
);
TypedQuery<Observation> typedQuery = entityManager.createQuery(query);
return typedQuery.getResultList();
}
添加回答
舉報
0/150
提交
取消