搜索
第八章Hibernate数据操作技巧8.1分组统计数据一、聚合函数（1）count（）publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();Longcount=(Long)session.createQuery(selectcount(*)fromDeptd).uniqueResult();System.out.println(部门总数：+count);}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}（2）sum（）publicclasstest{/***@paramargs*/publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();DoublesalarySum=(Double)session.createQuery(selectsum(e.salary)fromEmpe).uniqueResult();System.out.println(薪水总和：+salarySum);}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}（3）min（）publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();Doublesalary=(Double)session.createQuery(selectmin(e.salary)fromEmpe).uniqueResult();System.out.println(最低薪水：+salary);}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}（4）max（）publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();Doublesalary=(Double)session.createQuery(selectmax(e.salary)fromEmpe).uniqueResult();System.out.println(最高薪水：+salary);}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}（5）avg（）publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();Doublesalary=(Double)session.createQuery(selectavg(e.salary)fromEmpe).uniqueResult();System.out.println(平均薪水是：+salary);}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}【注意】publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();Object[]salarys=(Object[])session.createQuery(selectmin(salary),max(salary),avg(salary)fromEmp).uniqueResult();System.out.println(最低薪水：+salarys[0]+,+最高薪水：+salarys[1]+,+平均薪水：+salarys[2]);}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}uniqueResult()方法返回的是一个Object[]对象数组【经验】publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();Objectcount=session.createQuery(selectcount(distinctjob)fromEmp).uniqueResult();System.out.println(count.getClass().getName());}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}count.getClass().getName()显示查询结果类型为：java.lang.Long二、分组查询1.按职位统计员工个数。示例1：publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();ListObject[]list=session.createQuery(selectjob,count(e)fromEmpegroupbyjob).list();for(Object[]obj:list)System.out.println(obj[0]+,+obj[1]);}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}2.统计各个部门的平均工资示例2：publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactorysessionFactory=null;Sessionsession=null;try{//读取配置文件conf=newConfiguration().configure();//创建SessionFactorysessionFactory=conf.buildSessionFactory();//打开sessionsession=sessionFactory.openSession();IteratorObject[]it=session.createQuery(selecte.dept.deptName,avg(e.salary)fromEmpegroupbye.dept.deptName).list().iterator();Object[]obj=null;while(it.hasNext()){obj=it.next();System.out.println(obj[0]+,+obj[1]);}}catch(Exceptione){e.printStackTrace();}finally{//关闭sessionif(session!=null)session.close();}}}3.统计各个职位的最低工资和最高工资示例3：publicclasstest{publicstaticvoidmain(String[]args){Configurationconf=null;SessionFactoryse