In this example we create a criteria instance and implement the Projections class max, min ,avg methods.
In this example we create a criteria instance and implement the Projections class max, min ,avg methods.In this Example, We will discuss about hibernate criteria query, The class org.hibernate.criterion.Projections is used by applications as a framework for building new kinds of Projection. In this example we create a criteria instance and implement the Projections class max, min ,avg methods.
Here is the simple Example code files.
CriteriaMinMaxAverage.java
package roseindia; import java.util.Iterator; import java.util.List; import org.hibernate.Criteria; import org.hibernate.HibernateException; import org.hibernate.Session; import org.hibernate.criterion.ProjectionList; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import roseindia.bean.User; import roseindia.util.HibernateUtil; public class CriteriaMinMaxAverage { public static void main(String[] args) { Session session = HibernateUtil.getSessionFactory().openSession(); User user = null; try { // Creating a Criteria instance Criteria crit = session.createCriteria(User.class); ProjectionList prolist = Projections.projectionList(); prolist.add(Projections.max("empSalary")); prolist.add(Projections.min("empSalary")); prolist.add(Projections.avg("expInMonth")); crit.setProjection(prolist); List list = crit.list(); Iterator itr = list.iterator(); if (!itr.hasNext()) { System.out.println("No Data Available!"); } else { while (itr.hasNext()) { System.out.printf("\n"); Object[] row = (Object[]) itr.next(); for (int i = 0; i < row.length; i++) { if (i == 0) { System.out.print("Max Salary is : "); System.out.print(row[i]); System.out.printf("\n"); } if (i == 1) { System.out.print("Min Salary is : "); System.out.print(row[i]); System.out.printf("\n"); } if (i == 2) { System.out.print("average expr is (in months): "); System.out.print(row[i]); System.out.printf("\n"); } } } } } catch (HibernateException e) { e.printStackTrace(); } finally { session.close(); } } }
The bean class is as follows.
package roseindia.bean; import java.util.Date; public class User implements java.io.Serializable { private static final long serialVersionUID = 1L; private long Id = 0; private int empId = 0; private String empName = null; private String empAddr = null; private String empPhone = null; private String empEmail = null; private Date empDOB = null; private int expInMonth = 0; private double empSalary = 0.0; public User() { } public User(int empId ,String empName, String empAddr, String empPhone, String empEmail, Date empDOB, int expInMonth, double empSalary) { this.empId=empId; this.empName = empName; this.empAddr = empAddr; this.empPhone = empPhone; this.empEmail = empEmail; this.empDOB = empDOB; this.expInMonth = expInMonth; this.empSalary = empSalary; } public long getId() { return Id; } public void setId(long id) { Id = id; } public int getEmpId() { return empId; } public void setEmpId(int empId) { this.empId = empId; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } public String getEmpAddr() { return empAddr; } public void setEmpAddr(String empAddr) { this.empAddr = empAddr; } public String getEmpPhone() { return empPhone; } public void setEmpPhone(String empPhone) { this.empPhone = empPhone; } public String getEmpEmail() { return empEmail; } public void setEmpEmail(String empEmail) { this.empEmail = empEmail; } public Date getEmpDOB() { return empDOB; } public void setEmpDOB(Date empDOB) { this.empDOB = empDOB; } public int getExpInMonth() { return expInMonth; } public void setExpInMonth(int expInMonth) { this.expInMonth = expInMonth; } public double getEmpSalary() { return empSalary; } public void setEmpSalary(double empSalary) { this.empSalary = empSalary; } @Override public String toString() { return "User [empAddr=" + empAddr + ", empDOB=" + empDOB + ", empEmail=" + empEmail + ", empId=" + empId + ", empName=" + empName + ", empPhone=" + empPhone + ", empSalary=" + empSalary + ", expInMonth=" + expInMonth + "]"; } }
We use the following code to insert the values in the database table using mapping file.
DataInsert.java
package roseindia; import java.util.Date; import org.hibernate.HibernateException; import org.hibernate.Session; import org.hibernate.Transaction; import roseindia.bean.User; import roseindia.util.HibernateUtil; public class DataInsert { public static void main(String[] args) { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction transaction = null; User user = null; try { transaction = session.beginTransaction(); for (int i = 0; i < 10; i++) { user = new User( 1 * i,"Gyan " + i, "NEW DELHI" + i, "68784654654" + i, "[email protected]" + i, new Date(), 12 * i, 15398.00 * i); session.save(user); } transaction.commit(); } catch (HibernateException e) { transaction.rollback(); e.printStackTrace(); } finally { session.close(); } } }
The database table mapping is in the file User.hbm.xml and hibernate configuration file is hibernate.cfg.xml.
hibernate.cfg.xml
<?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Database connection settings --> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://localhost:3306/test</property> <property name="connection.username">root</property> <property name="connection.password"></property> <!-- JDBC connection pool (use the built-in) --> <property name="connection.pool_size">10</property> <!-- SQL dialect --> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <!-- Enable Hibernate's automatic session context management --> <property name="current_session_context_class">thread</property> <!-- Disable the second-level cache --> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <!-- Echo all executed SQL to stdout --> <property name="show_sql">true</property> <!-- Drop and re-create the database schema on startup --> <property name="hbm2ddl.auto">update</property> <mapping resource="roseindia/bean/User.hbm.xml" /> </session-factory> </hibernate-configuration>
The User.hbm.xml file is as follows-
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="roseindia.bean.User" table="EMPLOYEE"> <id name="Id" type="long" column="ID"> <generator class="native" /> </id> <property name="empId" type="int" length="100" not-null="false" column="EMP_ID" /> <property name="empName" type="java.lang.String" length="100" not-null="false" column="EMP_NAME" /> <property name="empAddr" type="java.lang.String" length="100" not-null="false" column="EMP_ADDR" /> <property name="empPhone" type="java.lang.String" length="100" not-null="false" column="EMP_PHONE" /> <property name="empEmail" type="java.lang.String" length="100" not-null="false" column="EMP_EMAIL" /> <property name="empDOB" type="java.util.Date" not-null="false" column="EMP_DOB" /> <property name="expInMonth" type="int" not-null="false" column="EXP_IN_MONTH" /> <property name="empSalary" type="double" not-null="false" column="EMP_SALARY" /> </class> </hibernate-mapping>
The HibernateUtil.java is as follows, is used to get the SessionFactory object to open session.
package roseindia.util; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; public class HibernateUtil { private static final SessionFactory sessionFactory; static { try { sessionFactory = new Configuration().configure() .buildSessionFactory(); } catch (Throwable ex) { System.err.println("Initial SessionFactory creation failed." + ex); throw new ExceptionInInitializerError(ex); } } public static SessionFactory getSessionFactory() { return sessionFactory; } }
This example give the output the values from the table according to the criteria defined.
The structure of the database table is as follows:
The resultant query generated by hibernate is as follows which produces the output as follows
Hibernate: select max(this_.EMP_SALARY) as y0_, min(this_.EMP_SALARY) as y1_, avg(this_.EXP_IN_MONTH) as y2_ from EMPLOYEE this_