Java进阶知识14 Hibernate查询语言（HQL），本文以hibernate注解版为例讲解

1、简单概述

1.1、

1) SQL：面向的是数据库 select * from tableName;
2) HQL查询（Hibernate Query language）： hibernate 提供的面向对象的查询语言。
例：Session.createQuery(“from tableName”) //和第1)点的SQL语句恒等的，查询出来的效果是一样的
3) Criteria 查询，完全面向对象的查询（Query By Criteria ,QBC）
4) SQLQuery，本地SQL查询，逻辑比较复杂，HQL很难实现

缺点：不能跨数据库平台，如果改了数据库，sql语句有可能要改
使用场景： 对于复杂sql，hql实现不了的情况，可以使用本地sql查询

HQL查询要注意：
      1、使用hql查询的时候 auto-import="true" 要设置true，如果是false，写hql的时候，要指定类的全名，例如：
           1.1、 Query q = session.createQuery("from Employee"); //auto-import="true"的时候
            1.2、Query q = session.createQuery("from com.shore.model.Employee"); //auto-import="false"的时候

2、用注解版时，auto-import="true" （默认为true），写不写类的全名，都是可以的。用xml 配置版时，需要指定 auto-import="true" ，这样省代码，不指定为true，则像上面第1点的第1.2小点那样写。

1.2、查询全部列

1 Query query = session.createQuery("from Employee"); //OK
2 Query query = session.createQuery("from com.shore.model.Employee"); //OK
3 Query query = session.createQuery("select * from Employee"); //错误，不支持 *
4 Query query = session.createQuery("select e from Employee e"); // OK
5 System.out.println(query.list()); //把结果打印到控台上

1.3、查询指定的列

1 Query query = session.createQuery("select name,sex,salary from Employee");
2 System.out.println(query.list());

1.4、查询指定的列，自动封装为对象

1 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) from Employee");
2 System.out.println(query.list());

1.5、条件查询

1.5.1、条件查询之占位符（？）

1 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) from Employee where department_id = ?");
2 //query.setParameter(0, 1);//或者下面的setInteger(0, 1)方法也行
3 query.setInteger(0, 1);
4 System.out.println(query.list());

1.5.2、条件查询之命名参数（:）

1 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) from Employee where department_id = :deptID");
2 query.setParameter("deptID", 1);
3 System.out.println(query.list());

1.5.3、条件查询之范围查询（between .... and ... ）

1 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) from Employee where department_id between :d1 and :d2");
2 query.setParameter("d1", 1);   //也可以用占位符？实现
3 query.setParameter("d2", 2);
4 System.out.println(query.list());

1.5.4、条件查询之模糊查询 like

1 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary)  from Employee where name like ?");
2 query.setParameter(0, "%三%");
3 System.out.println(query.list());

1.6、聚合函数统计

1 Query query = session.createQuery("select count(*) from Employee where name like ?");
2 query.setParameter(0, "%三%");
3 System.out.println(query.list());

1.7、分组查询（group by）

1 //MySQL数据库写法：select department_id,count(*) from employee group by department_id;
2 Query query = session.createQuery("select department,count(*) from Employee group by department_id"); //根据部门外键
3 System.out.println(query.list());

1.8、连接查询（内连接、外连接、左/右连接等）

内连接：Join == inner join //交集
左连接：Left join，以左边的表为主表，如果没有join上，左边表数据完整，右边表的数据可能为空。//左边表的所有数据+右边表的交集部分
右连接：Right join，以右边的表为主表，如果没有join上，右边表数据完整，左边表的数据可能为空。//右边表的所有数据+左边表的交集部分
外连接：Outer join //并集

 1 1) 内连接   【映射已经配置好了关系，关联的时候，直接写对象的属性即可】
 2 Query q = session.createQuery("from Employee e inner join e.department"); //department是Employee实体中映射的一个外键（Employee表中的department_id）
 3 
 4 2) 左外连接
 5 Query q = session.createQuery("from Employee e left join e.department");
 6 
 7 3) 右外连接
 8 Query q = session.createQuery("from Department d right join d.employee"); //映射的一个外键（employee_id）
 9 
10 4) 迫切内连接    【使用fetch, 会把右表的数据，填充到左表对象中！】
11 Query q = session.createQuery("from Employee e inner join fetch e.department");
12 q.list();
13         
14 5) 迫切左外连接
15 Query q = session.createQuery("from Employee e left join fetch e.department");

