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(); } }
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(); } }
Hibernate: select MIN(student0_.ROLL_NUMBER) as col_0_0_ from STUDENT student0_
MIN ROLL NUMBER: 1
q.list().get(0)