程式扎記: [ Hibernate 常見問題] 如何透過 Hibernate 呼叫 DB 的 store procedure

標籤

2010年11月29日 星期一

[ Hibernate 常見問題] 如何透過 Hibernate 呼叫 DB 的 store procedure


轉載自 這裡
前言 :
In this tutorial, you will learn how to call a store procedure in Hibernate.

問題描述 :
Here’s a MySQL store procedure, which accept a stock code parameter and return the related stock data :
- Store procedure :
  1. DELIMITER $$  
  2.   
  3. CREATE PROCEDURE `GetStocks`(int_stockcode varchar(20))  
  4. BEGIN  
  5.    SELECT * FROM stock WHERE stock_code = int_stockcode;  
  6.    END $$  
  7.   
  8. DELIMITER ;  

In MySQL, you can simple call it with a call keyword :
CALL GetStocks('7277');

說明 Hibernate call store procedure :
In Hibernate, there are three approaches to call a database store procedure.
- Native SQL – createSQLQuery
You can use createSQLQuery() to call a store procedure directly.
- 範例代碼 (1) :
  1. Query query = session.createSQLQuery(  
  2.     "CALL GetStocks(:stockCode)")  
  3.     .addEntity(Stock.class)  
  4.     .setParameter("stockCode""7277");  
  5.   
  6. List result = query.list();  
  7. for(int i=0; i
  8.     Stock stock = (Stock)result.get(i);  
  9.     System.out.println(stock.getStockCode());  
  10. }  

- NamedNativeQuery in annotation
Declare your store procedure inside the @NamedNativeQueries annotation.
  1. //Stock.java  
  2. ...  
  3. @NamedNativeQueries({  
  4.     @NamedNativeQuery(  
  5.     name = "callStockStoreProcedure",  
  6.     query = "CALL GetStocks(:stockCode)",  
  7.     resultClass = Stock.class  
  8.     )  
  9. })  
  10. @Entity  
  11. @Table(name = "stock")  
  12. public class Stock implements java.io.Serializable {  
  13. ...  
- sql-query in XML mapping file
Declare your store procedure inside the “sql-query” tag.
- hibernate xml 描述檔 :
  1.   
  2. ...  
  3.   
  4.     <class name="com.mkyong.common.Stock" table="stock" ...>  
  5.         "stockId" type="java.lang.Integer">  
  6.             "STOCK_ID" />  
  7.             class="identity" />  
  8.           
  9.         "stockCode" type="string">  
  10.             "STOCK_CODE" length="10" not-null="true" unique="true" />  
  11.           
  12.         ...  
  13.     class>  
  14.   
  15.     "callStockStoreProcedure">  
  16.     <return alias="stock" class="com.mkyong.common.Stock"/>  
  17.       
  18.       
  19.   
  20.   

- 範例代碼 (3) : Call it with getNamedQuery().
  1. Query query = session.getNamedQuery("callStockStoreProcedure")  
  2.     .setParameter("stockCode""7277");  
  3. List result = query.list();  
  4. for(int i=0; i
  5.     Stock stock = (Stock)result.get(i);  
  6.     System.out.println(stock.getStockCode());  
  7. }  
This message was edited 1 time. Last update was at 29/11/2010 14:33:12

沒有留言:

張貼留言

網誌存檔

關於我自己

我的相片
Where there is a will, there is a way!