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
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Current Comments
4 comments so far (post your own) View All Comments Latest 10 Comments: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
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
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
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