jpa - 在JPQL或JPA标准构建器中区分

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

在SQL中，我将使用类似:

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

但我需要使用JPQL或JPA标准构建器来完成此操作。我该如何实现？

最佳答案

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();