hibernate中原生态sql语句以及加入Ehcache二级缓存

╰半橙微兮° 2022-06-12 00:25 156阅读 0赞

Hibernate3.x允许使用手写SQL语句，包括存储过程，所有的创建，更新，删除和load操作。

应用程序将从会话创建一个原生SQL查询（Session接口上）createSQLQuery()方法：

public SQLQuery createSQLQuery(String sqlString) throws HibernateException  
当传递一个包含SQL查询到createSQLQuery()方法，可以将SQL结果与任何现有的Hibernate实体，联接，或者一个标量结果使用addEntity()方法，addJoin()，和addScalar()方法关联的字符串。

标量查询：  
最基本的SQL查询是从一个或多个表中得到标量（数值）的列表。以下是语法使用原生SQL标量的值：

`String sql =` `"SELECT first_name, salary FROM EMPLOYEE"` `;`

`SQLQuery query = session.createSQLQuery(sql);`

`query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);`

`List results = query.list();`

实体的查询：  
上面的查询都是返回标量值，也就是从resultset中返回的“裸”数据。以下是语法通过addEntity（）方法来从原生SQL查询获得实体对象作为一个整体。

`String sql =` `"SELECT * FROM EMPLOYEE"` `;`

`SQLQuery query = session.createSQLQuery(sql);`

`query.addEntity(Employee.` `class` `);`

`List results = query.list();`

命名SQL查询：  
以下是语法通过addEntity（）方法来从原生SQL查询获得实体对象和使用命名SQL查询。

[?][Link 1]

<table style="border-collapse:collapse; border-spacing:0px; border:1px solid rgb(204,204,204); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin-top:0.54em; margin-right:0px!important; margin-bottom:0px!important; margin-left:0px!important; outline:0px!important; overflow:visible!important; padding:0px!important; position:static!important; right:auto!important; text-align:center; top:auto!important; vertical-align:baseline!important; width:643px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:1em!important; min-height:auto!important; clear:both; background:none!important"> 
 <tbody style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:1em!important; min-height:auto!important; background:none!important"> 
  <tr style="border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:0px!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:1em!important; min-height:auto!important; background:none!important"> 
   <td style="margin:0px!important; padding:0.2em 0.46em; border:1px solid rgb(204,204,204); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:1em!important; min-height:auto!important; color:rgb(175,175,175)!important; background:none!important"> 
    <div style="margin:0px!important; padding:0px 0.5em 0px 1em!important; border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(108,226,108)!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important">
      1 
    </div> 
    <div style="margin:0px!important; padding:0px 0.5em 0px 1em!important; border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(108,226,108)!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important">
      2 
    </div> 
    <div style="margin:0px!important; padding:0px 0.5em 0px 1em!important; border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(108,226,108)!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important">
      3 
    </div> 
    <div style="margin:0px!important; padding:0px 0.5em 0px 1em!important; border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(108,226,108)!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important">
      4 
    </div> 
    <div style="margin:0px!important; padding:0px 0.5em 0px 1em!important; border-width:0px 3px 0px 0px!important; border-right-style:solid!important; border-right-color:rgb(108,226,108)!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:right!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important">
      5 
    </div> </td> 
   <td style="margin:0px!important; padding:0.2em 0.46em; border:1px solid rgb(204,204,204); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:611px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:1em!important; min-height:auto!important; background:none!important"> 
    <div style="margin:0px!important; padding:0px!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:relative!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; background:none!important"> 
     <div style="margin:0px!important; padding:0px 1em!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important"> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">String sql = </code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:blue!important; clear:both; background:rgb(255,255,255)">"SELECT * FROM EMPLOYEE WHERE id = :employee_id"</code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">;</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important"> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">SQLQuery query = session.createSQLQuery(sql);</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important"> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">query.addEntity(Employee.</code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:bold!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:rgb(0,102,153)!important; clear:both; background:rgb(255,255,255)">class</code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">);</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important"> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">query.setParameter(</code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:blue!important; clear:both; background:rgb(255,255,255)">"employee_id"</code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">, </code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:rgb(0,153,0)!important; clear:both; background:rgb(255,255,255)">10</code> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">);</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:15.4px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; white-space:pre!important; background:none white!important"> 
      <code style="margin:3px auto 0px; padding:0px 0px 0px 5px; border-top-width:0px!important; border-right-width:0px!important; border-bottom-width:0px!important; border-left-width:3px; border-left-style:solid; border-left-color:rgb(108,226,108); bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:20px; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; text-align:left!important; top:auto!important; vertical-align:baseline!important; width:640px; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; font-weight:normal!important; font-style:normal!important; font-size:14px; min-height:auto!important; color:black!important; clear:both; background:rgb(255,255,255)">List results = query.list();</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

以及加入二级缓存办法：

直接上测试代码

1.  @Test  
2.  **public** **void** testSQLQuery()\{  
3.      Session s = sf.openSession();  
4.      Transaction tr = s.beginTransaction();  
5.      SQLQuery q = s.createSQLQuery("select \* from customers where id=1");  
6.      q.setCacheable(**true**);  
7.      Customer c=  (Customer) q.addEntity(Customer.**class**).uniqueResult();  
8.      tr.commit();  
9.      s.close();  
10.       
11. //上述代码会发出sql语句, 关闭session再开一个session, 以同样条件再次查询  
12.     s = sf.openSession();  
13.     tr = s.beginTransaction();  
14.       
15.     q = s.createSQLQuery("select \* from customers where id=1");  
16.     q.setCacheable(**true**);  
17.     c=  (Customer) q.addEntity(Customer.**class**).uniqueResult();  
18.     System.out.println(c.getName()); //再次查询, 没有发出sql,说明是从缓存中取的  
19.     tr.commit();  
20.     s.close();  
21.                     \}

主要就是给指定返回的类型。。。。。。

[Link 1]: http://www.jb51.net/article/76825.htm#