HQL
HQL是HIbernate Query Language 的缩写,提供更加丰富灵活、更为强大的查询能力;HQL更接近SQL语句查询语法。
Hibernate查询语言(HQL)是一种面向对象的查询语言,类似与SQL,但不是去对表和列进行操作,而是面向对象和它们的属性。HQL查询被Hibernate翻译为传统的SQL查询从而对数据库进行操作。
注 意 : 除 了 J a v a 类 与 属 性 名 称 外 , 查 询 语 句 对 大 小 写 不 敏 感 。 当 有 相 同 的 实 体 类 名 时 , 必 须 使 用 包 名 . 类 名 。 \color{red}{注意:除了Java类与属性名称外,查询语句对大小写不敏感。当有相同的实体类名时,必须使用包名.类名。} 注意:除了Java类与属性名称外,查询语句对大小写不敏感。当有相同的实体类名时,必须使用包名.类名。
HQL查询的步骤:
- 获得HibernateSession对象
- 编写HQL语句
- 调用Sesison的createQuery方法创建查询对象
- 如果HQL语句包含参数,则调用Query的setXxx方法为参数赋值
- 调用Query对象的list等方法返回查询结果。
准备工作
创建一个Customer实体类
package pers.zhang.domain;
public class Customer {
private Long cust_id;
private String cust_name;
private String cust_source;
private String cust_industry;
private String cust_level;
private String cust_linkman;
private String cust_phone;
private String cust_mobile;
public Long getCust_id() {
return cust_id;
}
public void setCust_id(Long cust_id) {
this.cust_id = cust_id;
}
public String getCust_name() {
return cust_name;
}
public void setCust_name(String cust_name) {
this.cust_name = cust_name;
}
public String getCust_source() {
return cust_source;
}
public void setCust_source(String cust_source) {
this.cust_source = cust_source;
}
public String getCust_industry() {
return cust_industry;
}
public void setCust_industry(String cust_industry) {
this.cust_industry = cust_industry;
}
public String getCust_level() {
return cust_level;
}
public void setCust_level(String cust_level) {
this.cust_level = cust_level;
}
public String getCust_linkman() {
return cust_linkman;
}
public void setCust_linkman(String cust_linkman) {
this.cust_linkman = cust_linkman;
}
public String getCust_phone() {
return cust_phone;
}
public void setCust_phone(String cust_phone) {
this.cust_phone = cust_phone;
}
public String getCust_mobile() {
return cust_mobile;
}
public void setCust_mobile(String cust_mobile) {
this.cust_mobile = cust_mobile;
}
@Override
public String toString() {
return "Customer [cust_id=" + cust_id + ", cust_name=" + cust_name + "]";
}
}
配置ORM元数据:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="pers.zhang.domain" >
<class name="Customer" table="cst_customer" >
<id name="cust_id" >
<generator class="identity"></generator>
</id>
<property name="cust_name" column="cust_name" ></property>
<property name="cust_source" column="cust_source" ></property>
<property name="cust_industry" column="cust_industry" ></property>
<property name="cust_level" column="cust_level" ></property>
<property name="cust_linkman" column="cust_linkman" ></property>
<property name="cust_phone" column="cust_phone" ></property>
<property name="cust_mobile" column="cust_mobile" ></property>
</class>
</hibernate-mapping>
准备数据:
基本查询–返回List
- HQL查询的from语句
如果你想要在存储中加载一个完整并持久的对象,你将使用FROM语句。Hibernate中最简单的查询语句的形式如下:
from pers.zhang.Customer
该自居简单的返回Customer类的所有实例。通常我们不需要使用类的全限定名,因为auto-import(自动引入)是缺省的情况。所以我们几乎只使用如下的简单写法:
from Customer - HQL查询的AS 语句
大多数情况下,需要指定一个别名,原因是可能需要在查询语句的其他部分引用到Customer:
from Customer as c
这个语句把别名c指定给类Customer的实例,这样就可以在随后的查询中使用此别名了。关键字as是可选的,所以也可以这样写:
from Customer c
例子:
@Test
//基本查询
public void fun1(){
//获得session
Session session = HibernateUtils.openSession();
//控制事务
Transaction tx = session.beginTransaction();
//3执行操作
//-------------------------------------------
//书写HQL语句
//String hql = " from cn.itheima.domain.Customer ";
String hql = " from Customer c"; // 查询所有Customer对象
//根据HQL语句创建查询对象
Query query = session.createQuery(hql);
//根据查询对象获得查询结果
List<Customer> list = query.list(); // 返回list结果
System.out.println(list);
//-------------------------------------------
//4提交事务.关闭资源
tx.commit();
session.close();
}
运行JUnit测试输出:
Hibernate:
select
customer0_.cust_id as cust_id1_0_,
customer0_.cust_name as cust_nam2_0_,
customer0_.cust_source as cust_sou3_0_,
customer0_.cust_industry as cust_ind4_0_,
customer0_.cust_level as cust_lev5_0_,
customer0_.cust_linkman as cust_lin6_0_,
customer0_.cust_phone as cust_pho7_0_,
customer0_.cust_mobile as cust_mob8_0_
from
cst_customer customer0_
[Customer [cust_id=1, cust_name=Google],
Customer [cust_id=2, cust_name=联想],
Customer [cust_id=3, cust_name=百度],
Customer [cust_id=4, cust_name=阿里巴巴],
Customer [cust_id=5, cust_name=腾讯]]
条件查询–返回唯一对象或List
如果想要精准的从数据库存储中返回特定对象,需要使用where语句。在where字句中允许使用的表达式包括大多数可以在SQL中使用的表达式种类:
- 数学运算符+,-,*,/
- 二进制比较运算符=,>=,<=,<>,!=,like
- 逻辑运算符and,or,not
- in, not in, between, is null, is not null, is empty, is not empty, member of and not member of
- “简单的” case, case … when … then … else … end, 和“搜索” case, case when … then … else … end
- 字符串连接符… || … or concat(…,…)
- current_date(), current_time(), current_timestamp()
- second(…),minute(…),hour(…),day(…),month(…),year(…)
- EJB-QL 3.0 定义的任何函数或操作:substring(),trim(),lower(),upper(),length(),locate(),abs(),sqrt(),bit_length()
- coalesce()和nullif()
- cast(… as …), 其第二个参数是某Hibernate类型的名字,以及extract(… from …),只要ANSI cast() 和 extract() 被底层数据库支持
- JDBC参数传入?
- 命名参数:name,start_date, :xl
- SQL 直接常量 ‘foo’, 69, ‘1970-01-01 10:00:01.0’
- Java public static final 类型常量
关键字in与between可按如下方法使用:
from Customer c where c.cust_name between ‘A’ and ‘B’
from Customer c where c.cust_name in (‘Google’, ‘百度’)
而且否定的格式也可以如下书写:
from Customer c where c.cust_name not between ‘A’ and ‘B’
from Customer c where c.cust_name not in (‘Google’, ‘百度’)
例子:
@Test
//条件查询
//HQL语句中,不可能出现任何数据库相关的信息的
public void fun2(){
//1 获得session
Session session = HibernateUtils.openSession();
//2 控制事务
Transaction tx = session.beginTransaction();
//3执行操作
//-------------------------------------------
//书写HQL语句,条件查询
String hql1 = " from Customer where cust_id = 1 ";
String hql2 = "from Customer c where c.cust_name in ('Google', '百度', '联想')";
//2根据HQL语句创建查询对象
Query query1 = session.createQuery(hql1);
Query query2 = session.createQuery(hql2);
//3根据查询对象获得查询结果
Customer c1 = (Customer) query1.uniqueResult();
List<Customer> list = query2.list();
System.out.println(c1);
System.out.println(list);
//-------------------------------------------
//4提交事务.关闭资源
tx.commit();
session.close();
}
运行JUnit测试输出:
Hibernate:
select
customer0_.cust_id as cust_id1_0_,
customer0_.cust_name as cust_nam2_0_,
customer0_.cust_source as cust_sou3_0_,
customer0_.cust_industry as cust_ind4_0_,
customer0_.cust_level as cust_lev5_0_,
customer0_.cust_linkman as cust_lin6_0_,
customer0_.cust_phone as cust_pho7_0_,
customer0_.cust_mobile as cust_mob8_0_
from
cst_customer customer0_
where
customer0_.cust_id=1
Hibernate:
select
customer0_.cust_id as cust_id1_0_,
customer0_.cust_name as cust_nam2_0_,
customer0_.cust_source as cust_sou3_0_,
customer0_.cust_industry as cust_ind4_0_,
customer0_.cust_level as cust_lev5_0_,
customer0_.cust_linkman as cust_lin6_0_,
customer0_.cust_phone as cust_pho7_0_,
customer0_.cust_mobile as cust_mob8_0_
from
cst_customer customer0_
where
customer0_.cust_name in (
'Google' , '百度' , '联想'
)
Customer [cust_id=1, cust_name=Google]
[Customer [cust_id=1, cust_name=Google], Customer [cust_id=2, cust_name=联想], Customer [cust_id=3, cust_name=百度]]
条件查询–?占位符
在HQL语句中使用?占位符,然后根据索引和类型使用setXxx方法设置参数。
s
e
t
X
x
x
(
i
n
d
e
x
,
a
r
g
)
中
i
n
d
e
x
从
0
开
始
,
与
S
Q
L
的
从
1
开
始
不
同
!
\color{red}{setXxx(index, arg)中index从0开始,与SQL的从1开始不同!}
setXxx(index,arg)中index从0开始,与SQL的从1开始不同!
例子:
@Test
//条件查询
//问号占位符
public void fun3(){
//1 获得session
Session session = HibernateUtils.openSession();
//2 控制事务
Transaction tx = session.beginTransaction();
//3执行操作
//-------------------------------------------
//书写HQL语句
String hql = " from Customer where cust_id = ? ";
//根据HQL语句创建查询对象
Query query = session.createQuery(hql);
//设置参数
//query.setLong(0, 1l);//指定类型
query.setParameter(0, 1l);//自动匹配类型
//根据查询对象获得查询结果
Customer c = (Customer) query.uniqueResult();
System.out.println(c);
//-------------------------------------------
//4提交事务.关闭资源
tx.commit();
session.close();
}
运行JUnit测试输出:
Hibernate:
select
customer0_.cust_id as cust_id1_0_,
customer0_.cust_name as cust_nam2_0_,
customer0_.cust_source as cust_sou3_0_,
customer0_.cust_industry as cust_ind4_0_,
customer0_.cust_level as cust_lev5_0_,
customer0_.cust_linkman as cust_lin6_0_,
customer0_.cust_phone as cust_pho7_0_,
customer0_.cust_mobile as cust_mob8_0_
from
cst_customer customer0_
where
customer0_.cust_id=?
Customer [cust_id=1, cust_name=Google]
条件查询–命名占位符
命名占位符使用 “:” + “占位符名字” 的格式,随后使用query.setParameter(“占位符名字”, arg)来设置参数。
例子:
@Test
//条件查询
//命名占位符
public void fun4(){
//1 获得session
Session session = HibernateUtils.openSession();
//2 控制事务
Transaction tx = session.beginTransaction();
//3执行操作
//-------------------------------------------
//书写HQL语句
String hql = " from Customer where cust_id = :id ";
//根据HQL语句创建查询对象
Query query = session.createQuery(hql);
//设置参数
query.setParameter("id", 1l);
//根据查询对象获得查询结果
Customer c = (Customer) query.uniqueResult();
System.out.println(c);
//-------------------------------------------
//4提交事务.关闭资源
tx.commit();
session.close();
}
运行JUnit测试输出:
Hibernate:
select
customer0_.cust_id as cust_id1_0_,
customer0_.cust_name as cust_nam2_0_,
customer0_.cust_source as cust_sou3_0_,
customer0_.cust_industry as cust_ind4_0_,
customer0_.cust_level as cust_lev5_0_,
customer0_.cust_linkman as cust_lin6_0_,
customer0_.cust_phone as cust_pho7_0_,
customer0_.cust_mobile as cust_mob8_0_
from
cst_customer customer0_
where
customer0_.cust_id=?
Customer [cust_id=1, cust_name=Google]
分页查询
HQL的分页查询与MySql的limit十分相似,使用两个方法:
1.query.setFirstResult(arg),设置第一条结果从哪个索引开始,相当于limit中的第一个?。
2.query.setMaxResults(arg),设置一次查询多少条数据,相遇于limit中的第二个?。
例子:
@Test
//分页查询
public void fun5(){
//1获得session
Session session = HibernateUtils.openSession();
//2控制事务
Transaction tx = session.beginTransaction();
//3执行操作
//-------------------------------------------
//书写HQL语句
String hql = " from Customer "; // 查询所有Customer对象
//根据HQL语句创建查询对象
Query query = session.createQuery(hql);
//设置分页信息 limit ?,?
query.setFirstResult(1);
query.setMaxResults(2);
//据查询对象获得查询结果
List<Customer> list = query.list();
System.out.println(list);
//-------------------------------------------
//4提交事务.关闭资源
tx.commit();
session.close();
}
运行JUnit测试输出:
Hibernate:
select
customer0_.cust_id as cust_id1_0_,
customer0_.cust_name as cust_nam2_0_,
customer0_.cust_source as cust_sou3_0_,
customer0_.cust_industry as cust_ind4_0_,
customer0_.cust_level as cust_lev5_0_,
customer0_.cust_linkman as cust_lin6_0_,
customer0_.cust_phone as cust_pho7_0_,
customer0_.cust_mobile as cust_mob8_0_
from
cst_customer customer0_ limit ?,
?
[Customer [cust_id=2, cust_name=联想], Customer [cust_id=3, cust_name=百度]]