当前位置：Gxlcms > 数据库问题 > Hibernate（十五）：QBC检索和本地SQL检索

Hibernate（十五）：QBC检索和本地SQL检索

时间：2021-07-01 10:21:17 帮助过：21人阅读

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

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

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

执行sql及结果:

  1. <span style="color: #000000;">Hibernate: 
  2.     </span><span style="color: #0000ff;">select</span><span style="color: #000000;">
  3.         this_.ID </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> ID1_1_0_,
  4.         this_.NAME </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> NAME2_1_0_,
  5.         this_.SALARY </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> SALARY3_1_0_,
  6.         this_.EMAIL </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> EMAIL4_1_0_,
  7.         this_.DEPARTMENT_ID </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> DEPARTME5_1_0_ 
  8.     </span><span style="color: #0000ff;">from</span><span style="color: #000000;">
  9.         DX_EMPLOYEE this_ 
  10.     </span><span style="color: #0000ff;">where</span><span style="color: #000000;">
  11.         this_.EMAIL</span><span style="color: #808080;">=</span><span style="color: #000000;">? 
  12.         </span><span style="color: #808080;">and</span> this_.SALARY<span style="color: #808080;">></span><span style="color: #000000;">?
  13. Employee </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">id=11, name=tommy10, salary=10000.0, email=tommy10@dx.com</span><span style="color: #ff0000;">]</span>

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

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

执行sql及结果：

  1. <span style="color: #008080;"> 1</span> <span style="color: #000000;">Hibernate: 
  2. </span><span style="color: #008080;"> 2</span>     <span style="color: #0000ff;">select</span>
  3. <span style="color: #008080;"> 3</span>         this_.ID <span style="color: #0000ff;">as</span><span style="color: #000000;"> ID1_1_0_,
  4. </span><span style="color: #008080;"> 4</span>         this_.NAME <span style="color: #0000ff;">as</span><span style="color: #000000;"> NAME2_1_0_,
  5. </span><span style="color: #008080;"> 5</span>         this_.SALARY <span style="color: #0000ff;">as</span><span style="color: #000000;"> SALARY3_1_0_,
  6. </span><span style="color: #008080;"> 6</span>         this_.EMAIL <span style="color: #0000ff;">as</span><span style="color: #000000;"> EMAIL4_1_0_,
  7. </span><span style="color: #008080;"> 7</span>         this_.DEPARTMENT_ID <span style="color: #0000ff;">as</span><span style="color: #000000;"> DEPARTME5_1_0_ 
  8. </span><span style="color: #008080;"> 8</span>     <span style="color: #0000ff;">from</span>
  9. <span style="color: #008080;"> 9</span> <span style="color: #000000;">        DX_EMPLOYEE this_ 
  10. </span><span style="color: #008080;">10</span>     <span style="color: #0000ff;">where</span>
  11. <span style="color: #008080;">11</span> <span style="color: #000000;">        (
  12. </span><span style="color: #008080;">12</span>             this_.SALARY<span style="color: #808080;">></span><span style="color: #000000;">? 
  13. </span><span style="color: #008080;">13</span>             <span style="color: #808080;">or</span> this_.EMAIL <span style="color: #0000ff;">is</span> <span style="color: #0000ff;">null</span>
  14. <span style="color: #008080;">14</span> <span style="color: #000000;">        ) 
  15. </span><span style="color: #008080;">15</span>         <span style="color: #808080;">and</span><span style="color: #000000;"> (
  16. </span><span style="color: #008080;">16</span>             this_.NAME <span style="color: #808080;">like</span><span style="color: #000000;"> ? 
  17. </span><span style="color: #008080;">17</span>             <span style="color: #808080;">and</span> this_.DEPARTMENT_ID<span style="color: #808080;">=</span><span style="color: #000000;">?
  18. </span><span style="color: #008080;">18</span> <span style="color: #000000;">        )
  19. </span><span style="color: #008080;">19</span> <span style="color: #800000; font-weight: bold;">2</span>

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

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

执行sql及结果：

  1. <span style="color: #008080;">1</span> <span style="color: #000000;">Hibernate: 
  2. </span><span style="color: #008080;">2</span>     <span style="color: #0000ff;">select</span>
  3. <span style="color: #008080;">3</span>         <span style="color: #ff00ff;">max</span>(this_.SALARY) <span style="color: #0000ff;">as</span><span style="color: #000000;"> y0_ 
  4. </span><span style="color: #008080;">4</span>     <span style="color: #0000ff;">from</span>
  5. <span style="color: #008080;">5</span> <span style="color: #000000;">        DX_EMPLOYEE this_
  6. </span><span style="color: #008080;">6</span> <span style="color: #800000; font-weight: bold;">79000.0</span>

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

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

执行sql及结果：

  1. <span style="color: #008080;"> 1</span> <span style="color: #000000;">Hibernate: 
  2. </span><span style="color: #008080;"> 2</span>     <span style="color: #0000ff;">select</span>
  3. <span style="color: #008080;"> 3</span>         this_.ID <span style="color: #0000ff;">as</span><span style="color: #000000;"> ID1_1_0_,
  4. </span><span style="color: #008080;"> 4</span>         this_.NAME <span style="color: #0000ff;">as</span><span style="color: #000000;"> NAME2_1_0_,
  5. </span><span style="color: #008080;"> 5</span>         this_.SALARY <span style="color: #0000ff;">as</span><span style="color: #000000;"> SALARY3_1_0_,
  6. </span><span style="color: #008080;"> 6</span>         this_.EMAIL <span style="color: #0000ff;">as</span><span style="color: #000000;"> EMAIL4_1_0_,
  7. </span><span style="color: #008080;"> 7</span>         this_.DEPARTMENT_ID <span style="color: #0000ff;">as</span><span style="color: #000000;"> DEPARTME5_1_0_ 
  8. </span><span style="color: #008080;"> 8</span>     <span style="color: #0000ff;">from</span>
  9. <span style="color: #008080;"> 9</span> <span style="color: #000000;">        DX_EMPLOYEE this_ 
  10. </span><span style="color: #008080;">10</span>     <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span>
  11. <span style="color: #008080;">11</span>         this_.SALARY <span style="color: #0000ff;">desc</span><span style="color: #000000;">,
  12. </span><span style="color: #008080;">12</span>         this_.NAME <span style="color: #0000ff;">desc</span><span style="color: #000000;"> limit ?,
  13. </span><span style="color: #008080;">13</span> <span style="color: #000000;">        ?
  14. </span><span style="color: #008080;">14</span> <span style="color: #800000; font-weight: bold;">5</span>

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

  • 本地SQL查询

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

 

Hibernate（十五）：QBC检索和本地SQL检索

标签：mode   from   add   restrict   span   mit   set   criteria   page   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！