定义
- Hibernate Query Language, Hiberhate查询语言
- HQL是面向对象的查询语言
- HQL语句形式
Paste_Image.png - 点击查看练习代码
一．org.hibernate.Query接口
-
Query接口定义有执行查询的方法
Paste_Image.png Session的createQuery()方法创建Query实例
createQuery()方法包含一个HQL语句参数， createQuery(hql)
Query接口的list()方法执行HQL查询
list()方法返回结果数据类型为java.util.List, list接合中存放符合查询条件的持久化对象；
/**
* 查询班级信息
*/
@Test
public void testGrade() {
//定义hql语句，Grade类不需要写全限定名：entity.Grade, 默认情况会auto-import
String hql = "from Grade";
Query query = session.createQuery(hql);
List<Grade> res = query.list();
for (Grade grade : res) {
//只输出班级名称，则hibernate只查询grade表，不产生关联查询
System.out.println(grade.getGname());
//当输出班级包含的学生属性时候，hibernate会根据关联关系查询student2表数据
System.out.println(grade.getStudent2());
}
}
二．from子句中持久化类的引用
- 不需要引入持久化类的全限定名，直接引入类名, 比如要查询
- auto-import(自动引入)缺省情况
三．select子句返回选择属性的格式
- 以对象数组Object[]形式返回选择的属性
/**
* 以Object数组形式返回查询属性
*/
@Test
public void testSelectColumnObjectArray()
{
/*
//使用select查询班级id和名称，返回对象数组
String hql = "SELECT g.gid, g.gname FROM Grade AS g";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] objects : list) {
System.out.println("id = " + objects[0] + "name: " + objects[1]);
} */
//当只查询一个属性的时候，返回的是对象，而不是对象数组
String hql1 = "SELECT g.gname FROM Grade AS g";
Query query1 = session.createQuery(hql1);
List<Object> list1 = query1.list();
System.out.println("name = " + list1.get(0));
}
- 以List形式放回选择的属性
/**
* 以List集合形式返回查询属性, select语句中指定new list
*/
@Test
public void testSelectColumnList()
{
//以List集合形式返回查询属性 SELECT new list(g.gid, g.gname)
String hql = "SELECT new list(g.gid, g.gname) FROM Grade AS g";
Query query = session.createQuery(hql);
List<List> lists = query.list();
for (List list : lists) {
System.out.println("id = " + list.get(0) + "name = " + list.get(1));
}
}
- 以map形式返回选择的属性
/**
* 以Map集合形式返回查询属性, select语句中指定new map
* 如果指定属性未定义AS别名，在获取的时候要使用get("0")字符串下标方式获取
* 如果指定属性定义AS别名，在获取的时候可以使用属性别名作为key查询
*/
@Test
public void testSelectColumnMap()
{
//以List集合形式返回查询属性 SELECT new map(g.gid, g.gname)
//String hql = "SELECT new map(g.gid AS id, g.gname AS name) FROM Grade AS g";
String hql = "SELECT new map(g.gid , g.gname ) FROM Grade AS g";
Query query = session.createQuery(hql);
List<Map> maps = query.list();
for (Map map : maps) {
//未定义属性别名的获取方式
System.out.println("id = " + map.get("0") + "name = " + map.get("1"));
//定义属性别名的获取方式
System.out.println("id = " + map.get("id") + "name = " + map.get("name"));
}
}
- 以自定义类型返回选择的属性
(1)先在Grade实体类添加构造方法
public Grade(String gname, String gdesc) {
this.gname = gname;
this.gdesc = gdesc;
}
(2)测试程序
/**
* 以自定义方式返回查询属性，需要在查询的实体类中定义构造方法
* select语句中指定new 实体类名
*/
@Test
public void testSelectColumnCustomer()
{
String hql = "SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g";
Query query = session.createQuery(hql);
List<Grade> grades = query.list();
for (Grade grade : grades) {
//定义属性别名的获取方式
System.out.println("name = " + grade.getGname()+ "desc = " + grade.getGdesc());
}
}
四．where查询
- 比较运算符 =, <>, <, >, >=, <=
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.gid > 5
- null值判断, is [not] null
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.gid is null
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.gid is not null
或
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.gid = null
- 范围运算：
- [not] in (列表)
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.gid > 5 AND g.gid in (8,10)
- [not] between value1 and value2, 属性在value1和value2之间
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.gid > 5 AND g.gid in (8,10) AND g.gid BETWEEN 1 and 10
或
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.gid > 5 AND g.gid in (8,10) AND g.gid NOT BETWEEN 1 and 10
- 字符串模式匹配
- like
- 通配符百分号
%: 匹配任意个字符
模糊查询包含北京的数据
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.name like '%北京%'
通配符下划线 `_` ：匹配一个字符
查询名字为以张开头，两个字的数据
```
SELECT new Grade( g.gname , g.gdesc) FROM Grade AS g WHERE g.name like '张_'
```
* 逻辑运算符,使用方式与sql语句一致
+ and(逻辑与)， or(逻辑或)
+ not (非)
* 集合运算符
> 注意：会产生子查询，个人不建议使用
+ is[not] empty 集合[不]为空，不包含任何元素
//查询班级属性中学生属性不为空的班级信息
String hql = "SELECT new map(g.gid as id , g.gname as name ) FROM Grade AS g WHERE g.student2 is not empty";
解析为sql
select
grade0_.gid as col_0_0_,
grade0_.gname as col_1_0_
from
grade grade0_
where
exists (
select
student2x1_.sid
from
student2 student2x1_
where
grade0_.gid=student2x1_.gid
)
+ member of 元素属于集合
String hql = "SELECT s FROM Student2 AS s WHERE s member of grade.student2";
解析为sql
select
student2x0_.sid as col_0_0_
from
student2 student2x0_ cross
join
grade grade1_
where
student2x0_.gid=grade1_.gid
and (
student2x0_.sid in (
select
student2x2_.sid
from
student2 student2x2_
where
grade1_.gid=student2x2_.gid
)
)
* 四则运算
+ HQL语句中也可以使用 + - * / 四则运算
String hql = "SELECT g FROM Grade AS g where g.score*2 > 800";
+ 四则运算可以在where子句和select子句中使用
* 查询单个对象
+ Query 接口的uniqueResult方法
/**
* 使用query.uniqueResult()返回一个对象（只返回一条记录）
* 如果结果有多条则会报错
*/
@Test
public void testGetOne()
{
String hql = "SELECT g FROM Grade AS g where g.gid = 1 order by id asc";
Query query = session.createQuery(hql);
Grade grade = (Grade)query.uniqueResult();
System.out.println("name = " + grade.getGname());
}
* order by 子句
先按着s.gid正序排列，如果发现s.gid有相同的情况，那么以s.sid对s.gid相同的部分做倒序排列
SELECT s FROM Student AS s where order by s.gid asc, s.sid desc;
