第八章 Hibernate数据操作技巧_第1页
第八章 Hibernate数据操作技巧_第2页
第八章 Hibernate数据操作技巧_第3页
第八章 Hibernate数据操作技巧_第4页
第八章 Hibernate数据操作技巧_第5页
已阅读5页,还剩22页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

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

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论