HQL –Hibernate 查询语言–示例教程
时间：2020-02-23 14:41:25 来源:igfitidea点击:
HQL或者Hibernate查询语言是Hibernate Framework的面向对象查询语言。
HQL与SQL非常相似，只是我们使用对象而不是表名，这使其更接近于面向对象的编程。
Hibernate 查询语言– HQL
HQL和区分大小写：除了Java类和变量名外，HQL不区分大小写。
因此，" SeLeCT"与" SELEct"相同，与" SELECT"相同，但是" com.theitroad.model.Employee"与" com.theitroad.model.EMPLOYEE"不同。
HQL中一些普遍支持的子句是：
HQL发件人：HQL发件人与SQL中的select子句相同，
from Employee与select * from Employee相同。
我们还可以创建别名，例如" from emp emp"或者" from employee emp"。HQL联接：HQL支持内部联接，左外部联接，右外部联接和完全联接。
例如，"从员工e INNER JOIN e.address a中选择e.name，a.city"。
在此查询中，Employee类应具有一个名为address的变量。
我们将在示例代码中对其进行研究。聚合函数：HQL支持常用的聚合函数，例如count(*)，count(distinct x)，min()，max()，avg()和sum()。
表达式：HQL支持算术表达式(+，-，*，/)，二进制比较运算符(=，> =，
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, 'hyman', 100), (2, 'David', 200), (3, 'Lisa', 300), (4, 'Hyman', 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.theitroad.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">hyman123</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/TestDB</property>
<property name="hibernate.connection.username">hyman</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.theitroad.hibernate.model.Employee"
<mapping class="com.theitroad.hibernate.model.Address"
</session-factory>
</hibernate-configuration>
Hibernate SessionFactory Utility类
我们有一个实用程序类来配置Hibernate SessionFactory。
package com.theitroad.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.theitroad.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.theitroad.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.theitroad.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.theitroad.hibernate.model.Employee;
import com.theitroad.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", "hyman 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示例程序之上运行时，我们得到以下输出。
Jan 22, 2014 1:55:37 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {4.0.4.Final}
Jan 22, 2014 1:55:37 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {4.3.5.Final}
Jan 22, 2014 1:55:37 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
Jan 22, 2014 1:55:37 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
Jan 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration configure
INFO: HHH000043: Configuring from resource: hibernate.cfg.xml
Jan 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: HHH000040: Configuration resource: hibernate.cfg.xml
Jan 22, 2014 1:55:37 PM org.hibernate.cfg.Configuration doConfigure
INFO: HHH000041: Configured SessionFactory: null
Hibernate Configuration loaded
Hibernate serviceRegistry created
Jan 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!)
Jan 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]
Jan 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000046: Connection properties: {user=hyman, password=}
Jan 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000006: Autocommit mode: false
Jan 22, 2014 1:55:37 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
Jan 22, 2014 1:55:37 PM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
Jan 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
Jan 22, 2014 1:55:38 PM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)
Jan 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
[hyman 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
Jan 22, 2014 1:55:38 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH000030: Cleaning up connection pool [jdbc:mysql://localhost/TestDB]
请注意，一旦执行了删除操作，其他操作就不会显示该记录数据(薪金总和为600)。
但是，我正在回滚事务，因此表中的数据将保持不变。
更改代码以提交事务，它将反映在数据库表中。
