首頁猿問 Hibernate HQL...

Hibernate HQL select 與 where order by 查詢不同

Java

慕尼黑5688855 2023-11-10 17:02:22

我正在更新一個(gè)項(xiàng)目，以明確了解客戶，以前假設(shè)客戶，因?yàn)槲覀冎挥幸粋€(gè)......我的問題是向 HQL 查詢添加 where 子句。我的出發(fā)點(diǎn)是這個(gè)查詢： public static final String SELECT_DISTINCT_STORES = "select DISTINCT e.storeNum, e.city, e.state from BoEngagement e order by e.storeNum";我想添加一個(gè)where e.customer_fk = :customer_fk子句，但每次添加 where 子句時(shí)，我都會(huì)收到各種org.hibernate.hql.internal.ast.QuerySyntaxException錯(cuò)誤，除非我取出distinct關(guān)鍵字，但我不相信查詢會(huì)給出我所期望的結(jié)果。這有效： "select e.storeNum, e.city, e.state from BoEngagement e WHERE e.customer_fk = :customer_fk";而且，如果我要簡化查詢那么多，它確實(shí)應(yīng)該是"select e from BoEngagement e WHERE e.customer_fk = :customer_fk";然而，正如我所說，我不相信刪除關(guān)鍵字distinct是我想要做的。以下是我嘗試過的一些事情： "select DISTINCT e.storeNum, e.city, e.state FROM BoEngagement e WHERE e.customer_fk = :customer_fk order by e.storeNum";給出這個(gè)錯(cuò)誤[ERROR] 2019-10-18 15:10:03.449 [main] BsRetrieveDistinct - java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: FROM near line 1, column 94 [SELECT DISTINCT e.city, e.state from com.bh.radar.bo.BoEngagement e order by e.state, e.city FROM com.bh.radar.bo.BoEngagement e WHERE e.customer_fk = :customer_fk]java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: FROM near line 1, column 94 [SELECT DISTINCT e.city, e.state from com.bh.radar.bo.BoEngagement e order by e.state, e.city FROM com.bh.radar.bo.BoEngagement e WHERE e.customer_fk = :customer_fk]這個(gè)更復(fù)雜的版本 "select DISTINCT e.storeNum, e.city, e.state FROM BoEngagement e in " + "(select g FROM BoEngagement g WHERE g.customer_fk = :customer_fk order by g.storeNum)";顯然我并沒有完全理解HQL和distinct關(guān)鍵字。我究竟做錯(cuò)了什么？

查看完整描述

1 回答

一只名叫tom的貓

TA貢獻(xiàn)1906條經(jīng)驗(yàn) 獲得超3個(gè)贊

研究 EntityManager.createNativeQuery 方法，并提出這個(gè)有效的 PostgreSQL 查詢：

??public?static?final?String?SELECT_DISTINCT_STORES?=
?????????"SELECT?DISTINCT?ON(storeNum,?city,?state)?*?FROM?radar2.engagement?WHERE?customer_fk?=?:customer_fk?ORDER?BY?storeNum?asc,?city,?state";

我這樣使用它：

????results?=?em.createNativeQuery(SELECT_DISTINCT_CITY_STATES,?BoEngagement.class)
????????????????.setParameter("customer_fk",?customer_fk)
????????????????.getResultList();

現(xiàn)在，這個(gè)解決方案特定于 PostgreSQL，但這是我現(xiàn)在和無限期的未來使用的數(shù)據(jù)庫。

反對回復(fù) 2023-11-10