Hibernate（十二）：HQL查询（一）

  • 概述

　　Hibernate提供了以下几种检索对象的方式

1）导航对象图检索方式：根据已经加载的对象导航到其他对象；

2）OID检索方式：按照对象的OID来检索对象；

3）HQL检索方式：使用面向对象的HQL查询语言；

4）QBC检索方式：使用QBC（Query By Criteria）API来检索对象。这种API封装了基于字符串形式的查询语句，提供了更加面向对象的查询接口。

5）本地SQL检索的方式：使用本地数据的SQL查询语句。

　　HQL(Hibernate Query Language)面向对象的查询语言，它和SQL查询语言有些相似。在Hibernate提供的各种检索方式中，HQL是使用最广泛的一种检索方式。它有如下功能：

1）在查询语句中设定各种查询条件

2）支持投影查询，即仅检索出对象的部分属性

3）支持分页查询

4）支持连接查询

5）支持分组查询，允许使用“HAVING”和“GROUP BY”关键字

6）提供内置聚集函数，如：SUM()、MIN()、Max()

7）支持子查询

8）支持动态绑定参数

9）能够调用用户定义的SQL函数或标准的SQL函数。

　　HQL检索方式包括以下步骤

1）通过Session的createQuery()方法创建一个Query对象，它包括一个HQL查询语句。HQL查询语句中可以包括命名参数

2）动态绑定参数

3）调用Query相关方法执行查询语句

　　Query接口支持方法链编程风格

它的setXxx()方法返回自身实例，而不是void类型。

　　HQL vs SQL

1）HQL查询语句是面向对象的，Hibernate负责解析HQL查询语句，然后根据对象-关系映射文件中的映射信息，把HQL查询语句翻译成相应的SQL语句。HQL查询语句中的主题是域模型中的类与类的属性

2）SQL查询语句是与关系数据库绑定在一起的。SQL查询语句中的主体是数据库表及表的字段。

　　绑定参数

1）Hibernate的参数绑定机制依赖于JDBC API中的PreparedStatement的预定义SQL语句功能。

2）HQL的参数绑定两种形式：

　　按参数名称绑定：在HQL查询语句定义命名参数，命名参数以“.”开头

　　按参数位置绑定：在HQL查询语句中用“?”来定定义参数位置

3）相关参数

　　setEntity()：把参数与一个持久类绑定

　　setParamenter():绑定任意类型的参数，该方式的第三个参数显式指定Hibernate映射类性。

　　HQL采用ORDER BY关键字对查询结果排序

在映射文件中定义命名查询语句

1）Hibernate允许在映射文件中定义字符串形式的查询语句

2）<query>元素用于定义一个HQL查询语句，它和<class>元素并列

1 <query name="findNewsByTitle">
2     <![CDATA[
3         FROM News n WHERE n.title LIKE :title
4     ]]>
5 </query>

3）在程序中通过Session的getNameQuery()方法获取查询语句对应的Query对象。

  • 新建项目

新建工程Hibernate09,在根目录导入hibernate(required)开发包及mysql驱动包，在src下新建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 <hibernate-configuration>
 6     <session-factory>
 7         <property name="hibernate.connection.username">root</property>
 8         <property name="hibernate.connection.password">123456</property>
 9         <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
10         <property name="hibernate.connection.url">jdbc:mysql://localhost/hibernate_09</property>
11 
12         <!-- <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> 
13             <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property> -->
14         <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
15 
16         <property name="hibernate.show_sql">true</property>
17 
18         <property name="hibernate.format_sql">true</property>
19 
20         <property name="hibernate.hbm2ddl.auto">update</property>
21 
22         <property name="hibernate.current_session_context_class">thread</property>
23 
24         <property name="hibernate.c3p0.max_size">500</property>
25         <property name="hibernate.c3p0.min_size">20</property>
26         <property name="hibernate.c3p0.max_statements">10</property>
27         <property name="hibernate.c3p0.timeout">2000</property>
28         <property name="hibernate.c3p0.idle_test_period">2000</property>
29         <property name="hibernate.c3p0.acquire_increment">10</property>
30 
31         <mapping resource="com/dx/hibernate09/hql01/Department.hbm.xml" />
32         <mapping resource="com/dx/hibernate09/hql01/Employee.hbm.xml" />
33             
34     </session-factory>
35 </hibernate-configuration>
View Code

在src下新建包com.dx.hibernate09.hql01，在该包下新建：

