1 为什么需要学习HQL（hibernate query language）？

面向对象的查询语言，与SQL不同，HQL中的对象名是区分大小写的（除了Java类和属性，其他部分不区分大小写）； HQL中查的是对象而不是表，并且支持多态； HQL**主要通过Query来操作**Query q = session.createQuery(hql);

2 Hibernate HQL

Hibernate 工具类 http://blog.csdn.net/u013943420/article/details/70908878#t1

使用的表结构和测试数据

create table student( sid number primary key, sname varchar2(45) not null, ssex char(2) not null, sdept varchar2(10) not null, sage number(3), saddress varchar2(45) ); insert into student values(2004001, '林青霞', 'F', '计算机系', 22, '上海'); insert into student values(2004002, '刘德华', 'M', '外语系', 23, '南京'); insert into student values(2004003, '成龙', 'M', '化学系', 21, '山东'); insert into student values(2004004, '林可心', 'F', '计算机系', 22, '北京'); insert into student values(2004005, '周华健', 'M', '生物系', 24, '山东'); insert into student values(2004006, '周润发', 'M', '数学系', 20, '湖北'); create table course( cid number primary key, cname varchar2(50) not null, ccredit number(3) ); insert into course values(11, 'java编程', 6); insert into course values(21, 'c++课程', 4); insert into course values(31, 'oracle', 3); insert into course values(41, 'javaEE', 100); insert into course values(51, 'linux', 1); create table studCourse( stuCourseId number primary key, sid number references student(sid), cid number references course(cid), grade number not null ); insert into studCourse values(stucourse_seq.nextval, 2004001, 11, 90); insert into studCourse values(stucourse_seq.nextval, 2004001, 21, 19); insert into studCourse values(stucourse_seq.nextval, 2004003, 21, 45); insert into studCourse values(stucourse_seq.nextval, 2004004, 41, 99); insert into studCourse values(stucourse_seq.nextval, 2004006, 11, 39); create sequence stucourse_seq start with 1 increment by 1 minvalue 1 nomaxvalue nocycle nocache

①数据库表映射

使用Hibernate工具，自动生成domain对象和映射文件，如果表有主外键的关系，则应当先映射主表，再映射从表。

②检索Student的全部属性

查询全部属性：from Student 查询部分属性：select sid, sname, saddress from Student

