Search This Blog

Loading...

Thursday, 6 December 2012

HQL and aggregrate functions

We have been testing out the varied hql functions till now. But what if we want aggregation ?
Aggregate queries are almost always  required in applications. Getting the most expensive, the last logged, total hits... whenever any statistics or reporting screens come up, the aggregate queries show up. I decided to start with count functionality:
Count()
I wrote two simple queries - the first a simple select count(*)
public static void testSimpleCount() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select count(e) from Entity e ");
    Long count = (Long) q.uniqueResult();
    System.out.println("no of entities is "+ count);
}
The generated query and outputs is :
Hibernate: 
    /* select
        count(e) 
    from
        Entity e  */  
        select
            count(entity0_.ID) as col_0_0_ 
        from
            ENTITY entity0_
no of entities is 2
As can be seen the select clause includes a count(e). Hibernate automatically substituted the entity with its id field (i.e. the Primary key for the entity) in the generated SQL.
I also executed a count query for entities of a single parent:
public static void testWhereCount() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select count(e) from Entity e where e.master.id = ? ");
    Long count = (Long) q.setLong(0, 1L).uniqueResult();
    System.out.println("no of entities is "+ count);
}
The output :
Hibernate: 
    /* select
        count(e) 
    from
        Entity e 
    where
        e.master.id = ?  */ 
        select
            count(entity0_.ID) as col_0_0_ 
        from
            ENTITY entity0_ 
        where
            entity0_.MASTER_ID=?
no of entities is 2
There could be the case where our query could return duplicate values ( e.g. when counting against a non unique column) and we need only the count of unique entries. HQL provides us with the DISTINCT keyword for the same:
public static void testCountDistinct() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select count( distinct e.name) from Entity " +
            "e where e.master.id = ? ");
    Long count = (Long) q.setLong(0, 1L).uniqueResult();
    System.out.println("no of entities is "+ count);
}
Result:
Hibernate: 
    /* select
        count( distinct e.name) 
    from
        Entity e 
    where
        e.master.id = ?  */ 
        select
            count(distinct entity0_.NAME) as col_0_0_ 
        from
            ENTITY entity0_ 
        where
            entity0_.MASTER_ID=?
no of entities is 2
Min() and Max() 
I used the below code to test out max and min functionality:
public static void testMaxMin() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select max(e.id),"+ 
                  "min(e.id) from Entity e where e.master.id = ? ");
    Object[] result =  (Object[]) q.setLong(0, 1L).list().get(0);
    System.out.println("entity with max id is " + result[0] + 
                  " and with min id is " + result[1]);
}
The result is :
Hibernate: 
    /* select
        max(e.id),
        min(e.id) 
    from
        Entity e 
    where
        e.master.id = ?  */ 
        select
            max(entity0_.ID) as col_0_0_,
            min(entity0_.ID) as col_1_0_ 
        from
            ENTITY entity0_ 
        where
            entity0_.MASTER_ID=?
entity with max id is 3 and with min id is 2
Avg() and Sum() 
The last two were tested using the below method:
public static void testAvgSum() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select avg(c.id), " +
                      "sum(c.id) from Child c where c.id is not null ");
    Object[] result =  (Object[]) q.list().get(0);
    System.out.println("Avg value among ids is " + result[0] 
                      + " and sum of all ids is " + result[1]);
}
The result is :
Hibernate: 
    /* select
        avg(c.id),
        sum(c.id) 
    from
        Child c 
    where
        c.id is not null  */ 
        select
            avg(child0_.ID) as col_0_0_,
            sum(child0_.ID) as col_1_0_ 
        from
            CHILD_ENTITY child0_ 
        where
            child0_.ID is not null
Avg value among ids is 2.5 and sum of all ids is 10

No comments:

Post a Comment