HQL语句总结

1.      单个对象的查询

[select MappedObject] from MappedObject as alias wherealias.property=param order by alias.property

•       注意

>      []中的内容可以省略

>      MappedObject为Hibernate已经加载的映射对象

>      查询条件，排序使用的都是对象的属性

>      返回包含MappedObject的List集合

•       示例：查询所有担任保安职责的员工信息

>      Hql：“fromEmployee as emp where emp.jobStations.jobStationName=?“(老版本)

>      Hql:“from Employee emp inner join emp.jobStations job wherejob.jobStationName= ?”

 

2.      HQL复杂查询

•       子查询

•       关联查询

•       函数使用

•       分页查询

•      子查询

•       in,exists

•       示例：查询担任”前台”职责的员工所涉及的部门

•       String hql = "from Dept d where d.deptId in(selectemp.dept.deptId from Employee emp inner join emp.jobStations job wherejob.jobStationName= ?) “

•       示例：查询至少有一个人的部门

•       String hql="from Dept as d where exists (fromd.employees)"

•       操纵集合

•       size、is empty

•       示例：查询部门人数多于两人的部门信息（用size处理）

•       Stirng hql ="from Dept as d where d.employees.size > ?"

•       示例：查询没有员工的部门

•       String hql=“from Dept as d where d.employees is empty”

•      关联查询

•       隐式内连接，join，left join，right join

•       示例：查询编号为” 1”的部门下面所有的员工（隐式）

•       String hql="select emp from Dept as d,Employee emp whered.deptId = emp.dept.deptId and d.deptId = 1 “

•       示例：查询编号为” 1”的部门下面所有的员工（join）

•       String hql=“select emp from Dept as d join d.employees emp whered.deptId = 1“

•      函数使用

•       count、max….

•       示例：按照部门查询年龄最大的员工

•       String hql =" select max(emp.empAge),emp.dept.deptId fromEmployee emp group by emp.dept.deptId "

•      分页查询

•       query.setFirstResult(startIndex)//startIndex:从第几条开始查

•       query.setMaxResult(endIndex)//endIndex:往后面查几条

 

测试：

public class HqlDemo1 {

    public static void main(String[] args) {

       HqlDemo1 hd = new HqlDemo1();

       // 测试TestOne

       // hd.queryDeptDesc("技术部");

       // hd.addDept();

       // hd.updateDept();

       // hd.QueryEmployeeByJob();

 

       // hd.qryDeptsByJob();

       // hd.qryNumName();

 

       // hd.qryDeptAndEmpNum();

       // hd.qryAboveTwoBySize();

 

       hd.qryUniqueJobStation();

    }

 

    // ********************编写测试类TestOne***********************

    /**

     * 查询名称为“技术部”的部门描述信息

     * */

    public Dept queryDeptDesc(String deptName) {

       Session session = HibernateSessionFactory.getSession();

       String hql = "from Dept dwhere d.deptName = ?";

       Query query = session.createQuery(hql);

       query.setString(0, deptName);

       Dept dept = (Dept) query.uniqueResult();

       System.out.println(dept.getDeptDesc());

       session.close();

       return dept;

    }

 

    /**

     * 添加一个名称为“市场推广”的部门

     * 注意：修改主键生成策略为：identity

     *

     * 用sql完成，查看TestOne

     * */

    public void addDept() {

       Session session = HibernateSessionFactory.getSession();

       Dept dept = new Dept();

       dept.setDeptName("市场推广");

       dept.setDeptNo("010");

       Transaction trans = session.beginTransaction(); // 开启一个事务

       session.save(dept); // 保存

       trans.commit();

       session.close();

    }

 

    /**

     * 将名称为”市场推广”的部门的描述信息修改为”推广市场”

     *

     * 用sql完成，查看TestOne

     * */

    public void updateDept() {

       Dept dept = this.queryDeptDesc("市场推广"); // 调用已写方法

       dept.setDeptDesc("推广市场");

       Session session = HibernateSessionFactory.getSession();

       Transaction trans = session.beginTransaction(); // 涉及到增、改的，要开启一个事务

       session.update(dept); // 更新

       trans.commit();

       session.close();

    }

 

    /**

     * 查询编号为”J001”的职位由哪几个雇员承担

     * */

    public void QueryEmployeeByJob() {

       Session session = HibernateSessionFactory.getSession();

       String hql = "select empfrom Employee emp inner join emp.jobStations job"

              + " wherejob.jobStationId = ?";

       Query query = session.createQuery(hql);

       query.setString(0, "J001");

       List<Employee> emps = query.list();

       for (Employee employee : emps) {

           System.out.println(employee.getEmpFirstName() + ":"

                  + employee.getEmpLastName());

       }

       session.close();

    }

 

    // ********************编写测试类TestTwo***********************

    /**

     * 查询担任编号为”J001”岗位的员工所涉及的部门信息

     * 子查询

     * */

    public void qryDeptsByJob() {

       Session session = HibernateSessionFactory.getSession();

       String hql = "from Dept dwhere d.deptId in (select emp.dept.deptId from Employee emp "

              + "inner joinemp.jobStations job where job.jobStationId = ?)";

       Query query = session.createQuery(hql);

       query.setString(0, "J001");

       List<Dept> depts = query.list();

       for (Dept dept : depts) {

           System.out.println(dept.getDeptName());

       }

       session.close();

    }

 

    /**

     * 查询姓名为两个字的雇员信息

     * */

    public void qryNumName() {

       Session session = HibernateSessionFactory.getSession();

       String hql = "fromEmployee emp where emp.empFirstName like '_' and emp.empLastName like '_'";

       Query query = session.createQuery(hql);

       List<Employee> emps = query.list();

       for (Employee employee : emps) {

           System.out.println(employee.getEmpFirstName() + ":"

                  + employee.getEmpLastName());

       }

       session.close();

    }

 

    // ********************编写测试类TestThree***********************

    /**

     * 查询所有部门的名字,及该部门包含的员工数量

     * PS:自己的方法同样可行，查看TestThree

     * */

    public void qryDeptAndEmpNum() {

       Session session = HibernateSessionFactory.getSession();

       String hql = "selectdept.deptName, count(*) from Dept dept inner join dept.employees emp "

              + "group bydept.deptName";

       Query query = session.createQuery(hql);

       List<Object[]> emps = query.list();

       for (Object[] values : emps) {

           System.out.println(values[0] + ":" + values[1]);

       }

       session.close();

    }

 

    /**

     * 查询多于2名员工的部门信息

     * */

    public void qryAboveTwoBySize() {

       Session session = HibernateSessionFactory.getSession();

       String hql = "from Deptdept where dept.employees.size >= 2";

       Query query = session.createQuery(hql);

       List<Dept> depts = query.list();

       for (Dept dept : depts) {

           System.out.println(dept.getDeptName());

       }

       session.close();

    }

 

    // ********************编写测试类TestFour***********************

    /**

     * 往数据库中初始化3个名字相同的职能(其他字段自行设置)。要求：查询所有职能,如果多个

     * 职能的名称相同，则取其中任意一个完整的职能信息

     *

     * 涉及到子查询

     * */

    public void qryUniqueJobStation() {

       Session session = HibernateSessionFactory.getSession();

       String hql = "fromJobStation job1 where job1.jobStationId in "

              + "(selectmax(job.jobStationId) from JobStation job group "

              + "byjob.jobStationName)";

       Query query = session.createQuery(hql);

       List<JobStation> jobs = query.list();

       for (JobStation job : jobs) {

           System.out.println(job.getJobStationName());

       }

       session.close();

    }

}