在JPQL或JPA Criteria Builder中使用DISTINCT ON

Question

我是一个有用的助手，可以翻译文本。

我有一个JPA实体User，其中包含一个字段（实体）City。 我想选择一页，例如10个用户，但来自不同的城市。

在SQL中，我会使用类似以下的语句：

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

但我需要使用JPQL或JPA标准构建器来完成它。我该怎么做？

- Mihajlo Brankovic

---

这显然不适用于所有的关系型数据库管理系统，你可能有一个特定的系统在脑海中（尽管没有提到是哪一个）。 - Neil Stockton

没错，我正在使用PostgreSQL。 - Mihajlo Brankovic

Answer 1

最近我遇到了同样的情况，发现使用criteria查询没有直接的方法来支持它。

这是我的解决方案 -

• 创建自定义sql函数以进行distinct on操作
• 将该函数注册到方言中
• 在属性中更新方言
• 从criteria查询中调用该函数

1）创建自定义函数

public class DistinctOn implements SQLFunction {
    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type type, Mapping mapping) throws QueryException {
        return StandardBasicTypes.STRING;
    }

    @Override
    public String render(Type type, List arguments, SessionFactoryImplementor sessionFactoryImplementor) throws QueryException {
        if (arguments.size() == 0) {
            throw new QueryException("distinct on should have at least one argument");
        }
        String commaSeparatedArgs = String.join(",",arguments);
        return " DISTINCT ON( " + commaSeparatedArgs + ") " + arguments.get(0) + " ";
    }
}

2) 注册功能

public class CustomPostgresSqlDialect extends PostgreSQLDialect {
    public CustomPostgresSqlDialect() {
        super();
        registerFunction("DISTINCT_ON", new DistinctOn());
    }
}

3) 更新方言： 在这里传递您的类名

spring.jpa.properties.hibernate.dialect = com.harshal.common.CustomPostgresSqlDialect

4) 在Criteria查询中使用

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> user = query.from(User.class);
        
// SELECT DISTINCT ON (u.city_id) u.username 
query.multiselect(
    cb.function("DISTINCT_ON", String.class, user.get("city")),
    user.get("userName")
);
        
return em.createQuery(query).getResultList();

- Harshal Pagar

Answer 2

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

- Navnath Chinchore

---

问题是关于使用“Distinct on”，而答案是关于“distinct”。 - Siva