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，但这是我现在和无限期的未来使用的数据库。