CODEWITHSUNDEEP
postgresqljpadistinctjpql
Mihajlo Brankovic
Mihajlo Brankovic

Reputation: 273

DISTINCT ON in JPQL or JPA criteria builder

I have a JPA entity User which contains a field (entity) City. I want to select one page of, for example, 10 users but from different cities.

In SQL I would use something like: 

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

but I need to do it with JPQL or JPA criteria builder. How can I achieve this?

Upvotes: 6

Views: 3810

Answers (2)

Harshal Pagar
Harshal Pagar

Reputation: 164

Recently I came across same situation, found that there is no direct way using criteria query to support it.

Here is my solution -

  • Create custom sql function for distinct on
  • register function to dialect
  • Update dialect in properties
  • call it from criteria query

1) Create Custom function

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) Register Function

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

3) Update Dialect : Here pass on your class name

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

4) Use it in Criteria Query

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

Upvotes: 4

Navnath Chinchore
Navnath Chinchore

Reputation: 95

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

Upvotes: 0

Related Questions