hql语法002

1、

package cn.jbit.hibernatedemo.test;

import java.util.Iterator;
import java.util.List;

import org.hibernate.*;
import org.junit.Test;

import cn.jbit.hibernatedemo.dao.HibernateUtil;
import cn.jbit.hibernatedemo.entity.DeptSalary;

public class Eg {

    /**
     * 统计部门个数。
     */
    @Test
    public void egDept() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Long count = (Long) session.createQuery(
                    "select count(*) from Dept d").uniqueResult();
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计员工工资。
     */
    @Test
    public void egEmp() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Double salarySum = (Double) session.createQuery(
                    "select sum(e.salary) from Emp e").uniqueResult();
            System.out.println(salarySum);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计员工最低工资。
     */
    @Test
    public void egEmpMinSalary() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Double salary = (Double) session.createQuery(
                    "select min(e.salary) from Emp e").uniqueResult();
            System.out.println(salary);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计员工最高工资。
     */
    @Test
    public void egEmpMaxSalary() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Double salary = (Double) session.createQuery(
                    "select max(e.salary) from Emp e").uniqueResult();
            System.out.println(salary);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计员工平均工资。
     */
    @Test
    public void egEmpAvgSalary() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Double salary = (Double) session.createQuery(
                    "select avg(e.salary) from Emp e").uniqueResult();
            System.out.println(salary);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计最低工资、最高工资以及平均工资。
     */
    @Test
    public void egEmpSalary() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Object[] salarys = (Object[]) session.createQuery(
                    "select min(salary),max(salary),avg(salary) from Emp")
                    .uniqueResult();
            System.out
                    .println(salarys[0] + "," + salarys[1] + "," + salarys[2]);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计员工职位个数。
     */
    @Test
    public void egJobEmp() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Object count = session.createQuery(
                    "select count(distinct job) from Emp ").uniqueResult();
            System.out.println(count.getClass().getName());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 按职位统计员工个数。
     */
    @Test
    public void eg1() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            List<Object[]> list = session.createQuery(
                    "select job,count(e) from Emp e group by job").list();
            for (Object[] obj : list)
                System.out.println(obj[0] + "," + obj[1]);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计各个部门的平均工资
     */
    @Test
    public void eg2() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Iterator<Object[]> it = session
                    .createQuery(
                            "select e.dept.deptName,avg(e.salary) from Emp e group by e.dept.deptName")
                    .list().iterator();
            Object[] obj = null;
            while (it.hasNext()) {
                obj = it.next();
                System.out.println(obj[0] + "," + obj[1]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计各个职位的最低工资和最高工资
     */
    @Test
    public void eg3() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Iterator<Object[]> it = session
                    .createQuery(
                            "select job,min(salary),max(salary) from Emp group by job")
                    .list().iterator();
            Object[] obj = null;
            while (it.hasNext()) {
                obj = it.next();
                System.out.println(obj[0] + "," + obj[1] + "," + obj[2]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计各个部门平均工资高于4000元的部门名称，打印部门名称、部门平均工资
     */
    @Test
    public void eg4() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Iterator<Object[]> it = session
                    .createQuery(
                            "select e.dept.deptName,avg(e.salary) from Emp "
                                    + "e group by e.dept.deptName having avg(e.salary)>4000")
                    .list().iterator();
            Object[] obj = null;
            while (it.hasNext()) {
                obj = it.next();
                System.out.println(obj[0] + "," + obj[1]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }

    /**
     * 统计各个部门平均工资高于4000元的部门名称，打印部门名称、部门平均工资，使用JavaBean封装查询结果
     */
    @Test
    public void eg4JavaBean() {
        Session session = null;
        try {
            // 获取session
            session = HibernateUtil.currentSession();
            Iterator<DeptSalary> it = session
                    .createQuery(
                            "select new cn.jbit.hibernatedemo.entity.DeptSalary(e.dept.deptName,avg(e.salary))"
                                    + " from Emp e group by e.dept.deptName having avg(e.salary)>4000")
                    .list().iterator();
            DeptSalary deptSalary = null;
            while (it.hasNext()) {
                deptSalary = it.next();
                System.out.println(deptSalary.getDeptName() + ","
                        + deptSalary.getAvgSalary());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭session
            HibernateUtil.closeSession();
        }
    }


}