CODEWITHSUNDEEP
hibernatejpacriteria-api
INDIRA THEVAR
INDIRA THEVAR

Reputation: 33

How to make Criteria Query with grouping

select CreatedBy, count(*) as cnt
from fourwheelerquoteresponse
group by CreatedBy
order by count(*) desc
limit 5;

This is my sql I want it according to JPA Criteria API still not sure with this This is my query which is returning the overall list without considering the group by

@Override
public List<FourWheelerQuoteResponseEntity> findAllByTopOrLow(String createdBy) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<FourWheelerQuoteResponseEntity> criteriaQuery = criteriaBuilder.createQuery(FourWheelerQuoteResponseEntity.class);
    Root<FourWheelerQuoteResponseEntity> root = criteriaQuery.from(FourWheelerQuoteResponseEntity.class);
    Expression<String> groupByExp = root.get("createdBy").as(String.class);
    Expression<Long> countExp = criteriaBuilder.count(groupByExp);
    CriteriaQuery<FourWheelerQuoteResponseEntity> select =
    criteriaQuery.multiselect(groupByExp, countExp);
    criteriaQuery.groupBy(groupByExp);
    criteriaQuery.orderBy(criteriaBuilder.desc(countExp));

    criteriaQuery.select(root)
     .where(criteriaBuilder.equal(root.get("createdBy"), createdBy));

    try {
        return entityManager.createQuery(select).getResultList();   
    } catch (NoResultException nre) {
        return null;
    } catch (NonUniqueResultException nure) {
        return null;
    }
}

Upvotes: 3

Views: 3253

Answers (1)

Supager
Supager

Reputation: 95

Refer to the below code, I am certain this will solve your problem. If not then let me know

@Override
    @Transactional(readOnly = true)
    public Map<String,Long> topOrLowFew(List<String> userIdList,String order,int howMany) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class);
        Root<FourWheelerQuoteResponseEntity> root = criteriaQuery.from(FourWheelerQuoteResponseEntity.class);
        criteriaQuery.groupBy(root.get("createdBy"));
        if(order.equals("desc")) {
            criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.count(root)));
        }
        if(order.equals("asc")) {
            criteriaQuery.orderBy(criteriaBuilder.asc(criteriaBuilder.count(root)));
        }
        criteriaQuery.multiselect(root.get("createdBy"), criteriaBuilder.count(root));
        criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.in(root.get("createdBy")).value(userIdList)));
        List<Tuple> resultList = entityManager.createQuery(criteriaQuery).setMaxResults(howMany).getResultList();
        Map<String,Long> orderedMap = new LinkedHashMap<String,Long>();
        for ( Tuple tuple : resultList ) {
              String userId = (String) tuple.get( 0 );
              Long count = (Long) tuple.get( 1 );
              orderedMap.put(userId, count);
            }
        return orderedMap;
    }

Upvotes: 4

Related Questions