[ 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 :

view plaincopy to clipboardprint?

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) :

view plaincopy to clipboardprint?

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.

view plaincopy to clipboardprint?

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 描述檔 :

view plaincopy to clipboardprint?

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().

view plaincopy to clipboardprint?

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