Session session = null; Transaction ts = null; try { session = HibernateUtil.getCurrentSession(); ts = session.beginTransaction(); // 检索所有学生信息 List<Student> list = session.createQuery("from Student").list(); for(Student stu: list) { System.out.println(stu.getSid() + " " + stu.getSname() + " " + stu.getSaddress()); } Iterator<Student> it = list.iterator(); while(it.hasNext()) { System.out.println(it.next().getSid() + " " + it.next().getSname() + " " + it.next().getSaddress()); } ts.commit(); } catch (Exception e) { e.printStackTrace(); if(ts!=null) { ts.rollback(); } throw new RuntimeException(e.getMessage()); } finally { if(session!=null && session.isOpen()) { session.close(); } }

③检索Student的部分属性

检索学生的部分属性信息（Hibernate建议我们把整个对象的所有属性都查询出来）

// 核心代码 List list = session.createQuery("select sid, sname, saddress from Student").list(); for(int i=0; i<list.size(); i++) { Object objs[] = (Object[]) list.get(i); System.out.println(objs[0].toString() + " " + objs[1].toString() + " " + objs[2].toString()); } //------------------当查询只返回一列数据时---------- List list = session.createQuery("select count(*) from Student group by sdept").list(); // list中放置的是一个对象，而不是对象数组 for(int i=0; i<list.size(); i++) { Object objs = list.get(i); System.out.println(objs); }

④uniqueResult

uniqueResult:返回结果顶多有一个对象时，可以使用*uniqueResult()得到结果；但是，如果结果是多条，使用该方法就会抛出异常。*

Student stu = (Student) session.createQuery("from Student where sid='2004003'").uniqueResult(); if(stu!=null) { System.out.println(stu.getSid() + " " + stu.getSname() + " " + stu.getSaddress()); }

⑤过滤重复值distinct 及 between…and 和 in 与 not in的使用

// distinct List list = session.createQuery("select distinct sage, ssex from Student").list(); // between...and List list = session.createQuery("select distinct sage, ssex from Student where sage between 20 and 22").list(); // in 和 not in List list = session.createQuery("select sage, ssex, sname from Student where sdept in ('计算机系', '外语系')").list(); for(int i=0; i<list.size(); i++) { Object objs[] = (Object[]) list.get(i); System.out.println(objs[0].toString() + " " + objs[1].toString()); }

⑥分组查询group by 与 order by 及 having

List list = session.createQuery("select avg(sage), sdept from Student group by sdept").list(); List list = session.createQuery("select count(*), sdept from Student group by sdept having count(*) > 1").list(); // 女生多于1人的系 List list = session.createQuery("select count(*), sdept from Student where ssex='F' group by sdept having count(*) > 1").list();

⑦其他常用查询

查询选修11号课程的最低分和最高分

List list = session.createQuery("select 11, max(grade), min(grade) from Studcourse where course.cid = 11").list(); for(int i=0; i<list.size(); i++) { Object objs[] = (Object[]) list.get(i); System.out.println(objs[0].toString() + " " + objs[1].toString() + " " + objs[2].toString()); }

显示各科考试不及格学生的姓名，科目和分数

List list = session.createQuery("select student.sname, course.cname, grade from Studcourse where grade < 60").list(); for(int i=0; i<list.size(); i++) { Object objs[] = (Object[]) list.get(i); System.out.println(objs[0].toString() + " " + objs[1].toString() + " " + objs[2].toString()); }

显示各个科目不及格学生数量

List list = session.createQuery("select course.cname, count(*) from Studcourse where grade < 60 group by course.cname").list(); for(int i=0; i<list.size(); i++) { Object objs[] = (Object[]) list.get(i); System.out.println(objs[0].toString() + " " + objs[1].toString()); }

⑧分页查询

setFirstResult(i) 从第几个开始取 setMaxResults(j) 最多取出多少个 按照年龄从小到大取出1~3个学生

List<Student> list = session.createQuery("from Student order by sage").setFirstResult(2).setMaxResults(3).list(); for(Student stu: list) { System.out.println(stu.getSid() + " " + stu.getSname() + " " + stu.getSage()); }

分页显示所有学生信息

private static void showResultByPage(int pageSize) { // 设置分页变量 int pageNow = 1; int pageCount = 1; int rowCount = 1; Session session = null; Transaction ts = null; try { session = HibernateUtil.getCurrentSession(); ts = session.beginTransaction(); rowCount = Integer.parseInt(session.createQuery("select count(*) from Student").uniqueResult().toString()); pageCount = (rowCount - 1)/pageSize + 1; for(int i=1; i<=pageCount; i++) { System.out.println("******第"+i+"页**********"); List<Student> list = session.createQuery("from Student").setFirstResult((i-1)*pageSize).setMaxResults(pageSize).list(); for(Student stu: list) { System.out.println(stu.getSid() + " " + stu.getSname() + " " + stu.getSage()); } } ts.commit(); } catch (Exception e) { e.printStackTrace(); if(ts!=null) { ts.rollback(); } throw new RuntimeException(e.getMessage()); } finally { if(session!=null && session.isOpen()) { session.close(); } } }

⑨参数绑定

setParameter(); 使用参数绑定： - 可读性好 - 性能提高 - 防止sql注入 参数绑定有两种形式： ？ ：这种方式使用位置绑定； :xxx：这种方式采用参数名绑定； 如果两种方式同时使用，参数名绑定只能放在位置绑定之后

List<Student> list = session.createQuery("from Student where sage>? and saddress=:address") .setString(0, "20") .setString("address", "山东").list(); for(Student stu: list) { System.out.println(stu.getSid() + " " + stu.getSname() + " " + stu.getSage()); }

⑩ 在映射文件中得到hql语句

可以从映射文件中得到hql，执行查询语句，这样可以更加灵活，在某些情况下，可以考虑使用：比如在Student.hbm.xml中配置：

<query name="myquerytest"> from Student where sage>26 </query>

使用方式：

List<Student> list = session.getNamedQuery("myquerytest").list();

⑪子查询、多表查询

⑫Criteria 使用

Session session = HibernateUtil.getCurrentSession(); Criteria cri = session.createCriteria(Student.class); cri.add(Restrictions.gt("sage", (Integer)10)); List<Student> list = cri.list(); for(Student stu: list) { System.out.println(stu.getSname() + " " + stu.getSage()+ " " + stu.getSaddress()); }

3 Hibernate工具类扩展

public final class HibernateUtil { private static SessionFactory sessionFactory = null; // 线程局部模式 private static ThreadLocal<Session> threadLoacal = new ThreadLocal<Session>(); private HibernateUtil() {} static { sessionFactory = new Configuration().configure().buildSessionFactory(); } // 获取全新的session public static Session openSession() { return sessionFactory.openSession(); } // 获取和线程关联的session public static Session getCurrentSession() { Session session = threadLoacal.get(); // 判断是否得到 if(session==null) { session = sessionFactory.openSession(); // 将session放入threadLocal threadLoacal.set(session); } return session; } /// 分页 public static List executeQueryByPage(String hql, String[] paras, int pageSize, int pageNow) { Session session = null; List list = null; try { session = openSession(); Query query = session.createQuery(hql); if(paras!=null && paras.length>0) { for(int i=0; i<paras.length; i++) { query.setString(i, paras[i]); } } /// query.setFirstResult((pageNow-1)*pageSize).setMaxResults(pageSize); list = query.list(); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e.getMessage()); } finally { if(session!=null && session.isOpen()) { session.close(); } } return list; } /// 查询接口 public static List executeQuery(String hql, String[] paras) { Session session = null; List list = null; try { session = openSession(); Query query = session.createQuery(hql); if(paras!=null && paras.length>0) { for(int i=0; i<paras.length; i++) { query.setString(i, paras[i]); } } list = query.list(); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e.getMessage()); } finally { if(session!=null && session.isOpen()) { session.close(); } } return list; } /// 修改和删除 批量sql public static void executeUpdate(String hql, String[] paras) { Session session = null; Transaction ts = null; try { session = openSession(); ts = session.beginTransaction(); Query query = session.createQuery(hql); if(paras!=null && paras.length>0) { for(int i=0; i<paras.length; i++) { query.setString(i, paras[i]); } } query.executeUpdate(); ts.commit(); } catch (Exception e) { if(ts!=null) { ts.rollback(); } e.printStackTrace(); throw new RuntimeException(e.getMessage()); } finally { if(session!=null && session.isOpen()) { session.close(); } } } // 添加 public static void save(Object obj) { Session session = null; Transaction ts = null; try { session = openSession(); ts = session.beginTransaction(); session.save(obj); ts.commit(); } catch (Exception e) { if(ts!=null) { ts.rollback(); } e.printStackTrace(); throw new RuntimeException(e.getMessage()); } finally { if(session!=null && session.isOpen()) { session.close(); } } } }