Hibernate Criteria average example

Hibernate Criteria average example - Learn how to perform Hibernate Criteria Average Query

Here we will use the avg() method of org.hibernate.criterion.Projections factory for getting the Projection instance for performing the average on the 'salary" field of 'emp_salary' table.

In this example we will use the org.hibernate.criterion.Projections interface of the Hibernate Criteria API which is used to find the average, maximum or minimum values.

Our example will return the average of employee salary.

The Hibernate finally generates the following query for getting the results from database:

select avg(this_.salary) as y0_ from emp_salary this_

Here is SQL to create table in MySQL Database:

CREATE TABLE `emp_salary` (                              
   `id` int(11) NOT NULL AUTO_INCREMENT,                  
   `emp_id` int(11) DEFAULT NULL,                         
   `salary` int(11) DEFAULT NULL,                         
    PRIMARY KEY (`id`)                                     
  ) ENGINE=InnoDB;

After creating the table you should enter few records for checking the example.

Here is the screen shot of Eclipse Project:

Here is the screen shot of the code:

Here is the source code of the model class used:

package net.roseindia.model;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
/**
 * @author Deepak Kumar
 * Web: http://www.roseindia.net
 */
@Entity
@Table(name = "emp_salary")
public class EmpSalary implements Serializable{

	@Id
	@GeneratedValue
	@Column(name="id")
	private int id;	

	@Column(name="emp_id")
	private int empId;

	@Column(name="salary")
	private int salary;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public int getEmpId() {
		return empId;
	}

	public void setEmpId(int empId) {
		this.empId = empId;
	}

	public int getSalary() {
		return salary;
	}

	public void setSalary(int salary) {
		this.salary = salary;
	}	  
}

Here is the code of hibernate.cfg.xml file:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
	<session-factory>
		<property name="hibernate.connection.driver_class">
                  com.mysql.jdbc.Driver</property>
		<property name="hibernate.connection.url">
                jdbc:mysql://localhost:3306/hibernate4</property>
		<property name="hibernate.connection.username">root</property>
		<property name="hibernate.connection.password">root</property>
		<property name="hibernate.connection.pool_size">10</property>
		<property name="show_sql">true</property>
		<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
		<property name="hibernate.current_session_context_class">thread</property>
		<mapping class="net.roseindia.model.Employee" />
		<mapping class="net.roseindia.model.EmpSalary" />

	</session-factory>
</hibernate-configuration>

In the above xml file we have created a mapping entry for the class EmpSalary as shown below:

<mapping class="net.roseindia.model.EmpSalary" />

Here is the code of the Java file for testing the example:

package net.roseindia;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Projections;

import net.roseindia.model.*;

/**
 * @author Deepak Kumar Web: http://www.roseindia.net
 */

public class HibernateCriteriaAverageExample {
	public static void main(String[] args) throws Exception {

		SessionFactory sessFact = HibernateUtil.getSessionFactory();
		Session session = sessFact.getCurrentSession();
		org.hibernate.Transaction tr = session.beginTransaction();

		Criteria criteria = session.createCriteria(EmpSalary.class);
		criteria.setProjection(Projections.avg("salary"));
		List listAvgSalary = criteria.list();
		
		Double avgSalary = (Double)listAvgSalary.get(0);
		
		System.out.println("Average Salary is:" + avgSalary);

		tr.commit();
		
		sessFact.close();
	}
}

If you run the program it will give following output:

In this section you have learned how to find the average using the Criteria Query. Read more Hibernate Criteria Query examples.

Download the Source code of the project discussed here.

Check more Hibernate 4.2 tutorials.