Aggregate Functions(avg(…), sum(…), min(…), max(…))

Hibernate supports multiple aggregate functions. When they are used in HQL queries, they return an aggregate value ( such as avg(…), sum(…), min(…), max(…) , count(*), count(…), count(distinct …), count(all…) ) calculated from property values of all objects satisfying other query criteria.

Queries:
For max()
“SELECT MAX(rollNumber) FROM Student student”
For min():
“SELECT MIN(rollNumber) FROM Student student”

HibernateTestDemo.java

package com.sdnext.hibernate.tutorial;

import java.util.Iterator;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

public class HibernateTestDemo {
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
  Session session = sessionFactory.openSession();
  session.beginTransaction();
  String SQL_QUERY = "SELECT MAX(rollNumber) FROM Student student";
  Query query = session.createQuery(SQL_QUERY);
    
  for(Iterator it=query.iterate();it.hasNext();)
  {
   Long row = (Long) it.next();
   System.out.print("MAX ROLL NUMBER: " + row);
  }
  session.getTransaction().commit();
  session.close();
 }
}

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.annotations.Version).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select MAX(student0_.ROLL_NUMBER) as col_0_0_ from STUDENT student0_
MAX ROLL NUMBER: 8

For min() function

HibernateTestDemo.java

package com.sdnext.hibernate.tutorial;

import java.util.Iterator;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

public class HibernateTestDemo {
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
  Session session = sessionFactory.openSession();
  session.beginTransaction();
  String SQL_QUERY = "SELECT MIN(rollNumber) FROM Student student";
  Query query = session.createQuery(SQL_QUERY);
    
  for(Iterator it=query.iterate();it.hasNext();)
  {
   Long row = (Long) it.next();
   System.out.print("MIN ROLL NUMBER: " + row);
  }
  session.getTransaction().commit();
  session.close();
 }
}

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.annotations.Version).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select MIN(student0_.ROLL_NUMBER) as col_0_0_ from STUDENT student0_
MIN ROLL NUMBER: 1

Dinesh Rajput

Dinesh Rajput is the chief editor of a website Dineshonjava, a technical blog dedicated to the Spring and Java technologies. It has a series of articles related to Java technologies. Dinesh has been a Spring enthusiast since 2008 and is a Pivotal Certified Spring Professional, an author of a book Spring 5 Design Pattern, and a blogger. He has more than 10 years of experience with different aspects of Spring and Java design and development. His core expertise lies in the latest version of Spring Framework, Spring Boot, Spring Security, creating REST APIs, Microservice Architecture, Reactive Pattern, Spring AOP, Design Patterns, Struts, Hibernate, Web Services, Spring Batch, Cassandra, MongoDB, and Web Application Design and Architecture. He is currently working as a technology manager at a leading product and web development company. He worked as a developer and tech lead at the Bennett, Coleman & Co. Ltd and was the first developer in his previous company, Paytm. Dinesh is passionate about the latest Java technologies and loves to write technical blogs related to it. He is a very active member of the Java and Spring community on different forums. When it comes to the Spring Framework and Java, Dinesh tops the list!