Dynamic JPA criteria builder

Question

I am using Spring boot JPA to below execute below query

select DELTA_TYPE,OPERATION_ID,COUNT(*) from ACTIVE_DISCREPANCIES ad group by DELTA_TYPE,OPERATION_ID

DELTA_TYPE,OPERATION_ID, etc may come from external system, in repository class I tried to execute native query

    @Query(value="select OPERATION_ID,DELTA_TYPE,count(*) from ACTIVE_DISCREPANCIES ad group by ?1",nativeQuery = true)
    public List<Object[]> groupByQuery(@Param("reconType") String recGroupColumns);

where recGroupColumns="DELTA_TYPE,OPERATION_ID" but didnt work as @param will split ','

Second option for me was criteria query

public List<Object[]> getReconGroupList() {
        String recGroupColumns = "OPERATION_ID,DELTA_TYPE";
        String[] arrStr = recGroupColumns.split(",");
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
        Root<ActiveDiscrepancies> adr = query.from(ActiveDiscrepancies.class);
        query.groupBy(adr.get("operationId"), adr.get("deltaType"));

    // query.groupBy(adr.get("deltaType"));
    query.multiselect(adr.get("operationId"), adr.get("deltaType"), criteriaBuilder.count(adr));
    TypedQuery<Object[]> typedQuery = entityManager.createQuery(query);
    List<Object[]> resultList = typedQuery.getResultList();
    return resultList;
}

Here how can I pass groupBy and multiselect dynamically?

seen [this post](https://stackoverflow.com/questions/8491796/hibernate-group-by-criteria-object) on the same issue? — Curiosa Globunznik, Oct 25 '19 at 10:32
Possible duplicate of [Hibernate Group by Criteria Object](https://stackoverflow.com/questions/8491796/hibernate-group-by-criteria-object) — Curiosa Globunznik, Oct 25 '19 at 10:33

score 0 · Answer 1 · answered Oct 29 '19 at 06:34

Using projections we can solve this scenario, below is the code

     Criteria criteria = getSession().createCriteria(ActiveDiscrepancies.class);

      ProjectionList projectionList = Projections.projectionList();

      for(String str : colList) {
        projectionList.add(Projections.groupProperty(str));
      }
        projectionList.add(Projections.rowCount());
        criteria.setProjection(projectionList);
        List results = criteria.list();

        getSession().close();

Dynamic JPA criteria builder

1 Answers1