Hibernate_12_HQL语句查询

使用HQL查询的特点：

（1）与SQL相似，SQL中的语法基本上都可以直接使用。

（2）SQL查询的是表和表中的列；HQL查询的是对象与对象中的属

（3）HQL的关键字不区分大小写，类名与属性名是区分大小写

（4）SELECT可以省略.

Employee类、Department类、SessionFactoryTools类、Employee.hbm.xml文件、 Department.hbm.xml文件、Hibernate.cfg.xml文件都与4中的相同。

1>简单的数据库的查询：

持久化层代码：

/** * 员工类和部门类的持久层类 */public class EmpAndDepDao {/** * save的方法 */@Testpublic void save() {Session session = SessionFactoryTools.getSession();Transaction tx = null;// 声明一个事务try {tx = session.beginTransaction();// 开始一个事务// ============================================// 新建部门对象，设置并设置部门名称for (int i = 0; i < 10; i++) {Department department = new Department();department.setName("开发部" + i);session.save(department);}// 新建员工对象，并设置姓名for (int i = 0; i < 20; i++) {Employee employee = new Employee();employee.setName("赵钱孙" + i);session.save(employee);}// ===============================================tx.commit();} catch (RuntimeException e) {if (tx != null) {tx.rollback();}throw e;} finally {session.close();}}/** * getById */@Testpublic void getById() {Session session = SessionFactoryTools.getSession();Transaction tx = null;try {tx = session.beginTransaction();String hql = null;// ================================================================// -------获取部门信息-------------------------------------/** 1，简单的查询 hql = "FROM Employee"; hql = "FROM Employee AS e"; // 使用别名 hql = "FROM Employee e"; // 使用别名，as关键字可省略*//** 2，带上过滤条件的（可以使用别名）：Where hql = "FROM Employee WHERE id<10"; hql = "FROM Employee e WHERE e.id<10"; hql = "FROM Employee e WHERE e.id<10 AND e.id>5";*//**3，带上排序条件的：Order By hql = "FROM Employee e WHERE e.id<10 ORDER BY e.name"; hql = "FROM Employee e WHERE e.id<10 ORDER BY e.name DESC";       <span style="white-space:pre"></span> hql = "FROM Employee e WHERE e.id<10 ORDER BY e.name DESC, id ASC";*//**4，指定select子句（不可以使用select *） hql = "SELECT e FROM Employee e"; // 相当于"FROM Employee e"   // 只查询一个列，返回的集合的元素类型就是这个属性的类型<span style="white-space:pre"></span> hql = "SELECT e.name FROM Employee e"; <span style="white-space:pre"></span>// 查询多个列，返回的集合的元素类型是Object数组 <span style="white-space:pre"></span>hql = "SELECT e.id,e.name FROM Employee e"; <span style="white-space:pre"></span>// 可以使用new语法，指定把查询出的部分属性封装到对象中<span style="white-space:pre"></span>hql = "SELECT new Employee(e.id,e.name) FROM Employee e"; *//** 5，执行查询，获得结果（list、uniqueResult、分页 ）Query query = session.createQuery("FROM Employee e WHERE id<3"); query.setFirstResult(0); query.setMaxResults(10);// List list = query.list(); // 查询的结果是一个List集合<span style="white-space:pre"></span>// 查询的结果是唯一的一个结果，当结果有多个，就会抛异常Employee employee = (Employee) query.uniqueResult();<span style="white-space:pre"></span>System.out.println(employee);*//** 6，方法链  List list = session.createQuery(//"FROM Employee")//.setFirstResult(0)//.setMaxResults(10)//.list();*/// -----赋值语句--------------------------------<span style="white-space:pre"></span>hql = "SELECT new Employee (e.id,e.name) FROM Employee e";//-------执行查询-------------------------------List<?> list= session.createQuery(hql).list();// ----- 显示结果-----------------------------------/** *  判断List为什么类型，<span style="white-space:pre"></span> *  若为数组类型则采用数组输出方式，<span style="white-space:pre"></span> *  否则采用对象输出方式*/for (Object obj : list) {if (obj.getClass().isArray()) {   System.out.println(Arrays.toString((Object[]) obj));} else {System.out.println(obj);}}   // =====================================================tx.commit();} catch (RuntimeException e) {tx.rollback();throw e;} finally {session.close();}}}

2>带有限定条件和关联的数据库查询：

查询方法：

/** * getById */@Testpublic void getById() {Session session = SessionFactoryTools.getSession();Transaction tx = null;try {tx = session.beginTransaction();String hql = null;// ==================================================// -------获取部门信息-------------------------------/** 1，聚集函数：count(), max(), min(), avg(), sum() <span style="white-space:pre"></span> // 返回的结果是Long型的 hql = "SELECT COUNT(*) FROM Employee"; <span style="white-space:pre"></span> // 返回的结果是id属性的类型 <span style="white-space:pre"></span> hql = "SELECT min(id) FROM Employee";     <span style="white-space:pre"></span> Number result = (Number) session.createQuery(hql).uniqueResult(); System.out.println(result.getClass()); System.out.println(result);*//** 2，分组: Group By ... Having<span style="white-space:pre"></span> hql = "SELECT e.name,COUNT(e.id) FROM Employee e GROUP BY e.name";<span style="white-space:pre"></span> hql = "SELECT e.name,COUNT(e.id) FROM Employee e GROUP BY e.name HAVING count(e.id)>1";<span style="white-space:pre"></span> hql = "SELECT e.name,COUNT(e.id) FROM Employee e WHERE id<9 GROUP BY e.name HAVING count(e.id)>1";<span style="white-space:pre"></span> //------链式查询语言------------------------- hql = "SELECT e.name,COUNT(e.id) " + // "FROM Employee e " + // "WHERE id<9 " + // "GROUP BY e.name " + // "HAVING count(e.id)>1 " + // "ORDER BY count(e.id) ASC"; --- hql = "SELECT e.name,COUNT(e.id) AS c " + // "FROM Employee e " + // "WHERE id<9 " + // "GROUP BY e.name " + //<span style="white-space:pre"></span> "HAVING count(e.id)>1 " + // 在having子句中不能使用列别名 "ORDER BY c ASC"; // 在orderby子句中可以使用列别名*//** 3，连接查询 / HQL是面向对象的查询 //内连接（inner关键字可以省略） hql = "SELECT e.id,e.name,d.name FROM Employee e INNER JOIN e.department d"; hql = "SELECT e.id,e.name,d.name FROM Employee e JOIN e.department d"; // 左外连接（outer关键字可以省略） hql = "SELECT e.id,e.name,d.name FROM Employee e LEFT OUTER JOIN e.department d"; hql = "SELECT e.id,e.name,d.name FROM Employee e LEFT JOIN e.department d"; // 右外连接（outer关键字可以省略） hql = "SELECT e.id,e.name,d.name FROM Employee e RIGHT OUTER JOIN e.department d";  hql = "SELECT e.id,e.name,d.name FROM Employee e RIGHT JOIN e.department d"; // 可以使用更方便的方法 hql = "SELECT e.id,e.name,e.department.name FROM Employee e";*//** 4，查询时使用参数 //方式一：使用'?'占位 hql = "FROM Employee e WHERE id BETWEEN ? AND ?"; List list = session.createQuery(hql)// .setParameter(0, 5)// 设置参数，0表示为第一个参数 .setParameter(1, 15)//1表示为第二个参数 .list();//方式二：使用变量名 hql = "FROM Employee e WHERE id BETWEEN :idMin AND :idMax"; List list = session.createQuery(hql)// .setParameter("idMax", 15)//idMax表示：上边缘 .setParameter("idMin", 5)//idMin表示：下边缘 .list();// 当参数是集合时，一定要使用setParameterList()设置参数值 hql = "FROM Employee e WHERE id IN (:ids)";List list = session.createQuery(hql)//ids表示：变量名<span style="white-space:pre"></span>.setParameterList("ids", new Object[] { 1, 2, 3, 5, 8, 100 }) <span style="white-space:pre"></span>.list();//数组中的数值为变量名可以取得数值*//** 5，使用命名查询 *  //需要在hbm文件中配置查询语句     //queryByIdRange：hbm文件中配置的查询语句的名称 Query query = session.getNamedQuery("queryByIdRange"); query.setParameter("idMin", 3);<span style="white-space:pre"></span> //idMin、idMax：查询语句中的变量名 query.setParameter("idMax", 10); List list = query.list();*/   /**6，update与delete，不会通知Session缓存//Update int result = session.createQuery(// "UPDATE Employee e SET e.name=? WHERE id>15")// .setParameter(0, "无名氏")// .executeUpdate(); // 返回int型的结果，表示影响了多少行。 System.out.println("result = " + result);//Deleteint result = session.createQuery(//"DELETE FROM Employee e WHERE id>15")//.executeUpdate(); // 返回int型的结果，表示影响了多少行。System.out.println("result = " + result);  */  // ----- 执行查询并显示结果----------------------------  List list = session.createQuery(hql).list();  for (Object obj : list) { if (obj.getClass().isArray()) { System.out.println(Arrays.toString((Object[]) obj)); } else { System.out.println(obj); } }// =====================================================tx.commit();} catch (RuntimeException e) {tx.rollback();throw e;} finally {session.close();}}

 注意：在update或delete后，需要refresh(obj)一下以获取最新的状态

// 第一次显示名称   Employee employee = (Employee) session.get(Employee.class, 1);System.out.println(employee.getName());// update与delete，不会通知Session缓存int result = session.createQuery(//"UPDATE Employee e SET e.name=? WHERE id=1")//.setParameter(0, "无名氏2")//.executeUpdate(); System.out.println(result);// 第二次显示名称session.refresh(employee);System.out.println(employee.getName());