The GROUP BY clause allows to divide the query results into groups.
Optional HAVING clause can be used with GROUP BY to filter over the groups.
Quick Example
Query query = em.createQuery(
"SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept IN ('IT', 'Admin')");
List<Object[]> resultList = query.getResultList();
Examples
Entity
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private String dept;
private long salary;
.............
}
Using GROUP BY - HAVING
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findEmployeeCountGroupByDept();
findEmployeeAvgSalariesGroupByDept();
findEmployeeCountGroupBySalary();
findEmployeeMaxSalariesGroupBySelectedDept();
} finally {
entityManagerFactory.close();
}
}
public static void persistEmployees() {
Employee employee1 = Employee.create("Diana", "IT", 3000);
Employee employee2 = Employee.create("Rose", "Admin", 2000);
Employee employee3 = Employee.create("Denise", "Admin", 4000);
Employee employee4 = Employee.create("Mike", "IT", 3500);
Employee employee5 = Employee.create("Linda", "Sales", 2000);
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
em.persist(employee1);
em.persist(employee2);
em.persist(employee3);
em.persist(employee4);
em.persist(employee5);
em.getTransaction().commit();
em.close();
}
private static void findEmployeeCountGroupByDept() {
System.out.println("-- Employee count group by dept --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e.dept, COUNT(e) FROM Employee e GROUP BY e.dept");
List<Object[]> resultList = query.getResultList();
resultList.forEach(r -> System.out.println(Arrays.toString(r)));
em.close();
}
private static void findEmployeeAvgSalariesGroupByDept() {
System.out.println("-- Employees avg salary group by dept --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept");
List<Object[]> resultList = query.getResultList();
resultList.forEach(r -> System.out.println(Arrays.toString(r)));
em.close();
}
private static void findEmployeeCountGroupBySalary() {
System.out.println("-- Employee count group by salary --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e.salary, COUNT(e) FROM Employee e GROUP BY e.salary");
List<Object[]> resultList = query.getResultList();
resultList.forEach(r -> System.out.println(Arrays.toString(r)));
em.close();
}
private static void findEmployeeMaxSalariesGroupBySelectedDept() {
System.out.println("-- Employees max salary group by dept - only in IT and Admin dept --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept IN ('IT', 'Admin')");
List<Object[]> resultList = query.getResultList();
resultList.forEach(r -> System.out.println(Arrays.toString(r)));
em.close();
}
} -- Employee count group by dept -- [IT, 2] [Sales, 1] [Admin, 2] -- Employees avg salary group by dept -- [IT, 3250.0] [Sales, 2000.0] [Admin, 3000.0] -- Employee count group by salary -- [2000, 2] [4000, 1] [3500, 1] [3000, 1] -- Employees max salary group by dept - only in IT and Admin dept -- [IT, 3500] [Admin, 4000]
Example ProjectDependencies and Technologies Used: - h2 1.4.197: H2 Database Engine.
- hibernate-core 5.2.13.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
|
|