jpa - JPQL 或 JPA 标准构建器中的 DISTINCT ON

Question

我有一个 JPA 实体用户，其中包含一个字段(实体)城市。我想选择一个页面，例如，来自不同城市的 10 个用户。

在 SQL 中，我会使用类似的东西:

SELECT DISTINCT ON (u.city_id) u.username ,u.email, u.city_id ....
FROM user u LIMIT 0,10 ....

但我需要使用 JPQL 或 JPA 标准构建器来完成。我怎样才能做到这一点？

Answer 1

You can do this by using Hibernate Criteria Query 

sample code can be like this 




 Criteria criteria = session.createCriteria(user.class);
 ProjectionList projectionList = Projections.projectionList();
           projectionList.add(Projections.distinct(projectionList.add(Projections.property("city_id"),  "cityId")));
      projectionList.add(Projections.property("username"), "username");
     projectionList.add(Projections.property("email"), "email");
      criteria.setProjection(projectionList2);
     criteria.setResultTransformer(Transformers.aliasToBean(user.class)); 
     List list = criteria.list();