• 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         // OR13         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
     
       items = (List
      
       ) 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     where11         (12             this_.SALARY>? 13             or this_.EMAIL is null14         ) 15         and (16             this_.NAME like ? 17             and this_.DEPARTMENT_ID=?18         )19 2

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

1     @Test2     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     select3         max(this_.SALARY) as y0_ 4     from5         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) Pager10         int pageSize = 5;11         int pageNum = 2;12         List
     
       employees = (List
      
       ) 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 by11         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     select10         department0_.ID as ID1_0_0_,11         department0_.NAME as NAME2_0_0_ 12     from13         DX_DEPARTMENT department0_ 14     where15         department0_.ID=?16 Department [id=7, name=移动部门]

• HQL删除操作：

1     @Test2     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     from4         DX_DEPARTMENT 5     where6         ID=?