HQL - Hibernate 查询语言 - 示例教程
HQL 或 Hibernate 查询语言是 Hibernate 框架的面向对象查询语言。HQL 与 SQL 非常相似，只是我们使用对象而不是表名，这使得它更接近面向对象编程。
Hibernate 查询语言 - HQL
HQL 和大小写敏感度：HQL 不区分大小写，但 Java 类和变量名除外。因此SeLeCT和 相同 和sELEct相同SELECT，但com.journaldev.model.Employee和 不同com.journaldev.model.EMPLOYEE。HQL 中支持的一些常用子句包括：
- HQL From : HQL From 与 SQL 中的 select 子句相同，
from Employee与 相同select * from Employee。我们也可以创建别名，例如from Employee emp或from Employee as emp。 - HQL 连接：HQL 支持内连接、左外连接、右外连接和全连接。例如。
select e.name, a.city from Employee e INNER JOIN e.address a在此查询中，Employee 类应该有一个名为 address 的变量。我们将在示例代码中研究它。 - 聚合函数：HQL 支持常用的聚合函数，例如 count(*)、count(distinct x)、min()、max()、avg() 和 sum()。
- 表达式：HQL 支持算术表达式（+、-、*、/）、二进制比较运算符（=、>=、<=、<>、!=、like）、逻辑运算（and、or、not）等。
- HQL 还支持 ordre by 和 group by 子句。
- HQL 也像 SQL 查询一样支持子查询。
- HQL 也支持 DDL、DML 和执行存储过程。
我们来看一个在程序中使用 HQL 的简单示例。
HQL 示例数据库设置
我使用 MySQL 数据库作为示例，以下脚本将创建两个表 Employee 和 Address。它们具有一对一映射，我正在为示例插入一些演示数据。
CREATE TABLE `Employee` (
`emp_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`emp_name` varchar(20) NOT NULL,
`emp_salary` double(10,0) NOT NULL DEFAULT '0',
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `Address` (
`emp_id` int(11) unsigned NOT NULL,
`address_line1` varchar(50) NOT NULL DEFAULT '',
`zipcode` varchar(10) DEFAULT NULL,
`city` varchar(20) DEFAULT NULL,
PRIMARY KEY (`emp_id`),
CONSTRAINT `emp_fk_1` FOREIGN KEY (`emp_id`) REFERENCES `Employee` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Employee` (`emp_id`, `emp_name`, `emp_salary`)
VALUES
(1, 'Pankaj', 100),
(2, 'David', 200),
(3, 'Lisa', 300),
(4, 'Jack', 400);
INSERT INTO `Address` (`emp_id`, `address_line1`, `zipcode`, `city`)
VALUES
(1, 'Albany Dr', '95129', 'San Jose'),
(2, 'Arques Ave', '95051', 'Santa Clara'),
(3, 'BTM 1st Stage', '560100', 'Bangalore'),
(4, 'City Centre', '100100', 'New Delhi');
commit;
在 Eclipse 或您正在使用的 IDE 中创建一个 maven 项目，我们的最终项目将如下图所示。
Hibernate Maven 依赖项
我们最终的 pom.xml 包含 Hibernate 和 MySQL 驱动程序的依赖项。
<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.journaldev.hibernate</groupId>
<artifactId>HQLExample</artifactId>
<version>0.0.1-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>4.3.5.Final</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.0.5</version>
</dependency>
</dependencies>
</project>
Hibernate 配置 XML
我们的 Hibernate 配置 xml 文件包含数据库连接相关属性和映射类。我将使用注释进行 Hibernate 映射。hibernate.cfg.xml 代码：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"https://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.password">pankaj123</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/TestDB</property>
<property name="hibernate.connection.username">pankaj</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="hibernate.show_sql">true</property>
<mapping class="com.journaldev.hibernate.model.Employee"/>
<mapping class="com.journaldev.hibernate.model.Address"/>
</session-factory>
</hibernate-configuration>
Hibernate SessionFactory 实用程序类
我们有一个实用程序类来配置 Hibernate SessionFactory。
package com.journaldev.hibernate.util;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
public class HibernateUtil {
private static SessionFactory sessionFactory;
private static SessionFactory buildSessionFactory() {
try {
// Create the SessionFactory from hibernate.cfg.xml
Configuration configuration = new Configuration();
configuration.configure("hibernate.cfg.xml");
System.out.println("Hibernate Configuration loaded");
ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
System.out.println("Hibernate serviceRegistry created");
SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);
return sessionFactory;
}
catch (Throwable ex) {
System.err.println("Initial SessionFactory creation failed." + ex);
ex.printStackTrace();
throw new ExceptionInInitializerError(ex);
}
}
public static SessionFactory getSessionFactory() {
if(sessionFactory == null) sessionFactory = buildSessionFactory();
return sessionFactory;
}
}
具有基于注释的映射的模型类
我们的带有 JPA 注释的模型类如下所示。
package com.journaldev.hibernate.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import org.hibernate.annotations.Cascade;
@Entity
@Table(name = "EMPLOYEE")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "emp_id")
private long id;
@Column(name = "emp_name")
private String name;
@Column(name = "emp_salary")
private double salary;
@OneToOne(mappedBy = "employee")
@Cascade(value = org.hibernate.annotations.CascadeType.ALL)
private Address address;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
}
package com.journaldev.hibernate.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;
@Entity
@Table(name = "ADDRESS")
public class Address {
@Id
@Column(name = "emp_id", unique = true, nullable = false)
@GeneratedValue(generator = "gen")
@GenericGenerator(name = "gen", strategy = "foreign",
parameters = { @Parameter(name = "property", value = "employee") })
private long id;
@Column(name = "address_line1")
private String addressLine1;
@Column(name = "zipcode")
private String zipcode;
@Column(name = "city")
private String city;
@OneToOne
@PrimaryKeyJoinColumn
private Employee employee;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getAddressLine1() {
return addressLine1;
}
public void setAddressLine1(String addressLine1) {
this.addressLine1 = addressLine1;
}
public String getZipcode() {
return zipcode;
}
public void setZipcode(String zipcode) {
this.zipcode = zipcode;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public Employee getEmployee() {
return employee;
}
public void setEmployee(Employee employee) {
this.employee = employee;
}
}
HQL 示例测试类
我们来看看如何在Java程序中使用HQL。
package com.journaldev.hibernate.main;
import java.util.Arrays;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import com.journaldev.hibernate.model.Employee;
import com.journaldev.hibernate.util.HibernateUtil;
public class HQLExamples {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
//Prep work
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.getCurrentSession();
//HQL example - Get All Employees
Transaction tx = session.beginTransaction();
Query query = session.createQuery("from Employee");
List<Employee> empList = query.list();
for(Employee emp : empList){
System.out.println("List of Employees::"+emp.getId()+","+emp.getAddress().getCity());
}
//HQL example - Get Employee with id
query = session.createQuery("from Employee where id= :id");
query.setLong("id", 3);
Employee emp = (Employee) query.uniqueResult();
System.out.println("Employee Name="+emp.getName()+", City="+emp.getAddress().getCity());
//HQL pagination example
query = session.createQuery("from Employee");
query.setFirstResult(0); //starts with 0
query.setFetchSize(2);
empList = query.list();
for(Employee emp4 : empList){
System.out.println("Paginated Employees::"+emp4.getId()+","+emp4.getAddress().getCity());
}
//HQL Update Employee
query = session.createQuery("update Employee set name= :name where id= :id");
query.setParameter("name", "Pankaj Kumar");
query.setLong("id", 1);
int result = query.executeUpdate();
System.out.println("Employee Update Status="+result);
//HQL Delete Employee, we need to take care of foreign key constraints too
query = session.createQuery("delete from Address where id= :id");
query.setLong("id", 4);
result = query.executeUpdate();
System.out.println("Address Delete Status="+result);
query = session.createQuery("delete from Employee where id= :id");
query.setLong("id", 4);
result = query.executeUpdate();
System.out.println("Employee Delete Status="+result);
//HQL Aggregate function examples
query = session.createQuery("select sum(salary) from Employee");
double sumSalary = (Double) query.uniqueResult();
System.out.println("Sum of all Salaries= "+sumSalary);
//HQL join examples
query = session.createQuery("select e.name, a.city from Employee e "
+ "INNER JOIN e.address a");
List<Object[]> list = query.list();
for(Object[] arr : list){
System.out.println(Arrays.toString(arr));
}
//HQL group by and like example
query = session.createQuery("select e.name, sum(e.salary), count(e)"
+ " from Employee e where e.name like '%i%' group by e.name");
List<Object[]> groupList = query.list();
for(Object[] arr : groupList){
System.out.println(Arrays.toString(arr));
}
//HQL order by example
query = session.createQuery("from Employee e order by e.id desc");
empList = query.list();
for(Employee emp3 : empList){
System.out.println("ID Desc Order Employee::"+emp3.getId()+","+emp3.getAddress().getCity());
}
//rolling back to save the test data
tx.rollback();
//closing hibernate resources
sessionFactory.close();
}
}
请注意，我使用 HQL 进行选择、更新和删除操作。它还展示了如何使用 HQL Join 和 HQL Aggregate 函数。当我运行上述 hql 示例程序时，我们得到以下输出。
May 22, 2014 1:55:37 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {4.0.4.Final}
May 22, 2014 1:55:37 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {4.3.5.Final}
May 22, 2014 1:55:37 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
May 22, 2014 1:55:37 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
May 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration configure
INFO: HHH000043: Configuring from resource: hibernate.cfg.xml
May 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: HHH000040: Configuration resource: hibernate.cfg.xml
May 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration doConfigure
INFO: HHH000041: Configured SessionFactory: null
Hibernate Configuration loaded
Hibernate serviceRegistry created
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!)
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000401: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost/TestDB]
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000046: Connection properties: {user=pankaj, password=****}
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000006: Autocommit mode: false
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
May 22, 2014 1:55:37 PM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
May 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.internal.LobCreatorBuilder useContextualLobCreation
INFO: HHH000423: Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4
May 22, 2014 1:55:38 PM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)
May 22, 2014 1:55:38 PM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
List of Employees::1,San Jose
List of Employees::2,Santa Clara
List of Employees::3,Bangalore
List of Employees::4,New Delhi
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_ where employee0_.emp_id=?
Employee Name=Lisa, City=Bangalore
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_
Paginated Employees::1,San Jose
Paginated Employees::2,Santa Clara
Paginated Employees::3,Bangalore
Paginated Employees::4,New Delhi
Hibernate: update EMPLOYEE set emp_name=? where emp_id=?
Employee Update Status=1
Hibernate: delete from ADDRESS where emp_id=?
Address Delete Status=1
Hibernate: delete from EMPLOYEE where emp_id=?
Employee Delete Status=1
Hibernate: select sum(employee0_.emp_salary) as col_0_0_ from EMPLOYEE employee0_
Sum of all Salaries= 600.0
Hibernate: select employee0_.emp_name as col_0_0_, address1_.city as col_1_0_ from EMPLOYEE employee0_ inner join ADDRESS address1_ on employee0_.emp_id=address1_.emp_id
[Pankaj Kumar, San Jose]
[David, Santa Clara]
[Lisa, Bangalore]
Hibernate: select employee0_.emp_name as col_0_0_, sum(employee0_.emp_salary) as col_1_0_, count(employee0_.emp_id) as col_2_0_ from EMPLOYEE employee0_ where employee0_.emp_name like '%i%' group by employee0_.emp_name
[David, 200.0, 1]
[Lisa, 300.0, 1]
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_ order by employee0_.emp_id desc
ID Desc Order Employee::3,Bangalore
ID Desc Order Employee::2,Santa Clara
ID Desc Order Employee::1,San Jose
May 22, 2014 1:55:38 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH000030: Cleaning up connection pool [jdbc:mysql://localhost/TestDB]
Notice that once delete operation is performed, further operations are not showing that record data (sum of salary is 600). However I am rolling back the transaction, so the data in table will remain unchanged. Change the code to commit the transaction and it will be reflected in the database tables. I don’t like using HQL query a lot because as you can see that we need to take care of table mappings in our code. If we will use Session to delete the Employee object, it will delete the record from both the tables. You can download the sample hql example project from below link and try more examples.
Download Hibernate HQL Project