1.9、HQL查询优化

1 Query query = session.getNamedQuery("getAllDept"); //定义一个参数名 getAllDept
2 query.setParameter(0, 10);

然后在对应的xml 配置文件中 写SQL语句。（对于发杂的SQL语句，可以这样做，方便项目发布后，还可以修改）

1 比如在：Employee.hbm.xml 在xml中存放sql语句 2     <query name="getAllDept">  //这里的参数名 getAllDept 要和上面的对应
3         <![CDATA[
4             from Employee d where department_id < ?;  //这里写SQL语句，不是HQL语句
5         ]]>
6     </query>

2、HQL查询语言实例

2.1、创建 Employee类、Department类、SubEmployee类、HomeTown类

 1 package com.shore.model;
 2 
 3 import javax.persistence.CascadeType;
 4 import javax.persistence.Entity;
 5 import javax.persistence.GeneratedValue;
 6 import javax.persistence.Id;
 7 import javax.persistence.ManyToOne;
 8 import org.hibernate.annotations.Type;
 9 
10 /**
11  * @author DSHORE/2019-9-25
12  * 多对一，单向关联（注解版）
13  */
14 @Entity
15 public class Employee {//员工  （“多”的一方），在多的一方加外键
16     private Integer id;
17     private String name;
18     private Boolean sex;
19     private Float salary;
20     private Department department;
21     private Integer homeId;
22 
23     @Id
24     @GeneratedValue
25     public Integer getId() {
26         return id;
27     }
28     public void setId(Integer id) {
29         this.id = id;
30     }
31     public String getName() {
32         return name;
33     }
34     public void setName(String name) {
35         this.name = name;
36     }
37 
38     @Type(type="yes_no")
39     public Boolean getSex() {
40         return sex;
41     }
42     public void setSex(Boolean sex) {
43         this.sex = sex;
44     }
45     public Float getSalary() {
46         return salary;
47     }
48     public void setSalary(Float salary) {
49         this.salary = salary;
50     }
51 
52     @ManyToOne(cascade=CascadeType.ALL)
53     public Department getDepartment() {
54         return department;
55     }
56     public void setDepartment(Department department) {
57         this.department = department;
58     }
59 
60     @Override
61     public String toString() {
62         return "Employee [id=" + id + ", name=" + name + ", sex=" + sex
63                 + ", salary=" + salary + ", department=" + department + "]";
64     }
65     public Integer getHomeId() {
66         return homeId;
67     }
68     public void setHomeId(Integer homeId) {
69         this.homeId = homeId;
70     }
71 }

Department类

 1 package com.shore.model;
 2 
 3 import javax.persistence.Entity;
 4 import javax.persistence.GeneratedValue;
 5 import javax.persistence.Id;
 6 
 7 /**
 8  * @author DSHORE/2019-9-25
 9  * 多对一，单向关联（注解版）
10  */
11 @Entity
12 public class Department {//部门 （“一”的一方）
13     private Integer id;
14     private String name;
15     private String description;
16 
17     @Id
18     @GeneratedValue
19     public Integer getId() {
20         return id;
21     }
22     public void setId(Integer id) {
23         this.id = id;
24     }
25     public String getName() {
26         return name;
27     }
28     public void setName(String name) {
29         this.name = name;
30     }
31     public String getDescription() {
32         return description;
33     }
34     public void setDescription(String description) {
35         this.description = description;
36     }
37     @Override
38     public String toString() {
39         return "Department [id=" + id + ", name=" + name + ", description="
40                 + description + "]";
41     }
42 }

SubEmployee类

 1 package com.shore.model;
 2 
 3 /**
 4  * @author DSHORE/2019-9-25
 5  *
 6  */
 7 public class SubEmployee {//查询指定的列，自动封装为对象时 用到（此处不需要创建数据库表）
 8     private String name;
 9     private Boolean sex;
10     private Float salary;
11 
12     public SubEmployee(String name, Boolean sex, Float salary) {
13         super();
14         this.name = name;
15         this.sex = sex;
16         this.salary = salary;
17     }
18 
19     public SubEmployee() {
20         super();
21     }
22 
23     public String getName() {
24         return name;
25     }
26 
27     public void setName(String name) {
28         this.name = name;
29     }
30 
31     public Boolean getSex() {
32         return sex;
33     }
34 
35     public void setSex(Boolean sex) {
36         this.sex = sex;
37     }
38 
39     public Float getSalary() {
40         return salary;
41     }
42 
43     public void setSalary(Float salary) {
44         this.salary = salary;
45     }
46 
47     @Override
48     public String toString() {
49         return "SubEmployee [name=" + name + ", sex=" + sex + ", salary="
50                 + salary + "]";
51     }
52 }

