Hibernate（十五）：QBC检索、本地SQL检索和HQL删除

QBC检索

QBC查询就是通过使用Hibernate提供的Query By Criteria API来查询对象，这种API封装了SQL语句的动态拼装，对查询提供了更加面向对象的功能接口。

1）通过Critera实现具有条件的查询

 1     @Test
 2     public void testCriteria00() {
 3         // 1、创建criteria对象
 4         Criteria criteria = session.createCriteria(Employee.class);
 5 
 6         // 2、添加过滤条件可以用Criterion表，Criterion可以通过Restrictions的静态方法返回。
 7         criteria.add(Restrictions.eq("email", "tommy10@dx.com"));
 8         criteria.add(Restrictions.gt("salary", 1000F));
 9 
10         System.out.println(criteria.uniqueResult());
11     }

执行sql及结果:

Hibernate: 
    select
        this_.ID as ID1_1_0_,
        this_.NAME as NAME2_1_0_,
        this_.SALARY as SALARY3_1_0_,
        this_.EMAIL as EMAIL4_1_0_,
        this_.DEPARTMENT_ID as DEPARTME5_1_0_ 
    from
        DX_EMPLOYEE this_ 
    where
        this_.EMAIL=? 
        and this_.SALARY>?
Employee [id=11, name=tommy10, salary=10000.0, email=tommy10@dx.com]

2）通过Critera实现具有AND OR条件的查询

 1 @Test
 2     public void testCriteraAndOr() {
 3         Criteria criteria = session.createCriteria(Employee.class);
 4 
 5         // AND :使用Conjunction表示，Conjunction本身就是一个Criterion对象，且其中还可以添加Criterion对象
 6         Conjunction conjunction = Restrictions.conjunction();
 7         conjunction.add(Restrictions.like("name", "2", MatchMode.ANYWHERE));
 8         Department depart = new Department();
 9         depart.setId(5);
10         conjunction.add(Restrictions.eq("department", depart));
11 
12         // OR
13         Disjunction disjunction = Restrictions.disjunction();
14         disjunction.add(Restrictions.gt("salary", 10000F));
15         disjunction.add(Restrictions.isNull("email"));
16 
17         criteria.add(disjunction);
18         criteria.add(conjunction);
19 
20         List<Employee> items = (List<Employee>) criteria.list();
21         System.out.println(items.size());
22     }

执行sql及结果：

 1 Hibernate: 
 2     select
 3         this_.ID as ID1_1_0_,
 4         this_.NAME as NAME2_1_0_,
 5         this_.SALARY as SALARY3_1_0_,
 6         this_.EMAIL as EMAIL4_1_0_,
 7         this_.DEPARTMENT_ID as DEPARTME5_1_0_ 
 8     from
 9         DX_EMPLOYEE this_ 
10     where
11         (
12             this_.SALARY>? 
13             or this_.EMAIL is null
14         ) 
15         and (
16             this_.NAME like ? 
17             and this_.DEPARTMENT_ID=?
18         )
19 2

3）通过Critera实现统计查询：使用Projection 来表示

1     @Test
2     public void testStatistics(){
3         Criteria criteria=session.createCriteria(Employee.class);
4         
5         // 统计查询：使用Projection 来表示
6         criteria.setProjection(Projections.max("salary"));
7         
8         System.out.println(criteria.uniqueResult());        
9     }

执行sql及结果：

1 Hibernate: 
2     select
3         max(this_.SALARY) as y0_ 
4     from
5         DX_EMPLOYEE this_
6 79000.0

4）通过Critera实现排序、分页查询

 1     @Test
 2     public void testOrderByAndPager() {
 3         Criteria criteria = session.createCriteria(Employee.class);
 4 
 5         // 1) Order By
 6         criteria.addOrder(Order.desc("salary"));
 7         criteria.addOrder(Order.desc("name"));
 8 
 9         // 2) Pager
10         int pageSize = 5;
11         int pageNum = 2;
12         List<Employee> employees = (List<Employee>) criteria.setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list();
13         
14         System.out.println(employees.size());
15     }

执行sql及结果：

 1 Hibernate: 
 2     select
 3         this_.ID as ID1_1_0_,
 4         this_.NAME as NAME2_1_0_,
 5         this_.SALARY as SALARY3_1_0_,
 6         this_.EMAIL as EMAIL4_1_0_,
 7         this_.DEPARTMENT_ID as DEPARTME5_1_0_ 
 8     from
 9         DX_EMPLOYEE this_ 
10     order by
11         this_.SALARY desc,
12         this_.NAME desc limit ?,
13         ?
14 5

更多关于QBC的使用方式需要参考Hibernate官网实例。

本地SQL查询

本地SQL查询来完善HQL不能涵盖所有的查询特性。

 1     @Test
 2     public void testNativeSqlInsert() {
 3         String sql = "insert into dx_department(id,name)values(?,?);";
 4 
 5         Query query = session.createSQLQuery(sql);
 6 
 7         query.setInteger(0, 7).setString(1, "移动部门").executeUpdate();
 8 
 9         Department departs = session.get(Department.class, 7);
10         System.out.println(departs);
11     }

执行sql及结果：

 1 Hibernate: 
 2     insert 
 3     into
 4         dx_department
 5         (id,name)
 6     values
 7         (?,?);
 8 Hibernate: 
 9     select
10         department0_.ID as ID1_0_0_,
11         department0_.NAME as NAME2_0_0_ 
12     from
13         DX_DEPARTMENT department0_ 
14     where
15         department0_.ID=?
16 Department [id=7, name=移动部门]

HQL删除操作：

1     @Test
2     public void testDeleteWithHQL() {
3         String hql = "DELETE FROM Department d WHERE d.id=:id";
4         session.createQuery(hql).setInteger("id", 7).executeUpdate();
5     }

执行sql及结果：

1 Hibernate: 
2     delete 
3     from
4         DX_DEPARTMENT 
5     where
6         ID=?