学习笔记，为了加强记忆和深入理解在此记录备忘。如有错误和理解不当之处，还望指正。万分感谢！
另外，实例和内容有些是参照网络其他博文，如有侵权可联系删掉。
参考博客 地址：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]
603
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
