hibernate 统计函数
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 {
Listlist = 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 { Listlist = 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 { Listlist = 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 { Listlist = 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 { Listlist = 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 { Listlist = 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("失败");
}
}
}
