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
aggregate



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
aggregate

Labels: