聚合函数：
实例：
package Test; import static org.junit.Assert.*; import java.util.List; import org.hibernate.Session;
import org.junit.Test; import entity.AvgSal; import util.HibernateSessionFactory;
import util.HibernateUtil; public class AggTest { /**
* 统计部门个数。
*/
@Test
public void test1() {
Session session =HibernateSessionFactory.getSession();
try {
Long count = (Long) session.createQuery("select count(d) from Dept d").uniqueResult();
System.out.println("共有"+count+"个部门。");
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
} /**
* 统计员工工资。
*/
@Test
public void test2() {
Session session =HibernateSessionFactory.getSession();
try {
Double totalsal = (Double) session.createQuery("select sum(e.sal) from Emp e").uniqueResult();
System.out.println("员工工资总数为："+totalsal);
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
} /**
* 统计员工最低工资。
*/
@Test
public void test3() {
Session session =HibernateSessionFactory.getSession();
try {
Double minsal = (Double) session.createQuery("select min(e.sal) from Emp e").uniqueResult();
System.out.println("员工工资最低为："+minsal);
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
} /**
* 统计员工最高工资。
*/
@Test
public void test4() {
Session session =HibernateSessionFactory.getSession();
try {
Double maxsal = (Double) session.createQuery("select max(e.sal) from Emp e").uniqueResult();
System.out.println("员工工资最高为："+maxsal);
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
}
/**
* 统计员工平均工资。
*/
@Test
public void test5() {
Session session =HibernateSessionFactory.getSession();
try {
Double avgsal = (Double) session.createQuery("select avg(e.sal) from Emp e").uniqueResult();
System.out.println("员工平均工资为："+avgsal);
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
}
/**
* 统计最低工资、最高工资以及平均工资。
*/
@Test
public void test6() {
Session session =HibernateSessionFactory.getSession();
try {
List<Object[]> list = session.createQuery("select min(e.sal),max(e.sal),avg(e.sal) from Emp e").list();
for (Object[] objects : list) {
Double minsal=(Double) objects[0];
Double maxsal=(Double) objects[1];
Double avgsal=(Double) objects[2];
System.out.println("最低工资为："+minsal+"，最高工资为："+maxsal+",平均工资为："+avgsal);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
}
/**
* 统计员工职位个数。
*/
@Test
public void test7() {
Session session =HibernateSessionFactory.getSession();
try {
Long jobcount = (Long) session.createQuery("select count(distinct e.job) from Emp e").uniqueResult();
System.out.println("共有"+jobcount+"种职位");
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
} /**
* 按职位统计员工个数。
*/
@Test
public void test8() {
Session session =HibernateSessionFactory.getSession();
try {
List<Object[]> list = session.createQuery("select job,count(ename) from Emp group by job").list();
System.out.println("按职位统计员工个数");
for (Object[] objects : list) {
String job=(String) objects[0];
Long count=(Long) objects[1];
System.out.println(job+"职位======"+count+"个员工");
} } catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
} /**
* 统计各个部门的平均工资
*/
@Test
public void test9() {
Session session =HibernateSessionFactory.getSession();
try {
List<Object[]> list = session.createQuery("select job,avg(sal) from Emp group by job").list();
System.out.println("按职位统计每个职位员工的平均工资");
for (Object[] objects : list) {
String job=(String) objects[0];
Double avg=(Double) objects[1];
System.out.println(job+"职位平均工资是======"+avg);
} } catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
}
/**
* 统计各个职位的最低工资和最高工资
*/ @Test
public void test10() {
Session session =HibernateSessionFactory.getSession();
try {
List<Object[]> list = session.createQuery("select job,max(sal),min(sal) from Emp group by job").list();
System.out.println("统计各个职位的最低工资和最高工资");
for (Object[] objects : list) {
String job=(String) objects[0];
Double max=(Double) objects[1];
Double min=(Double) objects[2];
System.out.println(job+"职位最高工资是======"+max+",最低工资是：===="+min);
} } catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
} /**
* 统计各个部门平均工资高于2000元的部门名称，打印部门名称、部门平均工资
*/
@Test
public void test11() {
Session session =HibernateSessionFactory.getSession();
try {
List<Object[]> list = session.createQuery("select dept.dname,avg(sal) from Emp group by dept.dname having avg(sal)>2000").list();
System.out.println("统计各个部门平均工资高于2000元的部门名称，打印部门名称、部门平均工资");
for (Object[] objects : list) {
String dept=(String) objects[0];
Double avg=(Double) objects[1];
System.out.println(dept+"\t"+avg);
} } catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
}
/**
* 统计各个部门平均工资高于2000元的部门名称，打印部门名称、部门平均工资，使用JavaBean封装查询结果
*/
@Test
public void test12() {
Session session =HibernateSessionFactory.getSession();
try {
List<AvgSal> list = session.createQuery("select new entity.AvgSal(dept.dname,avg(sal) )from Emp group by dept.dname having avg(sal)>2000").list();
System.out.println("统计各个部门平均工资高于2000元的部门名称，打印部门名称、部门平均工资，使用JavaBean(entity.AvgSal)封装查询结果");
for (AvgSal avgSal : list) {
System.out.println(avgSal.getDname()+"\t"+avgSal.getAvgsal());
} } catch (Exception e) {
e.printStackTrace();
System.out.println("失败");
}
} }
