[原创]javaWEB学习笔记92：Hibernate学习之路QBC检索和本地SQL检索：基本的QBC查询，带AND和OR的QBC，统计查询，排序，分页...

1 &＃64;Test
2 public void testQBC(){
3 //1.创建一个Criteria 对象
4 Criteria criteria &＃61; 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 &＃61; (Employee) criteria.uniqueResult();
13 System.out.println(employee);
14 }
15

1 &＃64;Test
2 public void testQBC2(){
3 Criteria criteria &＃61; session.createCriteria(Employee.class);
4
5 //1.AND :使用Conjunction 表示
6 //conjuntion 本身就是一个criteria 对象&＃xff0c;且其中还可以添加Criteria 对象
7 Conjunction conjunction &＃61; Restrictions.conjunction();
8 conjunction.add(Restrictions.like("name", "a",MatchMode.ANYWHERE));
9 Department dept &＃61; new Department();
10 dept.setId(80);
11 conjunction.add(Restrictions.eq("dept", dept));
12 System.out.println(conjunction); //(name like %a% and dept&＃61;Department [id&＃61;80])
13
14 //2.OR
15 Disjunction disjunction &＃61; Restrictions.disjunction();
16 disjunction.add(Restrictions.ge("salary", 6000F));
17 disjunction.add(Restrictions.isNotNull("email") );
18
19 System.out.println(disjunction);//(salary>&＃61;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&＃61;department2_.ID
15 where
16 (
17 this_.SALARY>&＃61;?
18 or this_.EMAIL is not null
19 )
20 and (
21 this_.NAME like ?
22 and this_.DEPT_ID&＃61;?
23 )

1 &＃64;Test
2 public void testQBC3(){
3 Criteria criteria &＃61; session.createCriteria(Employee.class);
4
5 //统计查询&＃xff1a;使用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

1 &＃64;Test
2 public void testQBC4(){
3 Criteria criteria &＃61; session.createCriteria(Employee.class);
4 //1.添加排序
5 criteria.addOrder(Order.asc("salary"));
6 criteria.addOrder(Order.desc("email"));
7
8 //2.添翻页方法
9 int pageSize &＃61; 5;
10 int pageNo &＃61; 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&＃61;department2_.ID
22 order by
23 this_.SALARY asc,
24 this_.EMAIL desc ) row_
25 where
26 rownum <&＃61; ?
27 )
28 where
29 rownum_ > ?

1 &＃64;Test
2 public void testNaviteSQL(){
3 String sql &＃61; "INSERT INTO gg_department VALUES(?,?)";
4 Query query &＃61; 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 (?,?)

1 &＃64;Test
2 public void testUpdate(){
3 String hql &＃61; "DELETE FROM Department d WHERE d.id &＃61; :id";
4 session.createQuery(hql).setInteger("id", 280).executeUpdate();
5 }

1 Hibernate:
2 delete
3 from
4 GG_DEPARTMENT
5 where
6 ID&＃61;?