[原创]java WEB学习笔记92：Hibernate学习之路-- -QBC 检索和本地 SQL 检索：基本的QBC 查询，带 AND 和 OR 的QBC，统计查询，排序，分页
本博客的目的：①总结自己的学习过程，相当于学习笔记 ②将自己的经验分享给大家，相互学习，互相交流，不可商用
内容难免出现问题，欢迎指正，交流，探讨，可以留言，也可以通过以下方式联系。
本人互联网技术爱好者，互联网技术发烧友
微博：伊直都在0221
QQ：951226918
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.QBC 检索和本地 SQL 检索
1）QBC 查询就是通过使用 Hibernate 提供的 Query By Criteria API 来查询对象，这种 API 封装了 SQL 语句的动态拼装，对查询提供了更加面向对象的功能接口
2）本地SQL查询来完善HQL不能涵盖所有的查询特性：添加操作
2.QBC 的具体实践和相关API
1）基本的QBC 查询
1 @Test
2 public void testQBC(){
3 //1.创建一个Criteria 对象
4 Criteria criteria = session.createCriteria(Employee.class);
5
6 //2.添加查询条件: 在QBC 中查询条件使用 Criteria 表示
7 //Criteria 可以通过 Restrictions 的静态方法得到
8 criteria.add(Restrictions.eq("email", "SKUMAR"));
9 criteria.add(Restrictions.gt("salary", 5000F));
10
11 //3.执行查询
12 Employee employee = (Employee) criteria.uniqueResult();
13 System.out.println(employee);
14 }
15
2）带 AND 和 OR 的QBC： Restrictions 封装了一个常用的逻辑运算，算术等
1 @Test
2 public void testQBC2(){
3 Criteria criteria = session.createCriteria(Employee.class);
4
5 //1.AND :使用Conjunction 表示
6 //conjuntion 本身就是一个criteria 对象，且其中还可以添加Criteria 对象
7 Conjunction conjunction = Restrictions.conjunction();
8 conjunction.add(Restrictions.like("name", "a",MatchMode.ANYWHERE));
9 Department dept = new Department();
10 dept.setId(80);
11 conjunction.add(Restrictions.eq("dept", dept));
12 System.out.println(conjunction); //(name like %a% and dept=Department [id=80])
13
14 //2.OR
15 Disjunction disjunction = Restrictions.disjunction();
16 disjunction.add(Restrictions.ge("salary", 6000F));
17 disjunction.add(Restrictions.isNotNull("email") );
18
19 System.out.println(disjunction);//(salary>=6000.0 or email is not null)
20
21 //添加至 criteria
22 criteria.add(disjunction);
23 criteria.add(conjunction);
24
25 //执行
26 criteria.list();
27
28 }
1 Hibernate:
2 select
3 this_.ID as ID1_1_1_,
4 this_.NAME as NAME2_1_1_,
5 this_.SALARY as SALARY3_1_1_,
6 this_.EMAIL as EMAIL4_1_1_,
7 this_.DEPT_ID as DEPT_ID5_1_1_,
8 department2_.ID as ID1_0_0_,
9 department2_.NAME as NAME2_0_0_
10 from
11 GG_EMPLOYEE this_
12 left outer join
13 GG_DEPARTMENT department2_
14 on this_.DEPT_ID=department2_.ID
15 where
16 (
17 this_.SALARY>=?
18 or this_.EMAIL is not null
19 )
20 and (
21 this_.NAME like ?
22 and this_.DEPT_ID=?
23 )
3）统计查询：Projections 封装了一些 sql的聚合函数
1 @Test
2 public void testQBC3(){
3 Criteria criteria = session.createCriteria(Employee.class);
4
5 //统计查询：使用Projection 来表示 可以由Projections 静态方法得到
6 criteria.setProjection(Projections.max("salary"));
7 System.out.println(criteria.uniqueResult());
8
9 }
1 Hibernate:
2 select
3 max(this_.SALARY) as y0_
4 from
5 GG_EMPLOYEE this_
6 24000.0
4）排序，分页
1 @Test
2 public void testQBC4(){
3 Criteria criteria = session.createCriteria(Employee.class);
4 //1.添加排序
5 criteria.addOrder(Order.asc("salary"));
6 criteria.addOrder(Order.desc("email"));
7
8 //2.添翻页方法
9 int pageSize = 5;
10 int pageNo = 3;
11 criteria.setFirstResult((pageNo -1 ) * pageSize)
12 .setMaxResults(pageSize)
13 .list();
14
15
16 }
17
1 Hibernate:
2 select
3 *
4 from
5 ( select
6 row_.*,
7 rownum rownum_
8 from
9 ( select
10 this_.ID as ID1_1_1_,
11 this_.NAME as NAME2_1_1_,
12 this_.SALARY as SALARY3_1_1_,
13 this_.EMAIL as EMAIL4_1_1_,
14 this_.DEPT_ID as DEPT_ID5_1_1_,
15 department2_.ID as ID1_0_0_,
16 department2_.NAME as NAME2_0_0_
17 from
18 GG_EMPLOYEE this_
19 left outer join
20 GG_DEPARTMENT department2_
21 on this_.DEPT_ID=department2_.ID
22 order by
23 this_.SALARY asc,
24 this_.EMAIL desc ) row_
25 where
26 rownum <= ?
27 )
28 where
29 rownum_ > ?
3.由于QBC 不支持插入操作，所以hibernate 支持使用原生的 sql 进行更新操作
1 @Test
2 public void testNaviteSQL(){
3 String sql = "INSERT INTO gg_department VALUES(?,?)";
4 Query query = session.createSQLQuery(sql);
5
6 query.setInteger(0, 300)
7 .setString(1, "JASON")
8 .executeUpdate();
9
10 }
1 Hibernate:
2 INSERT
3 INTO
4 gg_department
5
6 VALUES
7 (?,?)
4.QBC 支持删除操作
1 @Test
2 public void testUpdate(){
3 String hql = "DELETE FROM Department d WHERE d.id = :id";
4 session.createQuery(hql).setInteger("id", 280).executeUpdate();
5 }
1 Hibernate:
2 delete
3 from
4 GG_DEPARTMENT
5 where
6 ID=?