Department.java

 1 package com.dx.hibernate09.hql01;
 2 
 3 import java.util.HashSet;
 4 import java.util.Set;
 5 
 6 public class Department {
 7     private Integer id;
 8     private String name;
 9     private Set<Employee> employees = new HashSet<>();
10 
11     public Department() {
12 
13     }
14 
15     public Department(String name) {
16         super();
17         this.name = name;
18     }
19 
20     public Integer getId() {
21         return id;
22     }
23 
24     public void setId(Integer id) {
25         this.id = id;
26     }
27 
28     public String getName() {
29         return name;
30     }
31 
32     public void setName(String name) {
33         this.name = name;
34     }
35 
36     public Set<Employee> getEmployees() {
37         return employees;
38     }
39 
40     public void setEmployees(Set<Employee> employees) {
41         this.employees = employees;
42     }
43 
44 }
View Code

Department.hbm.xml

 1 <?xml version="1.0"?>
 2 <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
 3 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 4 <!-- Generated 2017-6-9 23:13:49 by Hibernate Tools 3.5.0.Final -->
 5 <hibernate-mapping>
 6     <class name="com.dx.hibernate09.hql01.Department" table="DX_DEPARTMENT">
 7         <id name="id" type="java.lang.Integer">
 8             <column name="ID" />
 9             <generator class="native" />
10         </id>
11         <property name="name" type="java.lang.String">
12             <column name="NAME" />
13         </property>
14         
15         <set name="employees" table="DX_EMPLOYEE" inverse="true" lazy="true">
16             <key>
17                 <column name="DEPARTMENT_ID" />
18             </key>
19             <one-to-many class="com.dx.hibernate09.hql01.Employee" />
20         </set>
21     </class>
22 </hibernate-mapping>
View Code

Employee.java

 1 package com.dx.hibernate09.hql01;
 2 
 3 public class Employee {
 4     private Integer id;
 5     private String name;
 6     private float salary;
 7     private String email;
 8     private Department department;
 9 
10     public Employee() {
11 
12     }
13 
14     public Employee(String name, float salary, String email) {
15         super();
16         this.name = name;
17         this.salary = salary;
18         this.email = email;
19     }
20 
21     public Integer getId() {
22         return id;
23     }
24 
25     public void setId(Integer id) {
26         this.id = id;
27     }
28 
29     public String getName() {
30         return name;
31     }
32 
33     public void setName(String name) {
34         this.name = name;
35     }
36 
37     public float getSalary() {
38         return salary;
39     }
40 
41     public void setSalary(float salary) {
42         this.salary = salary;
43     }
44 
45     public String getEmail() {
46         return email;
47     }
48 
49     public Department getDepartment() {
50         return department;
51     }
52 
53     public void setDepartment(Department department) {
54         this.department = department;
55     }
56 
57     public void setEmail(String email) {
58         this.email = email;
59     }
60 
61 }
View Code

Employee.hbm.xml

 1 <?xml version="1.0"?>
 2 <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
 3 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 4 <!-- Generated 2017-6-9 23:13:49 by Hibernate Tools 3.5.0.Final -->
 5 <hibernate-mapping>
 6     <class name="com.dx.hibernate09.hql01.Employee" table="DX_EMPLOYEE">
 7         <id name="id" type="java.lang.Integer">
 8             <column name="ID" />
 9             <generator class="native" />
10         </id>
11         <property name="name" type="java.lang.String">
12             <column name="NAME" />
13         </property>
14         <property name="salary" type="float">
15             <column name="SALARY" />
16         </property>
17         <property name="email" type="java.lang.String">
18             <column name="EMAIL" />
19         </property>
20         <many-to-one name="department" class="com.dx.hibernate09.hql01.Department">
21             <column name="DEPARTMENT_ID" />
22         </many-to-one>
23     </class>
24 </hibernate-mapping>
View Code

注意：

1）上边新建的类Employee与Department是多对一关系；

2）hbm.xml配置的是双向一对多映射关系。

新建测试类TestMain.java

 1 package com.dx.hibernate09.hql01;
 2 
 3 import org.hibernate.Session;
 4 import org.hibernate.SessionFactory;
 5 import org.hibernate.Transaction;
 6 import org.hibernate.boot.Metadata;
 7 import org.hibernate.boot.MetadataSources;
 8 import org.hibernate.boot.model.naming.ImplicitNamingStrategyComponentPathImpl;
 9 import org.hibernate.boot.registry.StandardServiceRegistry;
10 import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
11 import org.junit.After;
12 import org.junit.Before;
13 import org.junit.Test;
14 
15 public class TestMain {
16     private SessionFactory sessionFactory = null;
17     private Session session = null;
18     private Transaction transaction = null;
19 
20     @Before
21     public void init() {
22         StandardServiceRegistry standardRegistry = new StandardServiceRegistryBuilder().configure().build();
23         Metadata metadata = new MetadataSources(standardRegistry).getMetadataBuilder().applyImplicitNamingStrategy(ImplicitNamingStrategyComponentPathImpl.INSTANCE).build();
24 
25         sessionFactory = metadata.getSessionFactoryBuilder().build();
26         session = sessionFactory.getCurrentSession();
27         transaction = session.beginTransaction();
28     }
29 
30     @Test
31     public void test() {
32 
33     }
34 
35     @After
36     public void destory() {
37         transaction.commit();
38         session.close();
39         sessionFactory.close();
40     }
41 }
View Code
  • 初始化数据库

