Hibernate Count Query

In this section we will show you, how to use the Count Query. Hibernate supports multiple aggregate functions. when they are used in HQL queries, they return an aggregate value (such as sum, average, and count) calculated from property values of all objects satisfying other query criteria. These functions can be used along with the distinct and all options, to return aggregate values calculated from only distinct values and all values (except null values), respectively. Following is a list of aggregate functions with their respective syntax; all of them are self-explanatory.

count( [ distinct | all ] object | object.property )

count(*) (equivalent to count(all ...), counts null values also)

sum ( [ distinct | all ] object.property)

avg( [ distinct | all ] object.property)

max( [ distinct | all ] object.property)

min( [ distinct | all ] object.property)

Here is the java code for counting the records from insurance table:


package roseindia.tutorial.hibernate;



import java.util.Iterator;

import java.util.List;



import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.Transaction;

import org.hibernate.cfg.Configuration;



public class HibernateHQLCountFunctions {



  /**

   * 

   */

  public static void main(String[] args) {

    // TODO Auto-generated method stub



    Session sess = null;

    int count = 0;

    try {

      SessionFactory fact = new 

Configuration().configure()

.buildSessionFactory();

      sess = fact.openSession();

      String SQL_QUERY = "select 

count(*)from Insurance insurance group by 

    insurance.lngInsuranceId";

        Query query = 

sess.createQuery(SQL_QUERY);

        for (Iterator it 

= query.iterate(); it.hasNext();) {

          it.next();

            count++;

        }

        System.out.println("

Total rows: " + count);

      sess.close();

    }

    catch(Exception e){

      System.out.println

(e.getMessage());

    }

  }



}

Download this code.

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select count(*) as col_0_0_ from insurance insurance0_ group by insurance0_.ID

Total rows: 6

Current Comments

5 comments so far (post your own) View All Comments Latest 10 Comments:

Count(*) will return the no. of records of particular id.

Posted by jysan on Wednesday, 09.24.08 @ 17:22pm | #80662

View This Comment Separately

In the code why are you counting on the result set? The aggregate function should have given you the count.

Posted by Indian on Thursday, 04.3.08 @ 22:00pm | #55253

View This Comment Separately

Its very useful to the persons who learning/starting their career in IT field...... But its not helpful for the persons who really doing in practical.... So Provide some more code and some more examples........

Posted by Girish Chowdary on Tuesday, 03.11.08 @ 18:37pm | #52341

View This Comment Separately

Modify the Query

String SQL_QUERY = "select sum insurance.investementAmount),insurance.insuranceName from Insurance insurance group by insurance.insuranceName";

Posted by sachin Sawashe on Thursday, 01.10.08 @ 15:16pm | #44898

View This Comment Separately

hi
really this sight is extremmely good for Hibernate learning persons .i am studing and practising since long time but i found some problem in this example "http://www.roseindia.net/hibernate/hibernate-count.shtml".can u suggest how i debugg the .i.e what is approach to debug the code

Thanksfull
Neeraj kr

Posted by Neeraj Kumar on Saturday, 09.29.07 @ 11:58am | #30467

View This Comment Separately