hb : HCQL(Aggregate Functions)

HCQL (Hibernate Criteria Query Language)

Employee.java

=========

package mypack;

import javax.persistence.*;

@Entity

@Table(name = "MYEMP")

public class Employee {

@Id

private int id;

private String name;

private float salary;

private String address;

public Employee() {

          super();

}

public Employee(String name, float salary,String address) {

          super();

          this.name = name;

          this.salary = salary;

        this.address = address;

}

public int getId() {

          return id;

}

public void setId(int id) {

          this.id = id;

}

public String getName() {

          return name;

}

public void setName(String name) {

          this.name = name;

}

public float getSalary() {

          return salary;

}

public void setSalary(float salary) {

          this.salary = salary;

}

    public String getAddress() {

        return address;

    }

    public void setAddress(String address) {

        this.address = address;

    }

}

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>

        <property name="connection.driver_class">org.apache.derby.jdbc.ClientDriver</property>       

        <property name="dialect">org.hibernate.dialect.DerbyDialect</property>

        <property name="connection.url">jdbc:derby://localhost:1527/myhb</property>

        <property name="connection.username">app</property>

        <property name="connection.password">app</property>

        <property name="show_sql">true</property>

        <property name="hbm2ddl.auto">update</property>  

        <mapping class="mypack.Employee"/>

    </session-factory>

</hibernate-configuration>

AverageSalary.java

==============

package mypack.aggregations;

import mypack.*;

import java.util.*;

import org.hibernate.*;

import org.hibernate.cfg.*;

import org.hibernate.criterion.Projections;

public class AverageSalary {

            

    public static void main(String[] args) {

       SessionFactory   sessionFactory = new AnnotationConfiguration().configure("hibernate.cfg.xml").buildSessionFactory();

       Session  session = sessionFactory.openSession();

       Criteria cr = session.createCriteria(Employee.class);

       // To get Avg salary.

       cr.setProjection(Projections.avg("salary"));

       List avgSalary = cr.list();

       System.out.println("Avg Salary: " + avgSalary.get(0) );

       session.close();    

    }

}

MaxSalary.java

==========

package mypack.aggregations;

import mypack.*;

import java.util.*;

import org.hibernate.*;

import org.hibernate.cfg.*;

import org.hibernate.criterion.Projections;

public class MaxSalary {             

    public static void main(String[] args) {

    SessionFactory   sessionFactory = new AnnotationConfiguration().configure("hibernate.cfg.xml").buildSessionFactory();

    Session  session = sessionFactory.openSession();

    Criteria cr = session.createCriteria(Employee.class);

    // To get Maximum salary.

    cr.setProjection(Projections.max("salary"));

    List maxSalary = cr.list();

    System.out.println("Maximum Salary: " + maxSalary.get(0) );

    session.close();    

    }

}

MinSalary.java

=========

package mypack.aggregations;

import mypack.*;

import java.util.*;

import org.hibernate.*;

import org.hibernate.cfg.*;

import org.hibernate.criterion.Projections;

public class MinSalary {                 

    public static void main(String[] args) {

     SessionFactory   sessionFactory = new AnnotationConfiguration().configure("hibernate.cfg.xml").buildSessionFactory();

     Session  session = sessionFactory.openSession();

     Criteria cr = session.createCriteria(Employee.class);

       // To get Minmum salary.

     cr.setProjection(Projections.min("salary"));

     List minSalary = cr.list();

     System.out.println("Minimum Salary: " + minSalary.get(0) );

     session.close();    

    }

}

SumSalary.java

==========

package mypack.aggregations;

import mypack.*;

import java.util.*;

import org.hibernate.*;

import org.hibernate.cfg.*;

import org.hibernate.criterion.Projections;

public class SumSalary {      

    public static void main(String[] args) {

       SessionFactory   sessionFactory = new AnnotationConfiguration().configure("hibernate.cfg.xml").buildSessionFactory();

       Session  session = sessionFactory.openSession();

       Criteria cr = session.createCriteria(Employee.class);

       // To get total salary.

       cr.setProjection(Projections.sum("salary"));

       List totalSalary = cr.list();

       System.out.println("Total Salary: " + totalSalary.get(0) );

       session.close();    

    }

}

CountEx.java

=========

package mypack.aggregations;

import mypack.*;

import java.util.*;

import org.hibernate.*;

import org.hibernate.cfg.*;

import org.hibernate.criterion.Projections;

public class CountEx {     

    public static void main(String[] args) {

        SessionFactory   sessionFactory = new AnnotationConfiguration().configure("hibernate.cfg.xml").buildSessionFactory();

        Session  session = sessionFactory.openSession();

        Criteria cr = session.createCriteria(Employee.class);

        // To get total row count.

        cr.setProjection(Projections.rowCount());

        List rowCount = cr.list();

        System.out.println("Total Count: " + rowCount.get(0) );

        session.close();

    }

}