Criteria and Aggregation

We saw aggregate functions with HQL. Criteria too has methods that provide for using aggregate functions. Consider the below code that simply fetches the count of all rows.

Count

public static void getCount() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.setProjection(Projections.rowCount());
    Integer count = (Integer) criteria.list().get(0);

    //The count value is returned as Integer

    System.out.println("No of entities is " + count);
}

The output of the method is :

select
    count(*) as y0_ 
from
    ENTITY this_
No of entities is 4

The rowCount() method returns an instance of RowCountProjection which appends the count fragment to the generated SQL. Its the first time, I have seen the * used in an SQL query by Hibernate.
This is from the RowCountProjection class:

public class RowCountProjection extends SimpleProjection {
//...
    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) 
    throws HibernateException {
        return new StringBuffer()
            .append("count(*) as y")
            .append(position)
            .append('_')
            .toString();
    }
//...
}

I decided to try out the queries I used in the HQL example earlier:

Query q = session.createQuery("select count(e) from Entity e where e.master.id = ? ");

The same using Criteria would be:

public static void getCount2() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.eq("master.id", 2L));
    criteria.setProjection(Projections.count("name"));
    Integer count = (Integer) criteria.list().get(0);
    System.out.println("No of entities is " + count);
}

The result is :

select
    count(this_.NAME) as y0_ 
from
    ENTITY this_ 
where
    this_.MASTER_ID=?
No of entities is 2

In case we need to ensure that the records are distinct:

criteria.setProjection(Projections.countDistinct("name"));

Max and Min

For max and min I had executed a combined query earlier:

"select max(e.id), min(e.id) from Entity e where e.master.id = ? "

public static void getMaxMin() {
    //("select max(e.id), min(e.id) from Entity e where e.master.id = ? "
        
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.eq("master.id", 2L));
    criteria.setProjection(
        Projections.projectionList()
            .add(Projections.max("id"))
            .add(Projections.min("id")
        )
    );
    Object[] result = (Object[]) criteria.list().get(0);
    System.out.println("Max id is  " + result[0] + " and min id is " + result[1]);
}

public static void getMaxMin() {
    //("select max(e.id), min(e.id) from Entity e where e.master.id = ? "
        
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.eq("master.id", 2L));
    criteria.setProjection(
        Projections.projectionList()
            .add(Projections.max("id"))
            .add(Projections.min("id")
        )
    );
    Object[] result = (Object[]) criteria.list().get(0);
    System.out.println("Max id is  " + result[0] + " and min id is " + result[1]);
}

The max() and min() method both return instances of AggregateProjection. The query generated is :

select
    max(this_.ID) as y0_,
    min(this_.ID) as y1_ 
from
    ENTITY this_ 
where
    this_.MASTER_ID=?
Max id is  5 and min id is 4

Avg and Sum 

The code for the aggregate methods is :

public static void getAvgSum() {
    //select avg(c.id), sum(c.id) from Child c where c.id is not null
        
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Child.class);
    criteria.add(Restrictions.isNotNull("id"));
    criteria.setProjection(
        Projections.projectionList()
            .add(Projections.avg("id"))
            .add(Projections.sum("id")
        )
    );
    Object[] result = (Object[]) criteria.list().get(0);
    System.out.println("Avg id is  " + result[0] + " and sum of id is " + result[1]);
}

The generated SQL is :

select
    avg(this_.ID) as y0_,
    sum(this_.ID) as y1_ 
from
    CHILD_ENTITY this_ 
where
    this_.ID is not null
Avg id is  3.5 and sum of id is 21