第八章 Hibernate数据操作技巧

1、第八章 Hibernate数据操作技巧8.1 分组统计数据一、聚合函数（1）count（）public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/

2、 打开sessionsession = sessionFactory.openSession();Long count = (Long) session.createQuery("select count(*) from Dept d").uniqueResult();System.out.println("部门总数："+count); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();（2）sum（）public

3、 class test /* * param args */public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactor

4、y.openSession();Double salarySum = (Double) session.createQuery("select sum(e.salary) from Emp e").uniqueResult();System.out.println("薪水总和："+salarySum); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();（3）min（）public class test publi

5、c static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Double salary = (Do

6、uble) session.createQuery("select min(e.salary) from Emp e").uniqueResult();System.out.println("最低薪水："+salary); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();（4）max（）public class test public static void main(String args) Configura

7、tion conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Double salary = (Double) session.createQuery("select ma

8、x(e.salary) from Emp e").uniqueResult();System.out.println("最高薪水："+salary); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();（5）avg（）public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFa

9、ctory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Double salary = (Double) session.createQuery("select avg(e.salary) from Emp e").uniqueResul

10、t();System.out.println("平均薪水是："+salary); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();【注意】public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取

11、配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Object salarys = (Object) session.createQuery("select min(salary),max(salary),avg(salary) from Emp").uniqueResult();System.out.println(

12、"最低薪水："+salarys0 + ", " +"最高薪水："+ salarys1 + ", " +"平均薪水："+ salarys2); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();uniqueResult()方法返回的是一个Object对象数组【经验】public class test public static void main(Strin

13、g args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Object count = session.createQuery("se

14、lect count(distinct job) from Emp ").uniqueResult();System.out.println(count.getClass().getName(); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();count.getClass().getName()显示查询结果类型为：java.lang.Long二、分组查询1. 按职位统计员工个数。示例1：public class test public st

15、atic void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();List<Object> list

16、 = session.createQuery("select job,count(e) from Emp e group by job").list();for (Object obj : list)System.out.println(obj0 + "," + obj1); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();2. 统计各个部门的平均工资示例2：public class test public st

17、atic void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Iterator<Object>

18、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(obj0 + "," + obj1); catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (sess

19、ion != null)session.close();3. 统计各个职位的最低工资和最高工资示例3： public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFact

20、ory();/ 打开sessionsession = sessionFactory.openSession();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(obj0 + "," + obj1 + "

21、;," + obj2);catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();4. 统计各个部门平均工资高于2000元的部门名称，打印部门名称、部门平均工资示例4：public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try

22、 / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Iterator<Object> it = session.createQuery("select e.dept.deptName,avg(e.salary) from Emp "+ "e group by e.dept.deptName

23、 having avg(e.salary)>2000").list().iterator();Object obj = null;while (it.hasNext() obj = it.next();System.out.println(obj0 + "," + obj1);catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();【经验】统计各个部门平均工资高于4000元的部门名称，打印部门名称、部门平均工资，使用Java

24、Bean封装查询结果1. JavaBean封装DeptSalarypackage entity;public class DeptSalary private String deptName;private Double avgSalary;public DeptSalary(String deptName, Double avgSalary) super();this.deptName = deptName;this.avgSalary = avgSalary;public String getDeptName() return deptName;public void setDeptNam

25、e(String deptName) this.deptName = deptName;public Double getAvgSalary() return avgSalary;public void setAvgSalary(Double avgSalary) this.avgSalary = avgSalary;2.编写测试类public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session

26、 = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Iterator<DeptSalary> it = session.createQuery("select new entity.DeptSalary(e.dept.deptName,avg(e.salary)"+ "

27、; from Emp e group by e.dept.deptName having avg(e.salary)>2000").list().iterator();DeptSalary deptSalary = null;while (it.hasNext() deptSalary = it.next();System.out.println(deptSalary.getDeptName() + ","+ deptSalary.getAvgSalary();catch (Exception e) e.printStackTrace(); finally

28、/ 关闭sessionif (session != null)session.close();运行结果相同8.2 子查询1. 子查询关键字如果子查询语句返回多条记录，可以使用以下的关键字进行量化。allanysomeinexists（1）查询所有员工工资都小于5000元的部门示例5：public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf

29、= new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();List<Dept> list = session.createQuery("from Dept d where 5000>all(select e.salary from d.emps e) and d.emps.size>0").list();for (Dep

30、t dept : list) System.out.println("部门的名称是："+dept.getDeptName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();（2）查询至少有一位员工的工资低于5000元的部门示例6：public class test public static void main(String args) Configuration conf = null;SessionFactory sessio

31、nFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();List<Dept> list = session.createQuery("from Dept d where 5000>any(select e.salary fr

32、om d.emps e)").list();for (Dept dept : list) System.out.println("部门名称是："+dept.getDeptName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();（3）查询有员工工资正好是5000元的部门示例7-1：public class test public static void main(String args) Configuration c

33、onf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();List<Dept> list = session.createQuery("from Dept d wher

34、e 5000=any(select e.salary from d.emps e)").list();for (Dept dept : list) System.out.println("部门名称是："+dept.getDeptName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();示例7-2：public class test public static void main(String args) Configu

35、ration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();List<Dept> list = session.createQuery("from Dep

36、t d where 5000=some(select e.salary from d.emps e)").list();for (Dept dept : list) System.out.println("部门名称是："+dept.getDeptName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();示例7-3：public class test public static void main(String args

37、) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();List<Dept> list = session.createQuery("

38、;from Dept d where 5000 in (select e.salary from d.emps e)").list();for (Dept dept : list) System.out.println("部门名称："+dept.getDeptName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();（4）查询至少有一位员工的部门示例8：public class test public static v

39、oid main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();List<Dept> list = sessi

40、on.createQuery("from Dept d where exists (from d.emps)").list();for (Dept dept : list) System.out.println("部门名称是："+dept.getDeptName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();8.4 原生SQL查询和命名查询一、原生SQL查询使用HQL查询时，Hibernate会生成标准的S

41、QL查询语句，适用于所有的数据库平台，因此HQL是跨数据库平台的。在实际开发中，有时很可能需要使用底层数据库的SQL特性，来生成一些特殊的查询语句。Hibernate提供了原生SQL的查询方式来支持这一需求。1. 查询姓名中包括字符“e”的，并且职位是“ENGINEER”的Emp对象示例11：待查询的数据源如下表：public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;t

42、ry / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();Query query = session.createSQLQuery("select * from EMP where ENAME like :ename and JOB = :job").setString("ename", &q

43、uot;%E%").setString("job", "MANAGER");List<Object> list = query.list();for (Object obj : list) System.out.println("员工编号："+obj0 + ",部门编号" + obj1);catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();运行结果：2.

44、SQLQuery接口提供了addEntity()方法把查询结果集中的关系数据映射为对象。示例12：public class test public static void main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory

45、();/ 打开sessionsession = sessionFactory.openSession();Query query = session.createSQLQuery("select * from EMP where ENAME like :ename and JOB = :job").addEntity(Emp.class).setString("ename", "%E%").setString("job", "MANAGER");List<Emp> list = qu

46、ery.list();for (Emp emp : list) System.out.println("员工编号："+emp.getEmpNo() + ",员工姓名：" + emp.getEmpName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session != null)session.close();3. 查询指定职位的员工，并打印员工姓名和所在部门名称示例13：待查询的数据源如下表：public class test public static void

47、main(String args) Configuration conf = null;SessionFactory sessionFactory = null;Session session = null;try / 读取配置文件conf = new Configuration().configure();/ 创建SessionFactorysessionFactory = conf.buildSessionFactory();/ 打开sessionsession = sessionFactory.openSession();String sql = "select e.*,d.*

48、 from EMP e join DEPT d on d.DEPTNO=e.DEPTNO"+ " where e.JOB = :job"Query query = session.createSQLQuery(sql).addEntity("e", Emp.class).addJoin("d", "e.dept").setString("job", "MANAGER");List<Object> list = query.list();Emp emp =

49、 null;Dept dept = null;for (Object obj : list) System.out.println(obj0 + " , " + obj1);emp = (Emp) obj0;dept = (Dept) obj1;System.out.println("员工姓名："+emp.getEmpName() + "，部门名称：" + dept.getDeptName();catch (Exception e) e.printStackTrace(); finally / 关闭sessionif (session

50、 != null)session.close();使用了SQLQuery接口的addJoin（）方法，建立了Emp对象和Dept对象之间的关联，list（）方法返回的List集合中存放的是Object数组对象，数组的第一个元素是Emp对象，第二个元素是Dept对象。二、命名查询Hibernate支持在映射文件中定义字符串形式的查询语句，这样的查询语句称为命名查询语句。1.HQL查询语句的命名查询示例14：Emp.hbm.xml<?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC "-/Hi

51、bernate/Hibernate Mapping DTD 3.0/EN" "<hibernate-mapping> <class name="entity.Emp" table="emp" schema="scott"> <cache usage="read-write"/> <id name="empNo" column="empno" type="java.lang.Integer">

52、; <generator class="assigned"/> </id> <property name="empName" type="java.lang.String" column="ename"/> <property name="job" type="java.lang.String" column="job"/> <property name="salary" type=

53、"java.lang.Double" column="sal"/> <property name="hireDate" type="java.util.Date"/> <many-to-one name="dept" column="DEPTNO" class="entity.Dept" lazy="no-proxy"></many-to-one> </class> <query name="findEmpByJob"> <!CDATA from