JPA 2 Hibernate Group BY and Count * with other columns

Question

I am stuck in finding a way to do the Group BY and Count * with other columns using JPA 2 Hibernate EntityManager + CriteriaBuilder apis. What is the best way to do this.

My sql query will be as simple as

select type, count(*) from test group by type

I have Test Entity created with exact mapping.

But finally I am needed the result with an extra field for count. I have seen example of using hibernate sessions and using projections for the same. But since I am using JPA2 CriteriaBuilder I couldn't able to use it. count with other fields in group by with hibernate

I am sure there will be some simple and more elegant way to do this. Let me know if anyone knows it.

Edit 1

As of now I am able to do to an extend without using criteria builder

List<Object[]> results = em
                .createQuery("SELECT m.type AS name, COUNT(m) AS total FROM Test AS m GROUP BY m.type ORDER BY m.type ASC").getResultList();

        for (Object[] result : results) {
            String name = String.valueOf( result[0]);
            int count = ((Number) result[1]).intValue();

            logger.info(name + "   " + count);
        }

But I need to do the same using CriteriaBuilder and i may need to dynamically inject where clauses based on my filter arguments.

First. HIbernate Criteria and JPA Criteria API are different things: which one do you want to use? Second. Have a look at a decent tutorial on the framework you have chosen, and get back with a concrete question. We are not going to code for you. — perissf
So why have you linked an answer that is using Hibernate-criteria? — perissf

Ysak Ysak · Accepted Answer · 2015-11-09T09:26:08

Able to achieve the same by this

   CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<Tuple> q = cb.createTupleQuery();
            Root<Test> c = q.from(Test.class);
            q.multiselect(c.get("type"), cb.count(c));
            q.groupBy(c.get("type"));

            TypedQuery<Tuple> t = em.createQuery(q);
            List<Tuple> resultList = t.getResultList();

            for(Tuple tuple : resultList){
                logger.info(tuple.get(0) + "   " + tuple.get(1));
            }

JPA 2 Hibernate Group BY and Count * with other columns

1 Answers