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>元素并列

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

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

• 新建项目

新建工程Hibernate09,在根目录导入hibernate(required)开发包及mysql驱动包，在src下新建hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">123456</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost/hibernate_09</property>

        <!-- <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> 
            <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property> -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

        <property name="hibernate.show_sql">true</property>

        <property name="hibernate.format_sql">true</property>

        <property name="hibernate.hbm2ddl.auto">update</property>

        <property name="hibernate.current_session_context_class">thread</property>

        <property name="hibernate.c3p0.max_size">500</property>
        <property name="hibernate.c3p0.min_size">20</property>
        <property name="hibernate.c3p0.max_statements">10</property>
        <property name="hibernate.c3p0.timeout">2000</property>
        <property name="hibernate.c3p0.idle_test_period">2000</property>
        <property name="hibernate.c3p0.acquire_increment">10</property>

        <mapping resource="com/dx/hibernate09/hql01/Department.hbm.xml" />
        <mapping resource="com/dx/hibernate09/hql01/Employee.hbm.xml" />
            
    </session-factory>
</hibernate-configuration>

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

Department.java

package com.dx.hibernate09.hql01;

import java.util.HashSet;
import java.util.Set;

public class Department {
    private Integer id;
    private String name;
    private Set<Employee> employees = new HashSet<>();

    public Department() {

    }

    public Department(String name) {
        super();
        this.name = name;
    }

    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;
    }

}

Department.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2017-6-9 23:13:49 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
    <class name="com.dx.hibernate09.hql01.Department" table="DX_DEPARTMENT">
        <id name="id" type="java.lang.Integer">
            <column name="ID" />
            <generator class="native" />
        </id>
        <property name="name" type="java.lang.String">
            <column name="NAME" />
        </property>
        
        <set name="employees" table="DX_EMPLOYEE" inverse="true" lazy="true">
            <key>
                <column name="DEPARTMENT_ID" />
            </key>
            <one-to-many class="com.dx.hibernate09.hql01.Employee" />
        </set>
    </class>
</hibernate-mapping>

Employee.java

package com.dx.hibernate09.hql01;

public class Employee {
    private Integer id;
    private String name;
    private float salary;
    private String email;
    private Department department;

    public Employee() {

    }

    public Employee(String name, float salary, String email) {
        super();
        this.name = name;
        this.salary = salary;
        this.email = email;
    }

    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 float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }

    public String getEmail() {
        return email;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public void setEmail(String email) {
        this.email = email;
    }

}

Employee.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2017-6-9 23:13:49 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
    <class name="com.dx.hibernate09.hql01.Employee" table="DX_EMPLOYEE">
        <id name="id" type="java.lang.Integer">
            <column name="ID" />
            <generator class="native" />
        </id>
        <property name="name" type="java.lang.String">
            <column name="NAME" />
        </property>
        <property name="salary" type="float">
            <column name="SALARY" />
        </property>
        <property name="email" type="java.lang.String">
            <column name="EMAIL" />
        </property>
        <many-to-one name="department" class="com.dx.hibernate09.hql01.Department">
            <column name="DEPARTMENT_ID" />
        </many-to-one>
    </class>
</hibernate-mapping>

注意：

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

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

新建测试类TestMain.java

package com.dx.hibernate09.hql01;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.model.naming.ImplicitNamingStrategyComponentPathImpl;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class TestMain {
    private SessionFactory sessionFactory = null;
    private Session session = null;
    private Transaction transaction = null;

    @Before
    public void init() {
        StandardServiceRegistry standardRegistry = new StandardServiceRegistryBuilder().configure().build();
        Metadata metadata = new MetadataSources(standardRegistry).getMetadataBuilder().applyImplicitNamingStrategy(ImplicitNamingStrategyComponentPathImpl.INSTANCE).build();

        sessionFactory = metadata.getSessionFactoryBuilder().build();
        session = sessionFactory.getCurrentSession();
        transaction = session.beginTransaction();
    }

    @Test
    public void test() {

    }

    @After
    public void destory() {
        transaction.commit();
        session.close();
        sessionFactory.close();
    }
}

• 初始化数据库

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

Hibernate: 
    
    create table DX_DEPARTMENT (
       ID integer not null auto_increment,
        NAME varchar(255),
        primary key (ID)
    ) engine=InnoDB
Hibernate: 
    
    create table DX_EMPLOYEE (
       ID integer not null auto_increment,
        NAME varchar(255),
        SALARY float,
        EMAIL varchar(255),
        DEPARTMENT_ID integer,
        primary key (ID)
    ) engine=InnoDB
Hibernate: 
    
    alter table DX_EMPLOYEE 
       add constraint FKqv35rmky1hq7k2ovpiohhpmvh 
       foreign key (DEPARTMENT_ID) 
       references DX_DEPARTMENT (ID)

查询数据

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

    @Test
    public void testInsertData() {
        Department department1 = new Department("开发部门");
        Department department2 = new Department("测试部门");
        Department department3 = new Department("业务部门");
        Department department4 = new Department("财务部门");
        Department department5 = new Department("行政部门");

        session.save(department1);
        session.save(department2);
        session.save(department3);
        session.save(department4);
        session.save(department5);

        department1.setId(1);
        department2.setId(2);
        department3.setId(3);
        department4.setId(4);
        department5.setId(5);

        for (int i = 0; i < 80; i++) {
            Employee employee = new Employee("tommy" + i, 1000 * i, "tommy" + i + "@dx.com");
            if (i % 5 == 0) {
                employee.setDepartment(department1);
            } else if (i % 5 == 1) {
                employee.setDepartment(department2);
            } else if (i % 5 == 2) {
                employee.setDepartment(department3);
            } else if (i % 5 == 3) {
                employee.setDepartment(department4);
            } else if (i % 5 == 4) {
                employee.setDepartment(department5);
            }
            session.save(employee);
        }

    }

执行结果查询

• HQL带参数查询：

1）基于位置的参数

    /**
     * 基于位置的参数
     */
    @Test
    public void testHQLPositionParameter() {
        String hql = "From Employee e Where e.salary>? and e.name like ?";

        Query query = session.createQuery(hql).setFloat(0, 6000).setString(1, "%1%");

        List<Employee> employees = query.list();
        System.out.println(employees.size());
        System.out.println(employees);
    }

2）基于命名的参数

    /**
     * 基于命名的参数
     */
    @Test
    public void testHQLNameParameter() {
        String hql = "From Employee e Where e.salary>:salary and e.name like :name";

        Query query = session.createQuery(hql).setFloat("salary", 6000).setString("name", "%1%");

        List<Employee> employees = query.list();
        System.out.println(employees.size());
        System.out.println(employees);
    }

• HQL Order By排序查询：

    /**
     * Order By
     */
    @Test
    public void testHQLOrderBy() {
        String hql = "From Employee e Where e.salary>? and e.name like ? Order By e.salary Desc";

        Query query = session.createQuery(hql).setFloat(0, 6000).setString(1, "%1%");

        List<Employee> employees = query.list();
        System.out.println(employees.size());
        System.out.println(employees);
    }

• HQL 设置实体参数查询：

    /**
     * 基于实体的查询
     */
    @Test
    public void testHQLEntity() {
        String hql = "From Employee e Where e.salary>? and e.name like ? and e.department=? Order By e.salary Desc";

        Department depart = new Department();
        depart.setId(1);

        @SuppressWarnings("deprecation")
        Query query = session.createQuery(hql).setFloat(0, 6000).setString(1, "%1%").setEntity(2, depart);

        List<Employee> employees = query.list();
        System.out.println(employees.size());
        System.out.println(employees);
    }