Close

JPA - GROUP BY and HAVING Criteria Query Example

[Updated: Jun 18, 2017, Created: Jun 18, 2017]

This quick example shows how to use JPA CriteriaQuery for setting the GROUP BY and HAVING clauses.

@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", "Sell");
      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, Admin, Person name 1]
[2, Admin, Person name 2]
[3, Sell, Person name 3]
[4, IT, Person name 4]
[5, Sell, Person name 5]
[6, Admin, Person name 6]
[7, Sell, Person name 7]
[8, IT, Person name 8]
[9, IT, Person name 9]
[10, Sell, Person name 10]
[11, Sell, Person name 11]
[12, Sell, Person name 12]
[13, Admin, Person name 13]
[14, Admin, Person name 14]
[15, Admin, Person name 15]
[16, IT, Person name 16]
[17, IT, Person name 17]
[18, IT, Person name 18]
[19, Sell, Person name 19]
[20, Sell, Person name 20]
-- running group by criteria query --
-- dept groups having count more than 5 --
DeptGroup{name='Sell', count=8}
DeptGroup{name='IT', count=6}
DeptGroup{name='Admin', count=6}

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

Criteria Query Group By Example Select All Download
  • criteria-group-by
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also