Using Min, Max and Count on HQL.

HQL supports many aggregate functions. Min,max and count are some of them.

min() function returns minimum value of specified value in the table.

max() function returns maximum value of specified value in the table.

count() function returns number of rows of the table.

Example:

package net.roseindia.main;

import java.util.*;
import net.roseindia.table.Employee;
import net.roseindia.util.HibernateUtil;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

public class AggregateFunctionHQL {
    public static void main(String[] args) {
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.openSession();
        String minHql = "Select min(emp.salary) FROM Employee emp";
        String maxHql = "Select max(emp.salary) FROM Employee emp";
        String countHql = "Select count(*) FROM Employee emp";

        Query minQuery = session.createQuery(minHql);
        Query maxQuery = session.createQuery(maxHql);
        Query countQuery = session.createQuery(countHql);
        System.out
                .println("Minimum salary in list : " + minQuery.list().get(0));
        System.out
                .println("Maximum salary in list : " + maxQuery.list().get(0));
        System.out.println("Number of Employee : " + countQuery.list().get(0));
    }
}

Output:

Hibernate: select min(employee0_.salary) as col_0_0_ from employee employee0_
Minimum salary in list : 15000
Hibernate: select max(employee0_.salary) as col_0_0_ from employee employee0_
Maximum salary in list : 220000
Hibernate: select count(*) as col_0_0_ from employee employee0_
Number of Employee : 4

Description: Here min(salary) returns the minimum salary in the employee table using HQL.

max(salary) returns the maximum salary in the employee table using HQL.

count(*) returns the number of employee in the table.

Suppose that rather than the simple SELECT MAX(emp.salary)... I want the whole Employee object, with the max. salary, how do I write that query?

Something like: SELECT e from Employee e WHERE e.salary = MAX(e.salary) Except that's wrong!