学习笔记,为了加强记忆和深入理解在此记录备忘。如有错误和理解不当之处,还望指正。万分感谢!
另外,实例和内容有些是参照网络其他博文,如有侵权可联系删掉。
参考博客 地址:https://www.cnblogs.com/whgk/p/6159336.html
目录
4.5.5 迫切左外连接, left outer join fetch
一、导航对象图查询
获取已经加载对象的关联对象信息,通过已加载的对象查询其关联的对象。
例如:通过部门查询员工信息
@Test
public void testGet() {
// 加载部门对象
Dept dept = session.get(Dept.class, 1);
Set<Employee> employees = new HashSet<>();
// 通过部门获取员工
employees = dept.getEmps();
for (Employee emp:employees) {
System.out.println(emp.getEmpName());
}
Hibernate SQL:
SELECT
dept0_.deptId AS deptId1_0_0_,
dept0_.deptName AS deptName2_0_0_
FROM
t_dept dept0_
WHERE
dept0_.deptId =?
Hibernate SQL:
SELECT
emps0_.deptId AS deptId3_1_0_,
emps0_.empId AS empId1_1_0_,
emps0_.empId AS empId1_1_1_,
emps0_.empName AS empName2_1_1_,
emps0_.deptId AS deptId3_1_1_
FROM
t_employee emps0_
WHERE
emps0_.deptId =?
结果集是Employee对象:
[demo.entity.Employee@31edeac, demo.entity.Employee@67d86804]
二、OID查询
获取加载对象的信息
和导航对象图的区别是:OID是获取加载对象的信息;导航对象图是获取加载对象的关联对象信息。
例如:查询员工信息
@Test
public void testGetByOID() {
Employee employee = session.get(Employee.class, 1);
System.out.println(employee.getEmpName());
}
Hibernate SQL:
SELECT
employee0_.empId AS empId1_1_0_,
employee0_.empName AS empName2_1_0_,
employee0_.deptId AS deptId3_1_0_
FROM
t_employee employee0_
WHERE
employee0_.empId =?
结果集是emp对象:
demo.entity.Employee@2015b2cd
三、本地sql查询
使用标准的SQL语句来编写,需要考虑数据库之间的语法差异
例如:查询员工信息
@Test
public void testGetByLocalSQL() {
// 创建一个接收结果集的SQLQuery对象
SQLQuery SQLquery = session.createSQLQuery("select * from t_employee");
// 通过addEntity。来绑定到实体
List<Employee> list = SQLquery.addEntity(Employee.class).list();
for (Employee employee:list) {
System.out.println(employee);
}
}
四、hql查询
HQL: Hibernate Query Language ,是面向对象的查询语言,它和SQL查询语言有些相似,在Hibernate提供的各种检索方式中,HQL是使用的最广的一种检索方式,导入的包是 org.hibernate.query.Query
注意:HQL操作的全是POJO类中的属性,而不是操作数据库表中的字段。
4.1 查询全部员工
@Test
public void testGetByHQLAll() {
// 这里Employee是对象名,不是数据库表名
String hql = "from Employee";
Query<Employee> hqlQuery = session.createQuery(hql);
List<Employee> employees = hqlQuery.list();
System.out.println(employee);
}
Hibernate:
SELECT
employee0_.empId AS empId1_1_,
employee0_.empName AS empName2_1_,
employee0_.deptId AS deptId3_1_
FROM
t_employee employee0_
结果集是对象组成的数组:
[demo.entity.Employee@37d871c2,
demo.entity.Employee@7a904f32,
demo.entity.Employee@2b59501e,
demo.entity.Employee@476e8796]
4.2 查询员工编号是1的员工
@Test
public void testGetByHQLCond() {
// 查询编号为1的员工,empId 也不是数据库字段名称
String hql = "from Employee where empId = 1";
Query<Employee> hqlQuery = session.createQuery(hql);
// 如果知道只有一条使用uniqueResult
Employee employee = (Employee) hqlQuery.uniqueResult();
System.out.println(employee.getEmpName());
}
Hibernate SQL:
SELECT
employee0_.empId AS empId1_1_,
employee0_.empName AS empName2_1_,
employee0_.deptId AS deptId3_1_
FROM
t_employee employee0_
WHERE
employee0_.empId = 1
结果集一个员工对象:
demo.entity.Employee@21ac5eb4
4.3 查询员工部分属性
@Test
public void testGetByHQLName() {
// 查询编号为1的员工,empId也不是数据库字段名称
String hql = "select empName from Employee where empId = 1";
Query<Employee> hqlQuery = session.createQuery(hql);
// 集合中直接存放着name属性,不是对象
List<Employee> employee = hqlQuery.list();
System.out.println(employee);
}
Hibernate SQL:
SELECT
employee0_.empName AS col_0_0_
FROM
t_employee employee0_
结果集四个员工名称,不是员工对象:
[员工1, 员工2, 员工3, 员工4]
4.4 结果集封装
使用new List()或者new Map()或new Employee()将返回的值给封装起来,前提是实体类中有相关构造器
4.4.1 new Employee()封装
@Test
public void testGetByHQLConstructor() {
// 把结果集封装到Employee里
String hql = "select new Employee(empId, empName) from Employee";
Query<Employee> hqlQuery = session.createQuery(hql);
// 结果是Employee对象
List<Employee> employee = hqlQuery.list();
System.out.println(employee);
}
Hibernate SQL:
SELECT
employee0_.empId AS col_0_0_,
employee0_.empName AS col_1_0_
FROM
t_employee employee0_
结果集是四个对象的数组:
[demo.entity.Employee@4acb2510,
demo.entity.Employee@7be3a9ce,
demo.entity.Employee@37d871c2,
demo.entity.Employee@3baf6936]
4.4.2 new list()封装
@Test
public void testGetByHQLList() {
// 把结果集封装到List里
String hql = "select new List(empId, empName) from Employee";
Query<Employee> hqlQuery = session.createQuery(hql);
List<Employee> employee = hqlQuery.list();
System.out.println(employee);
}
Hibernate:
SELECT
employee0_.empId AS col_0_0_,
employee0_.empName AS col_1_0_
FROM
t_employee employee0_
结果集是二维数组,[员工号, 员工名]
[[1, 员工1], [2, 员工2], [3, 员工3], [4, 员工4]]
4.4.3 new Map()封装
@Test
public void testGetByHQLMap() {
// 把结果集封装到Map里
String hql = "select new Map(empId, empName) from Employee";
Query<Employee> hqlQuery = session.createQuery(hql);
List<Employee> employee = hqlQuery.list();
System.out.println(employee);
}
Hibernate:
SELECT
employee0_.empId AS col_0_0_,
employee0_.empName AS col_1_0_
FROM
t_employee employee0_
结果集是map和list的嵌套
[{0=1, 1=员工1}, {0=2, 1=员工2}, {0=3, 1=员工3}, {0=4, 1=员工4}]
4.5 分页
@Test
public void testGetByHQLLimit() {
String hql = "from Employee";
Query hqlQuery = session.createQuery(hql);
// 从数据库表中从第1条开始取2条记录。相当于MySQL的limit 1,2;
hqlQuery.setFirstResult(1);
hqlQuery.setMaxResults(2);
List<Employee> employees = hqlQuery.list();
System.out.println(employees);
}
Hibernate:
SELECT
employee0_.empId AS empId1_1_,
employee0_.empName AS empName2_1_,
employee0_.deptId AS deptId3_1_
FROM
t_employee employee0_
LIMIT ?, ?
结果集是Employee对象数组:
[demo.entity.Employee@4acb2510, demo.entity.Employee@4e1459ea]
4.5 支持连接查询
4.5.1 内连接 inner join
可以省略inner,直接join
@Test
public void testGetByHQLInner() {
//不用写ON后面的连接条件,因为hibernate映射文件已经全部写好了。
String hql = "from Employee d join d.dept";
Query hqlQuery = session.createQuery(hql);
//这里的泛型不能是Employee了。结合了两张表,集合中存放的是Object[],
//数组中存放的是Employee和Dept实体
List<Object[]> lists = hqlQuery.list();
// 结果集是以这种方式[[员工对象1, 部门对象1], [员工对象2, 部门对象2]...]保存在Object[]中
System.out.println(lists);
// 分开Employee和Dept
for (Object[] list:lists) {
Object[] objects = list;
Employee employee = (Employee) objects[0];
Dept dept = (Dept) objects[1];
System.out.println(employee.getEmpName());
System.out.println(dept.getDeptName());
}
}
Hibernate:
SELECT
employee0_.empId AS empId1_1_0_,
dept1_.deptId AS deptId1_0_1_,
employee0_.empName AS empName2_1_0_,
employee0_.deptId AS deptId3_1_0_,
dept1_.deptName AS deptName2_0_1_
FROM
t_employee employee0_
INNER JOIN t_dept dept1_ ON employee0_.deptId = dept1_.deptId
结果集是以这种方式[[员工对象1, 部门对象1], [员工对象2, 部门对象2]...]保存在Object[]中
[[Ljava.lang.Object;@24934262,
[Ljava.lang.Object;@93f432e,
[Ljava.lang.Object;@288214b1,
[Ljava.lang.Object;@16eedaa6]
4.5.2 迫切内连接 inner join fetch
内连接返回的list中是Object[],而迫切内连接返回的list中是POJO类对象
@Test
public void testGetByHQLInnerFetch() {
//不用写ON后面的连接条件,因为hibernate映射文件已经全部写好了。
String hql = "from Employee d join fetch d.dept";
Query hqlQuery = session.createQuery(hql);
// 这里只有Employee对象了,不是Dept和Employee的混合体
List<Employee> employees = hqlQuery.list();
// 结果集是Employee对象的集合
System.out.println(employees);
}
Hibernate:
SELECT
employee0_.empId AS empId1_1_0_,
dept1_.deptId AS deptId1_0_1_,
employee0_.empName AS empName2_1_0_,
employee0_.deptId AS deptId3_1_0_,
dept1_.deptName AS deptName2_0_1_
FROM
t_employee employee0_
INNER JOIN t_dept dept1_ ON employee0_.deptId = dept1_.deptId
结果集是Employee对象的集合
[demo.entity.Employee@36fc05ff,
demo.entity.Employee@2d9f64c9,
demo.entity.Employee@21ac5eb4,
demo.entity.Employee@715d6168]
4.5.3 隐式内连接
不写任何关键字,通过where完成
@Test
public void testGetByHQLInnerWhere() {
// 这里只能通过多方找一方,因为一方存放的是集合,就不能向下面这样赋值
String hql = "from Employee d where d.dept.deptName = '人事部'";
Query hqlQuery = session.createQuery(hql);
// 这里只有Employee对象了,不是Dept和Employee的混合体
List employees = hqlQuery.list();
// 结果集是Employee对象的集合
System.out.println(employees);
}
Hibernate:
SELECT
employee0_.empId AS empId1_1_,
employee0_.empName AS empName2_1_,
employee0_.deptId AS deptId3_1_
FROM
t_employee employee0_
CROSS JOIN t_dept dept1_
WHERE
employee0_.deptId = dept1_.deptId
AND dept1_.deptName = '人事部'
结果集是员工的对象
[demo.entity.Employee@2d9f64c9, demo.entity.Employee@6428591a]
4.5.4 左外连接,left outer join
可以省略outer,直接left join
参考:4.5.1 内连接 inner join
4.5.5 迫切左外连接, left outer join fetch
参考:4.5.2 迫切内连接 inner join fetch
4.5.6 右外连接:right outer join
参考:4.5.1 内连接 inner join
4.5.7 交叉连接
交叉连接会出现笛卡尔积。两张表连接起来,比如一张表中有3条记录,另一张表中也有3条记录,那么连接之后,就会出现9条数据,其中就有一些重复的数据
@Test
public void testGetByHQLCross() {
// 交叉连接,俩个部门四个员工,笛卡尔积是8
String hql = "from Employee, Dept";
Query hqlQuery = session.createQuery(hql);
// 是Dept和Employee的混合体
List lists = hqlQuery.list();
// 结果集是8
System.out.println(lists);
}
Hibernate:
SELECT
employee0_.empId AS empId1_1_0_,
dept1_.deptId AS deptId1_0_1_,
employee0_.empName AS empName2_1_0_,
employee0_.deptId AS deptId3_1_0_,
dept1_.deptName AS deptName2_0_1_
FROM
t_employee employee0_
CROSS JOIN t_dept dept1_
结果集是8个empl和Dept的混合对象
4.6 group分组
@Test
public void testGetByHQLGroup() {
// 使用部门id分组
String hql = "select count(*) from Employee e group by e.dept.deptId";
Query hqlQuery = session.createQuery(hql);
List lists = hqlQuery.list();
System.out.println(lists);
}
Hibernate:
SELECT
count( * ) AS col_0_0_
FROM
t_employee employee0_
GROUP BY
employee0_.deptId
结果集是查询到的统计数(俩个部门,每个部门俩人)
[2, 2]
4.7 函数
HQL使用函数和标准SQL一样
4.8 子查询
@Test
public void testGetByHQLSubQuery() {
// 子查询
String hql = "from Employee e where e.dept.deptId in (select deptId from Dept where deptId = '1')";
Query hqlQuery = session.createQuery(hql);
// 返回Employee对象
List lists = hqlQuery.list();
System.out.println(lists);
}
Hibernate :
SELECT
employee0_.empId AS empId1_1_,
employee0_.empName AS empName2_1_,
employee0_.deptId AS deptId3_1_
FROM
t_employee employee0_
WHERE
employee0_.deptId IN ( SELECT dept1_.deptId FROM t_dept dept1_ WHERE dept1_.deptId = '1' )
结果集是
[ demo.entity.Employee @4a14c44f,
demo.entity.Employee @cbc8d0f]
4.9 动态设置参数
可以传入参数,也可以传入对象
4.9.1 使用?代替所需要填入的值
在下面设置值时则从0开始算起,第一个?是处于0的位置,如果有两个?号,则使用0,1索引号来插入值。
@Test
public void testGetByHQLDyn() {
// ?的动态参数
String hql = "from Employee e where e.empId = ?0";
Query hqlQuery = session.createQuery(hql);
// 传入参数,从0开始算起,第一个?是处于0的位置,如果有两个?号,则使用0,1索引号来插入值
hqlQuery.setParameter(0, 1);
List lists = hqlQuery.list();
System.out.println(lists);
}
Hibernate :
SELECT
employee0_.empId AS empId1_1_,
employee0_.empName AS empName2_1_,
employee0_.deptId AS deptId3_1_
FROM
t_employee employee0_
WHERE
employee0_.empId =?
结果集
[ demo.entity.Employee@715d6168]
4.9.2 使用别名设置参数
使用":id"这个名字来表示插入值的名称,在下面则不用索引号来确定插入值的位置,直接是使用这个别称
@Test
public void testGetByHQLDynId() {
// ?的动态参数
String hql = "from Employee e where e.empId = :id";
Query hqlQuery = session.createQuery(hql);
// 传入参数,
hqlQuery.setParameter("id", 1);
List lists = hqlQuery.list();
System.out.println(lists);
}
Hibernate :
SELECT
employee0_.empId AS empId1_1_,
employee0_.empName AS empName2_1_,
employee0_.deptId AS deptId3_1_
FROM
t_employee employee0_
WHERE
employee0_.empId =?
结果集
[ demo.entity.Employee @5580d62f]