HomeTown类

 1 package com.shore.model;
 2 
 3 /**
 4  * @author DSHORE/2019-9-25
 5  *
 6  */
 7 //后来加入的，没配注解，数据库表和数据都是手动创建的
 8 public class HomeTown {//在使用“连接查询”时用到（内连接、外连接、左/右连接等）
 9     private Integer id;
10     private String address;
11 
12     public Integer getId() {
13         return id;
14     }
15     public void setId(Integer id) {
16         this.id = id;
17     }
18     public String getAddress() {
19         return address;
20     }
21     public void setAddress(String address) {
22         this.address = address;
23     }
24 }

2.2、创建 hibernate.cfg.xml 核心配置文件

 1 <?xml version='1.0' encoding='utf-8'?>
 2 <!DOCTYPE hibernate-configuration PUBLIC
 3         "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 4         "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
 5 
 6 <hibernate-configuration>
 7     <session-factory>
 8         <!-- Database connection settings -->
 9         <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
10         <property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
11         <property name="connection.username">root</property>
12         <property name="connection.password">123456</property>
13 
14         <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
15         <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
16         <property name="show_sql">true</property>
17         <property name="hbm2ddl.auto">update</property>
18 
19         <mapping class="com.shore.model.Employee" />
20         <mapping class="com.shore.model.Department" />
21     </session-factory>
22 </hibernate-configuration>

