[原创]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 查询

 @Test
     public void testQBC(){
         //1.创建一个Criteria 对象
         Criteria criteria = session.createCriteria(Employee.class);

         //2.添加查询条件: 在QBC 中查询条件使用 Criteria 表示
         //Criteria 可以通过 Restrictions 的静态方法得到
         criteria.add(Restrictions.eq("email", "SKUMAR"));
         criteria.add(Restrictions.gt("salary", 5000F));

         //3.执行查询
         Employee employee = (Employee) criteria.uniqueResult();
         System.out.println(employee);
     }
     

　　2）带 AND 和 OR 的QBC： Restrictions 封装了一个常用的逻辑运算，算术等

　　

 @Test
     public void testQBC2(){
         Criteria criteria = session.createCriteria(Employee.class);

         //1.AND :使用Conjunction 表示
         //conjuntion 本身就是一个criteria 对象，且其中还可以添加Criteria 对象
         Conjunction conjunction = Restrictions.conjunction();
         conjunction.add(Restrictions.like("name", "a",MatchMode.ANYWHERE));
         Department dept = new Department();
         dept.setId(80);
         conjunction.add(Restrictions.eq("dept", dept));
         System.out.println(conjunction); //(name like %a% and dept=Department [id=80])

         //2.OR
         Disjunction disjunction = Restrictions.disjunction();
         disjunction.add(Restrictions.ge("salary", 6000F));
         disjunction.add(Restrictions.isNotNull("email") );

         System.out.println(disjunction);//(salary>=6000.0 or email is not null)

         //添加至 criteria
         criteria.add(disjunction);
         criteria.add(conjunction);

         //执行
         criteria.list();

     }

 Hibernate:
     select
         this_.ID as ID1_1_1_,
         this_.NAME as NAME2_1_1_,
         this_.SALARY as SALARY3_1_1_,
         this_.EMAIL as EMAIL4_1_1_,
         this_.DEPT_ID as DEPT_ID5_1_1_,
         department2_.ID as ID1_0_0_,
         department2_.NAME as NAME2_0_0_
     from
         GG_EMPLOYEE this_
     left outer join
         GG_DEPARTMENT department2_
             on this_.DEPT_ID=department2_.ID
     where
         (
             this_.SALARY>=?
             or this_.EMAIL is not null
         )
         and (
             this_.NAME like ?
             and this_.DEPT_ID=?
         )

　　3）统计查询：Projections 封装了一些 sql的聚合函数

　　

 @Test
     public void testQBC3(){
         Criteria criteria = session.createCriteria(Employee.class);

         //统计查询：使用Projection 来表示 可以由Projections 静态方法得到
         criteria.setProjection(Projections.max("salary"));
         System.out.println(criteria.uniqueResult());

     }

 Hibernate:
     select
         max(this_.SALARY) as y0_
     from
         GG_EMPLOYEE this_
 24000.0

　　4）排序，分页

     @Test
     public void testQBC4(){
         Criteria criteria = session.createCriteria(Employee.class);
         //1.添加排序
         criteria.addOrder(Order.asc("salary"));
         criteria.addOrder(Order.desc("email"));

         //2.添翻页方法
         int pageSize = 5;
         int pageNo = 3;
         criteria.setFirstResult((pageNo -1 ) * pageSize)
                 .setMaxResults(pageSize)
                 .list();

     }
     

 Hibernate:
     select
         *
     from
         ( select
             row_.*,
             rownum rownum_
         from
             ( select
                 this_.ID as ID1_1_1_,
                 this_.NAME as NAME2_1_1_,
                 this_.SALARY as SALARY3_1_1_,
                 this_.EMAIL as EMAIL4_1_1_,
                 this_.DEPT_ID as DEPT_ID5_1_1_,
                 department2_.ID as ID1_0_0_,
                 department2_.NAME as NAME2_0_0_
             from
                 GG_EMPLOYEE this_
             left outer join
                 GG_DEPARTMENT department2_
                     on this_.DEPT_ID=department2_.ID
             order by
                 this_.SALARY asc,
                 this_.EMAIL desc ) row_
         where
             rownum <= ?
         )
     where
         rownum_ > ?

3.由于QBC 不支持插入操作，所以hibernate 支持使用原生的 sql 进行更新操作

 @Test
     public void testNaviteSQL(){
 String sql = "INSERT INTO gg_department VALUES(?,?)";
         Query query = session.createSQLQuery(sql);

         query.setInteger(0, 300)
             .setString(1, "JASON")
             .executeUpdate();

     }

 Hibernate:
     INSERT
     INTO
         gg_department

     VALUES
         (?,?)

4.QBC 支持删除操作

 @Test
     public void testUpdate(){
         String hql = "DELETE FROM Department d WHERE d.id = :id";
         session.createQuery(hql).setInteger("id", 280).executeUpdate();
     }

 Hibernate:
     delete
     from
         GG_DEPARTMENT
     where
         ID=?