java - 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)";

出现此错误

[ERROR] 2019-10-18 15:08:14.317 [main] BsRetrieveDistinct - java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: in near line 1, column 81 [select DISTINCT e.storeNum, e.city, e.state FROM com.bh.radar.bo.BoEngagement e in (select g FROM com.bh.radar.bo.BoEngagement g WHERE g.customer_fk = :customer_fk order by g.storeNum)]
java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: in near line 1, column 81 [select DISTINCT e.storeNum, e.city, e.state FROM com.bh.radar.bo.BoEngagement e in (select g FROM com.bh.radar.bo.BoEngagement g WHERE g.customer_fk = :customer_fk order by g.storeNum)]

显然我并不完全理解 HQL 和 distinct 关键字。我做错了什么？

编辑:

@JB Nizet 评论说，根据我发布的错误，他认为我没有运行我发布的查询。我绝对正在运行该查询。我的困惑是为什么我编写的 HQL 在 SQL 中生成两个 FROM 子句。我在下面粘贴了一张屏幕截图，显示了相关代码和错误消息。

Answer 1

也许这不是我原来问题的答案，但是在互联网上搜索了几天并在这里发布我的问题并没有发现任何可以从我尝试过的各种 JPQL 字符串生成合理的 PostgreSQL 查询的内容，所以我偶然发现了这个博客文章:The Many Faces of DISTINCT in PostgreSQL

从那里开始研究 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，但这是我现在和无限期的 future 使用的数据库。