学习笔记,为了加强记忆和深入理解在此记录备忘。如有错误和理解不当之处,还望指正。万分感谢!
另外,实例和内容有些是参照网络其他博文,如有侵权可联系删掉。
参考地址:https://mp.csdn.net/postedit
目录
一、QBC检索方式
QBC:Query By Criteria,是一种更加面向对象的查询语言,提供的一系列QBC API来检索对象。
HQL所能做的事情,使用QBC也大多能做用,这个通过实例来看看QBC是如何使用的。
有些方法已经弃用,代替方法还没有总结。
1.1 查询全部
@Test
void testGetAll() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
// 获取结果集
List<Employee> employees = criteria.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_
结果集是对象组成的数组:
[demo.entity.Employee@37d871c2,
demo.entity.Employee@7a904f32,
demo.entity.Employee@2b59501e,
demo.entity.Employee@476e8796]
1.2 通过条件查询
1.2.1 and
// 多条件and
@Test
void testGetByConditionAnd() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
// add添加条件,通过Restrictions(字段名,值)eq是等于,默认是and
List<Employee> employees = criteria.add(Restrictions.eq("empId", 1)).add(Restrictions.eq("empName", "员工1"))
.list();
System.out.println(employees);
}
Hibernate SQL:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
this_.deptId AS deptId3_1_
FROM
t_employee this_
WHERE
this_.empId=? and this_.empName=?
结果集一个员工对象:
[demo.entity.Employee@21ac5eb4]
1.2.2 or
// 多条件or
@Test
void testGetByConditionOr() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
// 多条件or
List<Employee> employees = criteria
.add(Restrictions.or(Restrictions.eq("empId", 1), Restrictions.like("empName", "员工1%").ignoreCase()))
.list();
System.out.println(employees);
}
Hibernate SQL:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
this_.deptId AS deptId3_1_
FROM
t_employee this_
WHERE
(this_.empId=? or lower(this_.empName) like ?)
结果集一个员工对象:
[demo.entity.Employee@21ac5eb4]
1.2.3 like
@Test
void testGetByConditionLike() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
// like查询,俩种写法
// List<Employee> employees = criteria.add(Restrictions.like("empName", "员工1%").ignoreCase()).list();
List<Employee> employees = criteria.add(Restrictions.like("empName", "员工1", MatchMode.ANYWHERE).ignoreCase()).list();
System.out.println(employees);
}
Hibernate SQL:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
this_.deptId AS deptId3_1_
FROM
t_employee this_
WHERE
lower(this_.empName) like ?
结果集一个员工对象:
[demo.entity.Employee@21ac5eb4]
1.2.4 in
@Test
void testGetByConditionIn() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
List<Employee> employees = criteria.add(Restrictions.in("empId", new Integer[]{1, 2})).list();
System.out.println(employees);
}
Hibernate SQL:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
this_.deptId AS deptId3_1_
FROM
t_employee this_
WHERE
this_.empId in (?, ?)
结果集一个员工对象:
[demo.entity.Employee@21ac5eb4]
1.2.5 Restrictions的条件取值
实现数据库sql的条件查询
Restrictions.eq | 等于 |
Restrictions.allEq | 多个等于判断(多个key/value) |
Restrictions.gt | 大于 |
Restrictions.ge | 大于等于 |
Restrictions.lt | 小于 |
Restrictions.le | 小于等于 |
Restrictions.between | 对应sql的between |
Restrictions.like | 对应sql的like |
Restrictions.in | 对应sql的in |
Restrictions.and | and |
Restrictions.or | or |
Restrictions.sqlRestriction | sql限定查询 |
Restrictions.asc() | 升序 |
Restrictions.desc() | 降序 |
Restrictions.isNotNull | 非空查询 |
1.3 连接查询
@Test
void testGetInnerJoin() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
// createAlias默认是内连接,可以不用写。可以为dept表取别名,也可以不取
criteria.createAlias("dept", "d", Criteria.LEFT_JOIN);
List employees = criteria.list();
System.out.println(employees);
}
Hibernate:
SELECT
this_.empId AS empId1_1_0_,
dept1_.deptId AS deptId1_0_1_,
this_.empName AS empName2_1_0_,
this_.deptId AS deptId3_1_0_,
dept1_.deptName AS deptName2_0_1_
FROM
t_employee this_
left outer join t_dept d1_ on this_.deptId=d1_.deptId
结果集是以这种方式
[demo.entity.Employee@3b2f4a93,
demo.entity.Employee@213bd3d5,
demo.entity.Employee@451882b2,
demo.entity.Employee@4a23350]
1.4 分页查询
@Test
void testGetLimit() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
// 从第1条开始取2条
List<Employee> employees = criteria.setFirstResult(1).setMaxResults(2).list();
System.out.println(employees);
}
Hibernate:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
this_.deptId AS deptId3_1_
FROM
t_employee this_
LIMIT ?, ?
结果集是Employee对象数组:
[demo.entity.Employee@4acb2510, demo.entity.Employee@4e1459ea]
1.5 排序
@Test
void testOrder() {
// 创建criteriaQuery对象
Criteria criteria = session.createCriteria(Employee.class);
// empId排序
List<Employee> employees = criteria.addOrder(Order.desc("empId")).list();
System.out.println(employees);
}
Hibernate:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
this_.deptId AS deptId3_1_
FROM
t_employee this_
order by this_.empId desc
结果集是Employee对象数组:
[demo.entity.Employee@4acb2510, demo.entity.Employee@4e1459ea]
1.6 统计
查部门下的员工数
@Test
public void testCount() {
Criteria criteria = session.createCriteria(Employee.class);
// 统计部门1下的员工
List<Integer> employees = criteria.setProjection(Projections.rowCount()).add(Restrictions.eq("dept.deptId", 1)).list();
System.out.println(employees);
}
Hibernate:
SELECT
count(*) as y0_
FROM
t_employee this_
WHERE
this_.deptId=?
结果集是Employee对象数组:
[2]
1.7 聚合函数
查部门下的员工数
@Test
public void testFunc() {
Criteria criteria = session.createCriteria(Employee.class);
// 统计部门1下的员工
List<Integer> employees = criteria.setProjection(Projections.projectionList()
.add(Projections.property("dept.deptId"))
.add(Projections.rowCount())
.add(Projections.avg("empId"))
.add(Projections.max("empId"))
.add(Projections.min("empId"))
.add(Projections.sum("empId"))
.add(Projections.groupProperty("dept.deptId"))
).list();
System.out.println(employees);
}
Hibernate:
SELECT
this_.deptId AS y0_,
count( * ) AS y1_,
avg( this_.empId ) AS y2_,
max( this_.empId ) AS y3_,
min( this_.empId ) AS y4_,
sum( this_.empId ) AS y5_,
this_.deptId AS y6_
FROM
t_employee this_
GROUP BY
this_.deptId
结果集是Employee对象数组:
[[Ljava.lang.Object;@38b5f25, [Ljava.lang.Object;@327ed9f5]
1.8 多列查询
@Test
public void testCount() {
Criteria criteria = session.createCriteria(Employee.class);
// 统计部门1下的员工
List<Integer> employees = criteria.setProjection(Projections.rowCount()).add(Restrictions.eq("dept.deptId", 1)).list();
System.out.println(employees);
}
Hibernate:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
FROM
t_employee this_
结果集是Employee对象数组:
[[Ljava.lang.Object;@2776015d,
[Ljava.lang.Object;@b174a73,
[Ljava.lang.Object;@2e3f79a2,
[Ljava.lang.Object;@1460c81d]
1.9 非空查询
@Test
public void testNotNull() {
Criteria criteria = session.createCriteria(Employee.class);
// 统计部门1下的员工
List<Integer> employees = criteria.add(Restrictions.isNotNull("empId")).list();
System.out.println(employees);
}
Hibernate:
SELECT
this_.empId AS empId1_1_,
this_.empName AS empName2_1_,
this_.deptId AS deptId3_1_
FROM
t_employee this1_
WHERE
this_.empId is not null
结果集是Employee对象数组:
[demo.entity.Employee@4acb2510, demo.entity.Employee@4e1459ea]