概述
HQL是Hibernate封装成为面向对象的数据库查询语言,具有如下特点:
- 面向对象,包括继承、多态和关联之类的概念,SQL操作的数据库的表,HQL更像是操作对象
- 大小写敏感,只对对象和属性敏感,关键字不区分大小写,区别于SQL
简单查询语句
在看完HQL的特点之后,相信大家对于HQL的掌握有一定想法,我觉得就是使用面向对象的思维结合SQL的语法来学习HQL是比较快速且便于理解的。
现举例说明,假设有Course类(表),Subject学科类别,CourseTable课程表,相互关系如下:
Course对Subject:多对多双向,即Course中有一个Subject的Set,Subject中有一个Course的Set
Course对CourseTable:多对一单向,即CourseTable中有一个Course对象
查询所有课程
- from Course
from Course
查询课程名称为math的课程
- from Course as course where course.name='math'
from Course as course where course.name='math'
查询课程名称为math的课程总数
- select count(*) from Course as course where course.name='math'
select count(*) from Course as course where course.name='math'
查询所有课程名称为math的课程,按id升序
- select count(*) from Course as course where course.name='math' order by course.id asc
select count(*) from Course as course where course.name='math' order by course.id asc
查询所有课程,按id升序,name降序
- select count(*) from Course as course order by course.id asc, course.name desc
select count(*) from Course as course order by course.id asc, course.name desc
注意:
- Course指的是类而不是表,所以要区分大小写
- course.id指的是course对象的属性,而不是表的列,注意大小写
- HQL也支持使用函数,包括avg,sum,count,min,max
连接表查询
HQL的表连接参考了ANSI SQL,包含以下集中连接方式(括弧表示outer可以不写):
- inner join内连接
- left (outer) join 左外链接
- right(outer) join 右外链接
- full join 全连接(不常用)
内连接inner join
内连接结果中包含两张表中符合条件的行。
查询属于计算机科学学科(Subject)的所有课程(Course中有一个Subject Set)
显式内连接:
- from Course as course inner join course.subjects as subject where subject.name='计算机科学'
from Course as course inner join course.subjects as subject where subject.name='计算机科学'
注意上述查询语句的结果是List<Object[]>,每个Object数据包含Course和Subject对象,如果要只返回Course:
- select distinct course
- from Course as course
- inner join course.subjects as subject
- where subject.name='计算机科学'
select distinct course
from Course as course
inner join course.subjects as subject
where subject.name='计算机科学'
隐式内连接,如果课程和Subject是一对一的关系:
- from Course as course where course.subject.name='计算机科学'
from Course as course where course.subject.name='计算机科学'
查询的结果直接就是Course对象的list
外连接
左外链接,包含两张表所有列,保留左面表的所有行,右面表没有的行null填充
- select distinct course
- from Course as course
- left join course.subjects as subject
- where subject.name='计算机科学'
select distinct course
from Course as course
left join course.subjects as subject
where subject.name='计算机科学'
查询结果也是List<Object[]>,如果要只返回Course对象,使用select distinct course
右外链接,包含两张表所有列,保留右面表的所有行,左面表没有的行null填充
- select distinct course
- from Course as course
- right join course.subjects as subject
- where subject.name='计算机科学'
select distinct course
from Course as course
right join course.subjects as subject
where subject.name='计算机科学'
多表查询
查询某张课程表(CourseTable)里面的所有课程(Course),课程表对课程多对一,单向
- from Course as course, CourseTable as courseTable
- where course.id=courseTable.course.id
from Course as course, CourseTable as courseTable
where course.id=courseTable.course.id
查询结果为List<Object[]>,每个Object[]包含Course和CourseTable,如果只查询course,使用select distinct course