JPA Criteria API - Mapping groupBy() result to user defined object

This quick example shows how to map query result obtained via groupBy() to a user defined object.

@Entity
public class Person {
    @Id
    @GeneratedValue
    private long id;
    private String name;
    private String  dept;
    .............
}

public class DeptGroup {
    private String name;
    private long count;

    public DeptGroup(String name, long count) {
        this.name = name;
        this.count = count;
    }
    .............
}

public class ExampleMain {
    private static EntityManagerFactory emf;

    public static void main(String[] args) {
        emf = Persistence.createEntityManagerFactory("example-unit");
        try {
            nativeQuery("SHOW COLUMNS from person");
            persistPersons();
            nativeQuery("Select * from person");
            runGroupByQuery();
        } finally {
            emf.close();
        }
    }

    private static void persistPersons() {
        System.out.println("-- persisting persons --");
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();

        List<String> deptList = Arrays.asList("Admin", "IT", "Sale");
        for (int i = 1; i <= 20; i++) {
            Person p = new Person();
            p.setName("Person name " + i);
            //set dept randomly
            int index = ThreadLocalRandom.current()
                                         .nextInt(0, 3);
            p.setDept(deptList.get(index));
            em.persist(p);
        }
        em.getTransaction().commit();
        em.close();
        System.out.println("-- persisting persons finished --");
    }

    private static void runGroupByQuery() {
        System.out.println("-- running group by criteria query --");
        System.out.println("-- dept groups having count more than 5 --");
        EntityManager em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<DeptGroup> criteriaQuery = cb.createQuery(DeptGroup.class);
        Root<Person> root = criteriaQuery.from(Person.class);

        Expression<String> groupByExp = root.get("dept").as(String.class);
        Expression<Long> countExp = cb.count(groupByExp);
        CriteriaQuery<DeptGroup> select =
                criteriaQuery.multiselect(groupByExp, countExp);
        criteriaQuery.groupBy(groupByExp);
        criteriaQuery.having(cb.gt(cb.count(root), 5));
        //ordering by count in descending order
        criteriaQuery.orderBy(cb.desc(countExp));

        TypedQuery<DeptGroup> query = em.createQuery(select);
        List<DeptGroup> resultList = query.getResultList();
        resultList.forEach(System.out::println);
    }

    public static void nativeQuery(String s) {
        System.out.printf("'%s'%n", s);
        EntityManager em = emf.createEntityManager();
        Query query = em.createNativeQuery(s);
        List list = query.getResultList();
        for (Object o : list) {
            if (o instanceof Object[]) {
                System.out.println(Arrays.toString((Object[]) o));
            } else {
                System.out.println(o);
            }
        }
        em.close();
    }
}

Output

'SHOW COLUMNS from person'
[ID, BIGINT(19), NO, PRI, NULL]
[DEPT, VARCHAR(255), YES, , NULL]
[NAME, VARCHAR(255), YES, , NULL]
-- persisting persons --
-- persisting persons finished --
'Select * from person'
[1, Sale, Person name 1]
[2, Sale, Person name 2]
[3, IT, Person name 3]
[4, IT, Person name 4]
[5, Sale, Person name 5]
[6, Sale, Person name 6]
[7, IT, Person name 7]
[8, IT, Person name 8]
[9, IT, Person name 9]
[10, Sale, Person name 10]
[11, Sale, Person name 11]
[12, Admin, Person name 12]
[13, Sale, Person name 13]
[14, Sale, Person name 14]
[15, Admin, Person name 15]
[16, IT, Person name 16]
[17, IT, Person name 17]
[18, IT, Person name 18]
[19, Admin, Person name 19]
[20, Admin, Person name 20]
-- running group by criteria query --
-- dept groups having count more than 5 --
DeptGroup{name='IT', count=8}
DeptGroup{name='Sale', count=8}

Also check out this tutorial to understand how multiselect() method works.

Example Project

Dependencies and Technologies Used:

• h2 1.4.195: H2 Database Engine.
• hibernate-core 5.2.10.Final: The core O/RM functionality as provided by Hibernate.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9