Hibernate HQL select 与 where order by 查询不同

Question

我正在更新一个项目，以明确了解客户，以前假设客户，因为我们只有一个......

我的问题是向 HQL 查询添加 where 子句。

我的出发点是这个查询：

  public static final String SELECT_DISTINCT_STORES =

        "select DISTINCT e.storeNum, e.city, e.state from BoEngagement e order by e.storeNum";

我想添加一个where e.customer_fk = :customer_fk子句，但每次添加 where 子句时，我都会收到各种org.hibernate.hql.internal.ast.QuerySyntaxException错误，除非我取出distinct关键字，但我不相信查询会给出我所期望的结果。

这有效：

   "select e.storeNum, e.city, e.state from BoEngagement e WHERE e.customer_fk = :customer_fk";

而且，如果我要简化查询那么多，它确实应该是

"select e from BoEngagement e WHERE e.customer_fk = :customer_fk";

然而，正如我所说，我不相信删除关键字distinct是我想要做的。

以下是我尝试过的一些事情：

   "select DISTINCT e.storeNum, e.city, e.state FROM BoEngagement e WHERE e.customer_fk = :customer_fk order by e.storeNum";

给出这个错误

[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]

这个更复杂的版本

   "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关键字。我究竟做错了什么？

Answer 1

研究 EntityManager.createNativeQuery 方法，并提出这个有效的 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();现在，这个解决方案特定于 PostgreSQL，但这是我现在和无限期的未来使用的数据库。