|
In this section, we will show you, how to use the avg()
function. Hibernate supports multiple aggregate functions. When they are used in
HQL queries, they return an aggregate value ( such as avg(...),
sum(...), min(...), max(...) , count(*), count(...), count(distinct ...),
count(all...) ) calculated from property values of all objects satisfying
other query criteria.
Following is a aggregate function (avg() function) with
their respective syntax.
avg(
[ distinct | all ] object.property):
The avg() function aggregates the average value
of the given column.
Table Name: insurance
| ID | insurance_name | invested_amount | investement_date | | 2 | Life Insurance | 25000 | 0000-00-00 00:00:00 | | 1 | Givan Dhara | 20000 | 2007-07-30 17:29:05 | | 3 | Life Insurance | 500 | 2005-10-15 00:00:00 | | 4 | Car Insurance | 2500 | 2005-01-01 00:00:00 | | 5 | Dental Insurance | 500 | 2004-01-01 00:00:00 | | 6 | Life Insurance | 900 | 2003-01-01 00:00:00 | | 7 | Travel Insurance | 2000 | 2005-02-02 00:00:00 |
Here is the java code to retrieve the average value of
"invested_amount" column from insurance table:
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class HibernateHQLAvgFunction {
public static void main(String[] args) {
// TODO Auto-generated method stub
Session sess = null;
try {
SessionFactory fact = new Configuration()
.configure().buildSessionFactory();
sess = fact.openSession();
String SQL_QUERY = "select avg
(investementAmount) from Insurance insurance";
Query query = sess.createQuery(SQL_QUERY);
List list = query.list();
System.out.println("Average of
Invested Amount: " + list.get(0));
}
catch(Exception e){
System.out.println(e.getMessage());
}
}
} |