执行空的测试函数test()，生成数据表，后台执行的sql:

 1 Hibernate: 
 2     
 3     create table DX_DEPARTMENT (
 4        ID integer not null auto_increment,
 5         NAME varchar(255),
 6         primary key (ID)
 7     ) engine=InnoDB
 8 Hibernate: 
 9     
10     create table DX_EMPLOYEE (
11        ID integer not null auto_increment,
12         NAME varchar(255),
13         SALARY float,
14         EMAIL varchar(255),
15         DEPARTMENT_ID integer,
16         primary key (ID)
17     ) engine=InnoDB
18 Hibernate: 
19     
20     alter table DX_EMPLOYEE 
21        add constraint FKqv35rmky1hq7k2ovpiohhpmvh 
22        foreign key (DEPARTMENT_ID) 
23        references DX_DEPARTMENT (ID)
View Code

查询数据

添加testInsertData()测试函数，给数据表填充数据：

 1     @Test
 2     public void testInsertData() {
 3         Department department1 = new Department("开发部门");
 4         Department department2 = new Department("测试部门");
 5         Department department3 = new Department("业务部门");
 6         Department department4 = new Department("财务部门");
 7         Department department5 = new Department("行政部门");
 8 
 9         session.save(department1);
10         session.save(department2);
11         session.save(department3);
12         session.save(department4);
13         session.save(department5);
14 
15         department1.setId(1);
16         department2.setId(2);
17         department3.setId(3);
18         department4.setId(4);
19         department5.setId(5);
20 
21         for (int i = 0; i < 80; i++) {
22             Employee employee = new Employee("tommy" + i, 1000 * i, "tommy" + i + "@dx.com");
23             if (i % 5 == 0) {
24                 employee.setDepartment(department1);
25             } else if (i % 5 == 1) {
26                 employee.setDepartment(department2);
27             } else if (i % 5 == 2) {
28                 employee.setDepartment(department3);
29             } else if (i % 5 == 3) {
30                 employee.setDepartment(department4);
31             } else if (i % 5 == 4) {
32                 employee.setDepartment(department5);
33             }
34             session.save(employee);
35         }
36 
37     }
View Code

执行结果查询

  • HQL带参数查询：

1）基于位置的参数

 1     /**
 2      * 基于位置的参数
 3      */
 4     @Test
 5     public void testHQLPositionParameter() {
 6         String hql = "From Employee e Where e.salary>? and e.name like ?";
 7 
 8         Query query = session.createQuery(hql).setFloat(0, 6000).setString(1, "%1%");
 9 
10         List<Employee> employees = query.list();
11         System.out.println(employees.size());
12         System.out.println(employees);
13     }

2）基于命名的参数

 1     /**
 2      * 基于命名的参数
 3      */
 4     @Test
 5     public void testHQLNameParameter() {
 6         String hql = "From Employee e Where e.salary>:salary and e.name like :name";
 7 
 8         Query query = session.createQuery(hql).setFloat("salary", 6000).setString("name", "%1%");
 9 
10         List<Employee> employees = query.list();
11         System.out.println(employees.size());
12         System.out.println(employees);
13     }
  • HQL Order By排序查询：
 1     /**
 2      * Order By
 3      */
 4     @Test
 5     public void testHQLOrderBy() {
 6         String hql = "From Employee e Where e.salary>? and e.name like ? Order By e.salary Desc";
 7 
 8         Query query = session.createQuery(hql).setFloat(0, 6000).setString(1, "%1%");
 9 
10         List<Employee> employees = query.list();
11         System.out.println(employees.size());
12         System.out.println(employees);
13     }
  • HQL 设置实体参数查询：
 1     /**
 2      * 基于实体的查询
 3      */
 4     @Test
 5     public void testHQLEntity() {
 6         String hql = "From Employee e Where e.salary>? and e.name like ? and e.department=? Order By e.salary Desc";
 7 
 8         Department depart = new Department();
 9         depart.setId(1);
10 
11         @SuppressWarnings("deprecation")
12         Query query = session.createQuery(hql).setFloat(0, 6000).setString(1, "%1%").setEntity(2, depart);
13 
14         List<Employee> employees = query.list();
15         System.out.println(employees.size());
16         System.out.println(employees);
17     }
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yy3b2007com/p/6965055.html