Hibernate(十一)HQL查询

HQL：Hibernate Query Language

HQL有几个特点：

与SQL相似，SQL中的语法基本上都可以直接使用;SQL查询的是表和表中的列；HQL查询的是对象与对象中的属性;HQL的关键字不区分大小写，类名与属性名区分大小写;select可以省略。package test.hibernate.hbmHQL;import java.util.HashSet;import java.util.Set;public class Department {private Integer id;private String name;private Set<Employee> employees = new HashSet<Employee>();public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Set<Employee> getEmployees() {return employees;}public void setEmployees(Set<Employee> employees) {this.employees = employees;}@Overridepublic String toString() {// TODO Auto-generated method stubreturn "[employee:id=" + id + ",name=" + name + "]";}}<?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC"-//Hibernate/Hibernate Mapping DTD 3.0//EN"""><hibernate-mapping package="test.hibernate.hbmHQL"><class name="Employee" table="employee_hql"><id name="id" type="integer" column="id"><generator class="native" /></id><property name="name" /><many-to-one name="department" class="Department" column="departmentId"></many-to-one></class><!– <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN ? AND ? </query> –><!– <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN :idMin AND :idMax </query> –><query name="queryByIdRange"><!–这里<小于号会被看成特殊字符而报错，用<![CDATA[…]]>包起来表示没有特殊字符 –><![CDATA[FROM Employee e WHERE e.id >= :idMin AND e.id <= :idMax]]></query></hibernate-mapping><?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC"-//Hibernate/Hibernate Mapping DTD 3.0//EN"""><hibernate-mapping package="test.hibernate.hbmHQL"><class name="Department" table="department_hql"><id name="id" type="integer" column="id"><generator class="native" /></id><property name="name" /><set name="employees" cascade="all"><key column="departmentId"></key><one-to-many class="Employee" /></set></class></hibernate-mapping><?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC"-//Hibernate/Hibernate Mapping DTD 3.0//EN"""><hibernate-mapping package="test.hibernate.hbmHQL"><class name="Employee" table="employee_hql"><id name="id" type="integer" column="id"><generator class="native" /></id><property name="name" /><many-to-one name="department" class="Department" column="departmentId"></many-to-one></class><!– <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN ? AND ? </query> –><!– <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN :idMin AND :idMax </query> –><query name="queryByIdRange"><!–这里<小于号会被看成特殊字符而报错，用<![CDATA[…]]>包起来表示没有特殊字符 –><![CDATA[FROM Employee e WHERE e.id >= :idMin AND e.id <= :idMax]]></query></hibernate-mapping>package test.hibernate.hbmHQL;import java.util.Arrays;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;import org.junit.Test;public class App {private static SessionFactory sessionFactory = new Configuration()//.configure()//.addClass(Department.class)// 添加Hibernate实体类（加载对应的映射文件）.addClass(Employee.class)//.buildSessionFactory();@Testpublic void testSave() throws Exception {Session session = sessionFactory.openSession();session.beginTransaction();// ——————————————–// 构建对象for (int i = 1; i <= 10; i++) {Department department = new Department();department.setName("部门_" + i);session.save(department);}for (int i = 1; i <= 20; i++) {Employee employee = new Employee();employee.setName("员工_" + i);employee.setDepartment(null);session.save(employee);}// ——————————————–session.getTransaction().commit();session.close();}// 获取@Testpublic void testHQL() throws Exception {Session session = sessionFactory.openSession();session.beginTransaction();// ===========================================// 获取数据String hql = null;// hql = "FROM Employee";//HQL中查询select可以省略，但不能用select *// hql = "FROM Employee AS e";// hql = "FROM Employee e";//使用别名，as关键字可以省略// hql = "FROM Employee e WHERE e.id < 30 AND e.id > 25";// hql = "FROM Employee e WHERE e.id < 30 ORDER BY e.id DESC";// 有多个排序条件，先按前面的排// hql =// "FROM Employee e WHERE e.id < 30 ORDER BY e.id DESC,e.name ASC";// hql = "select e.name FROM Employee e";// hql = "select e FROM Employee e";//相当与"FROM Employee e"// hql = "select e.id,e.name FROM Employee e";//返回一个Object数组// hql = "select new Employee(e.id,e.name) FROM Employee e";// // 返回对象数组,要求类中有id、name两个参数的构造函数// // List list = session.createQuery(hql).list();Query query = session.createQuery("FROM Employee WHERE id=200");// // 分页query.setFirstResult(0);// 从第一条记录开始查询，与具体的id无关query.setMaxResults(5);Employee employee = (Employee) query.uniqueResult();System.out.println(employee);// 如果下标越界，查询的结果返回null// List list = query.list();// 方法链的形式// List list = session.createQuery(//// "select e.id,e.name FROM Employee e")//// .setFirstResult(0)//// .setMaxResults(10)//// .list();//// for (Object obj : list) {// if (obj.getClass().isArray()) {// System.out.println(Arrays.toString((Object[]) obj));// } else {// System.out.println(obj);// }//// }// ==========================================session.getTransaction().commit();session.close();}@Testpublic void testHQL2() throws Exception {Session session = sessionFactory.openSession();session.beginTransaction();// ===========================================String hql = null;// 聚集函数：count()、max()、min()、avg()、sum()// hql="SELECT COUNT(*) FROM Employee";// Long result=(Long)session.createQuery(hql).uniqueResult();// System.out.println(result);// hql = "SELECT MAX(id) FROM Employee";// Number result = (Number) session.createQuery(hql).uniqueResult();// System.out.println(result.getClass());// 返回结果类型// System.out.println(result.intValue());// System.out.println(result.longValue());// 分组group by、order by、having// hql =// "SELECT e.name,COUNT(e.id) FROM Employee e WHERE id <= 30 GROUP BY name";// 过滤，只显示数量大于2的组(不能用where代替，因为分组在查询之后)// hql="SELECT e.name,COUNT(e.id) FROM Employee e WHERE id <= 30 GROUP BY name HAVING COUNT(e.id) > 2";// hql = "SELECT e.name,COUNT(e.id) AS c " + //// "FROM Employee e " + //// "WHERE id <= 30 " + //// "GROUP BY e.name " + //// "HAVING COUNT(e.id) = 2 " + // having子句不能使用列别名// "ORDER BY c ASC";// order by 子句可以使用列别名// 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);// }// }// hql =// "SELECT e.id,e.name,d.name FROM Employee e JOIN e.department d";// hql =// "SELECT e.id,e.name,d.name FROM Employee e INNER 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 RIGHT JOIN e.department d";// hql = "SELECT e.id,e.name,e.department.name FROM Employee e";// 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);// }// }// 使用参数查询// 方式一：使用?占位// hql = "FROM Employee WHERE id=?";// List list = session.createQuery(hql)//// .setParameter(0, 23)//// .list();// hql = "FROM Employee WHERE id BETWEEN ? AND ?";// List list = session.createQuery(hql)//// .setParameter(0, 22)// 设置参数，，第一个参数的索引为0// .setParameter(1, 30)//// .list();// for (Object obj : list) {// if (obj.getClass().isArray()) {// System.out.println(Arrays.toString((Object[]) obj));// } else {// System.out.println(obj);// }// }// 方式二：使用变量名// hql = "FROM Employee e WHERE id BETWEEN :idMin AND :idMax";// List list = session.createQuery(hql)//// .setParameter("idMin", 22)//// .setParameter("idMax", 30)//// .list();// for (Object obj : list) {// if (obj.getClass().isArray()) {// System.out.println(Arrays.toString((Object[]) obj));// } else {// System.out.println(obj);// }// }// 当变量是集合时，可以使用IN，参数设置用ParameterList// hql = "FROM Employee WHERE id IN (:ids)";// List list = session.createQuery(hql)//// .setParameterList("ids", new Object[] { 23, 26, 27, 28, 30 })//// .list();// 使用命名查询// Query query = session.getNamedQuery("queryByIdRange");// query.setParameter(0, 25);// query.setParameter(1, 30);// List list = query.list();// Query query = session.getNamedQuery("queryByIdRange");// query.setParameter("idMin", 25);// query.setParameter("idMax", 30);// List list = query.list();/* * update与delete,不会通知Session缓存，如要获取数据库 * 更新后的数据可以用refresh */// int result = session.createQuery(//// "UPDATE Employee e SET e.name=? WHERE id < 30")// .setParameter(0, "张小姐")//// .executeUpdate();int result = session.createQuery(//"DELETE Employee e WHERE id < ?")// delete子句里from可以省掉.setParameter(0, 30)//.executeUpdate();System.out.println("result=" + result);// for (Object obj : list) {// if (obj.getClass().isArray()) {// System.out.println(Arrays.toString((Object[]) obj));// } else {// System.out.println(obj);// }// }// ===========================================session.getTransaction().commit();session.close();}}

部分截图

内连接

左外连接

右外连接

顺境的美德是节制，逆境的美德是坚韧，这后一种是较为伟大的德性。

相关文章：

你感兴趣的文章：

标签云：