2.3、开始测试

  1 package com.shore.test;
  2 
  3 import java.util.ArrayList;
  4 import java.util.List;
  5 
  6 import org.hibernate.Query;
  7 import org.hibernate.Session;
  8 import org.hibernate.SessionFactory;
  9 import org.hibernate.Transaction;
 10 import org.hibernate.cfg.AnnotationConfiguration;
 11 import org.junit.AfterClass;
 12 import org.junit.BeforeClass;
 13 import org.junit.Test;
 14 
 15 import com.shore.model.Department;
 16 import com.shore.model.Employee;
 17 
 18 /**
 19  * @author DSHORE/2019-9-19
 20  *
 21  */
 22 public class MyTest {
 23     public static SessionFactory sessionFactory = null;
 24     public static Session session = null;
 25 
 26     @BeforeClass
 27     public static void buildSessionFactory() {
 28         sessionFactory = new AnnotationConfiguration().configure()
 29                 .buildSessionFactory();
 30     }
 31 
 32     @AfterClass
 33     public static void close() {
 34         session.close();
 35         sessionFactory.close();
 36     }
 37 
 38     /**
 39      * 单向多对一：多个员工 对 一个部门
 40      */
 41     /*@Test
 42     public void testAdd() {//插入数据
 43         session = sessionFactory.openSession();
 44         Transaction transaction = session.beginTransaction();
 45         Department dept1 = new Department();
 46         Department dept2 = new Department();
 47         dept1.setName("总裁办");
 48         dept1.setDescription("秘书");
 49         dept2.setName("市场部");
 50         dept2.setDescription("经理");
 51 
 52         Employee em1 = new Employee();
 53         em1.setName("张三");
 54         em1.setSex(true);
 55         em1.setSalary(5999.00f);
 56         em1.setDepartment(dept1);
 57 
 58         Employee em2 = new Employee();
 59         em2.setName("李四");
 60         em2.setSex(true);
 61         em2.setSalary(5999.00f);
 62         em2.setDepartment(dept2);
 63 
 64         Employee em3 = new Employee();
 65         em3.setName("王五");
 66         em3.setSex(true);
 67         em3.setSalary(5999.00f);
 68         em3.setDepartment(dept1);
 69 
 70         Employee em4 = new Employee();
 71         em4.setName("赵六");
 72         em4.setSex(true);
 73         em4.setSalary(5999.00f);
 74         em4.setDepartment(dept1);
 75 
 76         Employee em5 = new Employee();
 77         em5.setName("田七");
 78         em5.setSex(true);
 79         em5.setSalary(5999.00f);
 80         em5.setDepartment(dept2);
 81 
 82         session.save(em1); // 先创建dept1并数据，后创建em1并插入数据
 83         session.save(em2); // 先创建dept2并数据，后插入em2的数据
 84         session.save(em3);
 85         session.save(em4);
 86         session.save(em5);
 87         transaction.commit();
 88     }*/
 89 
 90     /**
 91      * hql :1、查询全部列
 92      */
 93     @SuppressWarnings("unchecked")
 94     @Test
 95     public void testListAllEmployee() {
 96         session = sessionFactory.openSession();
 97         Transaction transaction = session.beginTransaction();
 98         List<Employee> list = new ArrayList<Employee>();
 99         Query query = session.createQuery("select e from Employee e");
100         //或者这样写：Query query = session.createQuery("from Employee"); 效果是一样的
101         list = query.list();
102         transaction.commit();
103 
104         System.out.println("!!!!!!!!!!!!!!");
105         if (list != null && list.size() > 0) {
106             for (int j = 0; j < list.size(); j++) {
107                 System.out.println(j + ":" + list.get(j));
108             }
109         }
110         /** testListAllEmployee()运行结果：
111          * !!!!!!!!!!!!!!
112          * 0:Employee [id=1, name=张三, sex=true, salary=5999.0, department=Department [id=1, name=总裁办, description=秘书]]
113          * 1:Employee [id=2, name=李四, sex=true, salary=5999.0, department=Department [id=2, name=市场部, description=经理]]
114          * 2:Employee [id=3, name=王五, sex=true, salary=5999.0, department=Department [id=1, name=总裁办, description=秘书]]
115          * 3:Employee [id=4, name=赵六, sex=true, salary=5999.0, department=Department [id=1, name=总裁办, description=秘书]]
116          * 4:Employee [id=5, name=田七, sex=true, salary=5999.0, department=Department [id=2, name=市场部, description=经理]]
117          */
118     }
119 
120     /**
121      * hql: 2、查询指定的列
122      */
123     @Test
124     public void testListSubField() {
125         session = sessionFactory.openSession();
126         Transaction transaction = session.beginTransaction();
127         Query query = session.createQuery("select name,sex,salary from Employee");
128         System.out.println(query.list());
129         transaction.commit();
130         /** testListSubField()运行结果：
131          * [[Ljava.lang.Object;@259a8416, [Ljava.lang.Object;@4355d3a3, [Ljava.lang.Object;@37b994de, [Ljava.lang.Object;@78dc9766, [Ljava.lang.Object;@5a57e787]
132          */
133     }
134 
135     /**
136      * hql: 3、查询指定的列，自动封装为对象
137      * 注意：必须要提供带参数构造器；必须在hql封装类前面要加上package名称
138      */
139     @Test
140     public void testListSubFieldsToObj() {
141         session = sessionFactory.openSession();
142         Transaction transaction = session.beginTransaction();
143         Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) from Employee");
144         System.out.println(query.list());
145         transaction.commit();
146         /** testListSubFieldsToObj()运行结果：
147          * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=李四, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0], SubEmployee [name=田七, sex=true, salary=5999.0]]
148          */
149     }
150 
151     /**
152      * hql: 4 、条件查询
153      *      4.1、条件查询之占位符（？）
154      */
155     @Test
156     public void testListByConditions1() {
157         session = sessionFactory.openSession();
158         Transaction transaction = session.beginTransaction();
159         Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +
160                         "from Employee where department_id=?"); //用department_id或department都是可以的
161         //query.setParameter(0, 1);//或者下面的setInteger(0, 1)方法也行
162         query.setInteger(0, 1); //类似于JDBC中的 PreparedStatement
163         System.out.println(query.list());
164         transaction.commit();
165         /** testListByConditions1()运行结果：
166          * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0]]
167          */
168     }
169     
170     /**
171      * hql: 4.2、条件查询之命名参数（:）
172      */
173     @Test
174     public void testListByConditions2() {
175         session = sessionFactory.openSession();
176         Transaction transaction = session.beginTransaction();
177         Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +
178                         "from Employee where department_id=:deptID");
179         query.setParameter("deptID", 1);
180         System.out.println(query.list());
181         transaction.commit();
182         /** testListByConditions2()运行结果：
183          * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0]]
184          */
185     }
186     
187     /**
188      * hql: 4.3、条件查询之范围查询
189      * between 1 and 10    []闭区间  mysql    1<=x<=10
190      */
191     @Test
192     public void testListByConditions3() {
193         session = sessionFactory.openSession();
194         Transaction transaction = session.beginTransaction();
195         Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +
196                         "from Employee where department_id between :d1 and :d2");
197         query.setParameter("d1", 1);   //也可以用占位符？实现
198         query.setParameter("d2", 2);
199         System.out.println(query.list());
200         transaction.commit();
201         /** testListByConditions3()运行结果：
202          * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0]]
203          */
204     }
205     
206     /**
207      * hql: 4.4、条件查询之模糊查询  like
208      */
209     @Test
210     public void testListByConditions4() {
211         session = sessionFactory.openSession();
212         Transaction transaction = session.beginTransaction();
213         Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +
214                         "from Employee where name like ?");
215         query.setParameter(0, "%三%");
216         System.out.println(query.list());
217         transaction.commit();
218         /** testListByConditions4()运行结果：
219          * [SubEmployee [name=张三, sex=true, salary=5999.0]]
220          */
221     }
222     
223     /**
224      * hql: 5、聚合函数统计
225      */
226     @Test
227     public void testListByCount() {
228         session = sessionFactory.openSession();
229         Transaction transaction = session.beginTransaction();
230         Query query = session.createQuery("select count(*) " +
231                         "from Employee where name like ?");
232         query.setParameter(0, "%三%");
233         System.out.println(query.list());
234         transaction.commit();
235         /** testListByCount()运行结果：
236          * [1]
237          */
238     }
239     
240     /**
241      * hql: 6、分组查询group by
242      */
243     @Test
244     public void testListByDept() {
245         session = sessionFactory.openSession();
246         Transaction transaction = session.beginTransaction();
247         Query query = session.createQuery("select department,count(*) " +
248                         "from Employee group by department_id");
249         System.out.println(query.list());
250         transaction.commit();
251         /** testListByDept()运行结果：
252          * [[Ljava.lang.Object;@3b35b1f3, [Ljava.lang.Object;@4235e6e3]
253          */
254     }
255     
256     /**
257      * hql: 7、连接查询
258      *    7.1、内连接 
259      */
260     @Test
261     public void testListByInnerJoin() {
262         session = sessionFactory.openSession();
263         Transaction transaction = session.beginTransaction();
264         Query query = session.createQuery("from Employee e inner join e.department");
265         System.out.println(query.list());
266         transaction.commit();
267         /**
268          * 运行结果：
269          * [[Ljava.lang.Object;@60c9630a, [Ljava.lang.Object;@4585572a, [Ljava.lang.Object;@351daa0e, [Ljava.lang.Object;@2e879860, [Ljava.lang.Object;@4824de7d]
270          */
271     } 
272     
273     /**
274      * 7.2、左连接
275      */
276     @Test
277     public void testListByLeftJoin() {
278         session = sessionFactory.openSession();
279         Transaction transaction = session.beginTransaction();
280         Query query = session.createQuery("from Employee e left join e.department");
281         System.out.println(query.list());
282         transaction.commit();
283         /**
284          * 运行结果：
285          * [[Ljava.lang.Object;@5d15126e, [Ljava.lang.Object;@126d2380, [Ljava.lang.Object;@3b35b1f3, [Ljava.lang.Object;@4235e6e3, [Ljava.lang.Object;@60c9630a]
286          */
287     } 
288     
289     /**
290      * 7.3、右连接
291      */
292     @Test
293     public void testListByRightJoin() {
294         session = sessionFactory.openSession();
295         Transaction transaction = session.beginTransaction();
296         Query query = session.createQuery("from Employee e right join e.department");
297         System.out.println(query.list());
298         transaction.commit();
299         /**
300          * 运行结果：
301          * [[Ljava.lang.Object;@4235e6e3, [Ljava.lang.Object;@60c9630a, [Ljava.lang.Object;@4585572a, [Ljava.lang.Object;@351daa0e, [Ljava.lang.Object;@2e879860]
302          */
303     } 
304 }

数据库表的原数据图：

1 SELECT 
2     a.name,a.sex,b.address
3 FROM
4    (SELECT NAME,sex,homeId FROM employee WHERE homeId=1) a 
5 LEFT JOIN
6    (SELECT id,address FROM hometown WHERE id = 1) b
7 ON 
8    a.homeId = b.id; --MySQL语言 左连接查询