HQL with Aggregate functions
Let us understand about Aggregate functions in HQL
Hibernate supports several aggregate functions similar to SQL aggregate functions.
Aggregate functions are used to get an aggregate value of any column like SUM(salary) from Employee table , AVG(salary) from Employee table
There are multiple such aggregate methods as below supported by Hibernate
Let us perform some of these aggregate functions on Employee table by creating Hibernate Project.
Step 1
Create hibernate project
Please refer Hibernate setup in eclipse article on how to do it.
Project structure
Step 2
Update pom.xml with Hibernate and Mysql dependencies
- <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
- <modelVersion>4.0.0</modelVersion>
- <groupId>HQLAggregateFunctions</groupId>
- <artifactId>HQLAggregateFunctions</artifactId>
- <version>0.0.1-SNAPSHOT</version>
- <packaging>jar</packaging>
- <name>HQLAggregateFunctions</name>
- <url>http://maven.apache.org</url>
- <properties>
- <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
- </properties>
- <dependencies>
- <dependency>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- <version>3.8.1</version>
- <scope>test</scope>
- </dependency>
- <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
- <dependency>
- <groupId>org.hibernate</groupId>
- <artifactId>hibernate-core</artifactId>
- <version>5.2.6.Final</version>
- </dependency>
- <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <version>6.0.5</version>
- </dependency>
- </dependencies>
- </project>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>HQLAggregateFunctions</groupId> <artifactId>HQLAggregateFunctions</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>HQLAggregateFunctions</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.2.6.Final</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.5</version> </dependency> </dependencies> </project>
Step 3
Create Employee class
- package com.kb.model;
- import javax.persistence.Column;
- import javax.persistence.Entity;
- import javax.persistence.GeneratedValue;
- import javax.persistence.GenerationType;
- import javax.persistence.Id;
- import javax.persistence.Table;
- @Entity
- @Table(name="Employee")
- public class Employee {
- @Id
- @GeneratedValue(strategy = GenerationType.SEQUENCE)
- @Column(name = "Employee_Id")
- private int employeeId;
- @Column(name = "FirstName")
- private String firstName;
- @Column(name = "LastName")
- private String lastName;
- @Column(name = "Age")
- private int age;
- @Column(name = "Education")
- private String education;
- @Column(name = "Salary")
- private int salary;
- public int getEmployeeId() {
- return employeeId;
- }
- public void setEmployeeId(int employeeId) {
- this.employeeId = employeeId;
- }
- public String getFirstName() {
- return firstName;
- }
- public void setFirstName(String firstName) {
- this.firstName = firstName;
- }
- public String getLastName() {
- return lastName;
- }
- public void setLastName(String lastName) {
- this.lastName = lastName;
- }
- public int getAge() {
- return age;
- }
- public void setAge(int age) {
- this.age = age;
- }
- public String getEducation() {
- return education;
- }
- public void setEducation(String education) {
- this.education = education;
- }
- public int getSalary() {
- return salary;
- }
- public void setSalary(int salary) {
- this.salary = salary;
- }
- }
package com.kb.model; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name="Employee") public class Employee { @Id @GeneratedValue(strategy = GenerationType.SEQUENCE) @Column(name = "Employee_Id") private int employeeId; @Column(name = "FirstName") private String firstName; @Column(name = "LastName") private String lastName; @Column(name = "Age") private int age; @Column(name = "Education") private String education; @Column(name = "Salary") private int salary; public int getEmployeeId() { return employeeId; } public void setEmployeeId(int employeeId) { this.employeeId = employeeId; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getEducation() { return education; } public void setEducation(String education) { this.education = education; } public int getSalary() { return salary; } public void setSalary(int salary) { this.salary = salary; } }
We have specified Primary key as employeeId and generator class as “sequence” for automatic primary key generation.
Step 4
Create hibernate.cfg.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE hibernate-configuration PUBLIC
- "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
- "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
- <hibernate-configuration>
- <session-factory>
- <!-- Database connection properties -->
- <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
- <property name="connection.url">jdbc:mysql://localhost/javainsimpleway</property>
- <property name="connection.username">root</property>
- <property name="connection.password">root</property>
- <!-- JDBC connection pool (using the built-in) -->
- <property name="connection.pool_size">100</property>
- <!-- SQL dialect -->
- <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
- <!-- Disable the second-level cache -->
- <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>
- <!-- Echo all executed SQL to stdout -->
- <property name="show_sql">true</property>
- <!-- Format the generated Sql -->
- <property name="format_sql">true</property>
- <!-- Dont Drop and re-create the database schema on startup,Just update it -->
- <property name="hbm2ddl.auto">update</property>
- <mapping class="com.kb.model.Employee" />
- </session-factory>
- </hibernate-configuration>
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Database connection properties --> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://localhost/javainsimpleway</property> <property name="connection.username">root</property> <property name="connection.password">root</property> <!-- JDBC connection pool (using the built-in) --> <property name="connection.pool_size">100</property> <!-- SQL dialect --> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <!-- Disable the second-level cache --> <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property> <!-- Echo all executed SQL to stdout --> <property name="show_sql">true</property> <!-- Format the generated Sql --> <property name="format_sql">true</property> <!-- Dont Drop and re-create the database schema on startup,Just update it --> <property name="hbm2ddl.auto">update</property> <mapping class="com.kb.model.Employee" /> </session-factory> </hibernate-configuration>
We have defined all the database configuration in this file
hbm2ddl.auto property is defined in the config file which helps in automatic creation of tables in the database based on the mapping.
We have also provided the mapping class name using “mapping” tag.
Step 5
Create Hibernate util class
- package com.kb.util;
- import org.hibernate.SessionFactory;
- import org.hibernate.cfg.Configuration;
- public class HibernateUtil {
- private static final SessionFactory sessionFactory = buildSessionFactory();
- private static SessionFactory buildSessionFactory() {
- try {
- // Create the SessionFactory from hibernate.cfg.xml
- return new Configuration().configure().buildSessionFactory();
- } catch (Throwable ex) {
- // Make sure you log the exception to track it
- System.err.println("SessionFactory creation failed." + ex);
- throw new ExceptionInInitializerError(ex);
- }
- }
- public static SessionFactory getSessionFactory() {
- return sessionFactory;
- }
- public static void shutdown() {
- // Optional but can be used to Close caches and connection pools
- getSessionFactory().close();
- }
- }
package com.kb.util; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; public class HibernateUtil { private static final SessionFactory sessionFactory = buildSessionFactory(); private static SessionFactory buildSessionFactory() { try { // Create the SessionFactory from hibernate.cfg.xml return new Configuration().configure().buildSessionFactory(); } catch (Throwable ex) { // Make sure you log the exception to track it System.err.println("SessionFactory creation failed." + ex); throw new ExceptionInInitializerError(ex); } } public static SessionFactory getSessionFactory() { return sessionFactory; } public static void shutdown() { // Optional but can be used to Close caches and connection pools getSessionFactory().close(); } }
Step 6
Create PopulateData.java file to Populate Employee Table with initial data
- package com.kb.db;
- import org.hibernate.Session;
- import org.hibernate.SessionFactory;
- import org.hibernate.Transaction;
- import com.kb.model.Employee;
- import com.kb.util.HibernateUtil;
- public class PopulateData {
- public static void main(String[] args) {
- // Get session factory using Hibernate Util class
- SessionFactory sf = HibernateUtil.getSessionFactory();
- // Get session from Sesson factory
- Session session = sf.openSession();
- // Begin transaction
- Transaction t = session.beginTransaction();
- //Create Employee data
- Employee employee1 = new Employee();
- employee1.setFirstName("John");
- employee1.setLastName("KC");
- employee1.setAge(28);
- employee1.setEducation("PG");
- employee1.setSalary(25000);
- Employee employee2 = new Employee();
- employee2.setFirstName("Jacob");
- employee2.setLastName("JC");
- employee2.setAge(30);
- employee2.setEducation("PG");
- employee2.setSalary(30000);
- Employee employee3 = new Employee();
- employee3.setFirstName("Martin");
- employee3.setLastName("A");
- employee3.setAge(24);
- employee3.setEducation("UG");
- employee3.setSalary(20000);
- Employee employee4 = new Employee();
- employee4.setFirstName("Peter");
- employee4.setLastName("M");
- employee4.setAge(25);
- employee4.setEducation("UG");
- employee4.setSalary(22000);
- Employee employee5 = new Employee();
- employee5.setFirstName("Roshan");
- employee5.setLastName("B");
- employee5.setAge(29);
- employee5.setEducation("PG");
- employee5.setSalary(45000);
- session.save(employee1);
- session.save(employee2);
- session.save(employee3);
- session.save(employee4);
- session.save(employee5);
- // Commit the transaction and close the session
- t.commit();
- session.close();
- System.out.println("successfully persisted Employee details");
- }
- }
package com.kb.db; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.Transaction; import com.kb.model.Employee; import com.kb.util.HibernateUtil; public class PopulateData { public static void main(String[] args) { // Get session factory using Hibernate Util class SessionFactory sf = HibernateUtil.getSessionFactory(); // Get session from Sesson factory Session session = sf.openSession(); // Begin transaction Transaction t = session.beginTransaction(); //Create Employee data Employee employee1 = new Employee(); employee1.setFirstName("John"); employee1.setLastName("KC"); employee1.setAge(28); employee1.setEducation("PG"); employee1.setSalary(25000); Employee employee2 = new Employee(); employee2.setFirstName("Jacob"); employee2.setLastName("JC"); employee2.setAge(30); employee2.setEducation("PG"); employee2.setSalary(30000); Employee employee3 = new Employee(); employee3.setFirstName("Martin"); employee3.setLastName("A"); employee3.setAge(24); employee3.setEducation("UG"); employee3.setSalary(20000); Employee employee4 = new Employee(); employee4.setFirstName("Peter"); employee4.setLastName("M"); employee4.setAge(25); employee4.setEducation("UG"); employee4.setSalary(22000); Employee employee5 = new Employee(); employee5.setFirstName("Roshan"); employee5.setLastName("B"); employee5.setAge(29); employee5.setEducation("PG"); employee5.setSalary(45000); session.save(employee1); session.save(employee2); session.save(employee3); session.save(employee4); session.save(employee5); // Commit the transaction and close the session t.commit(); session.close(); System.out.println("successfully persisted Employee details"); } }
In this class, we are persisting 5 Employee records and we will perform aggregate operations on these records.
Step 7
Lets Create Main class to perform HQL Aggregate operations
- package com.kb.db;
- import org.hibernate.Session;
- import org.hibernate.SessionFactory;
- import org.hibernate.query.Query;
- import com.kb.util.HibernateUtil;
- public class AggregateFunctions {
- public static void main(String[] args) {
- // Get session factory using Hibernate Util class
- SessionFactory sf = HibernateUtil.getSessionFactory();
- // Get session from Sesson factory
- Session session = sf.openSession();
- // Retrieve Max salary of Employee
- Query maxSalaryQuery = session.createQuery("SELECT MAX(salary) FROM Employee");
- Object maxSalary = maxSalaryQuery.getSingleResult();
- System.out.println("Maximum salary of Employee -> " + maxSalary);
- // Retrieve Min salary of Employee
- Query minSalaryQuery = session.createQuery("SELECT MIN(salary) FROM Employee");
- Object minSalary = minSalaryQuery.getSingleResult();
- System.out.println("Minimum salary of Employee -> " + minSalary);
- // Retrieve Average salary of Employee
- Query avgSalaryQuery = session.createQuery("SELECT AVG(salary) FROM Employee");
- Object avgSalary = avgSalaryQuery.getSingleResult();
- System.out.println("Average salary of Employees -> " + avgSalary);
- // Retrieve Number of Employees
- Query empCountQuery = session.createQuery("SELECT COUNT(*) FROM Employee");
- Object empCount = empCountQuery.getSingleResult();
- System.out.println("Total number of Employees -> " + empCount);
- // Retrieve Total Sum of salary of all Employees
- Query sumSalaryQuery = session.createQuery("SELECT SUM(salary) FROM Employee");
- Object sumSalary = sumSalaryQuery.getSingleResult();
- System.out.println("Total sum of salary of all Employees -> " + sumSalary);
- session.close();
- }
- }
package com.kb.db; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.query.Query; import com.kb.util.HibernateUtil; public class AggregateFunctions { public static void main(String[] args) { // Get session factory using Hibernate Util class SessionFactory sf = HibernateUtil.getSessionFactory(); // Get session from Sesson factory Session session = sf.openSession(); // Retrieve Max salary of Employee Query maxSalaryQuery = session.createQuery("SELECT MAX(salary) FROM Employee"); Object maxSalary = maxSalaryQuery.getSingleResult(); System.out.println("Maximum salary of Employee -> " + maxSalary); // Retrieve Min salary of Employee Query minSalaryQuery = session.createQuery("SELECT MIN(salary) FROM Employee"); Object minSalary = minSalaryQuery.getSingleResult(); System.out.println("Minimum salary of Employee -> " + minSalary); // Retrieve Average salary of Employee Query avgSalaryQuery = session.createQuery("SELECT AVG(salary) FROM Employee"); Object avgSalary = avgSalaryQuery.getSingleResult(); System.out.println("Average salary of Employees -> " + avgSalary); // Retrieve Number of Employees Query empCountQuery = session.createQuery("SELECT COUNT(*) FROM Employee"); Object empCount = empCountQuery.getSingleResult(); System.out.println("Total number of Employees -> " + empCount); // Retrieve Total Sum of salary of all Employees Query sumSalaryQuery = session.createQuery("SELECT SUM(salary) FROM Employee"); Object sumSalary = sumSalaryQuery.getSingleResult(); System.out.println("Total sum of salary of all Employees -> " + sumSalary); session.close(); } }
Step 8
Run PopulateData.java class to create the initial data
Step 9
Check the output in MYSQL DB
E:\MySql_Install\bin
Mysql –u root –p
Enter password
Use javainsimpleway;
Select * from Employee;
We can see that Emploeyee table has 5 records.
Step 10
Run AggregateFunctions.java
Output