Search This Blog

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

4 comments:

  1. how to the result into jlabel or other swing component?

    ReplyDelete
  2. HI, just a question, how do I retrieve the entitie instead a single string in the resultset?

    ReplyDelete
  3. One-of-a-kind and fascinating thoughts revealed in this article. Hopefully it will most likely also be practical for others as it produces helpful details for all.
    White Mailer Boxes
    Cardboard Mailer boxes

    ReplyDelete