http://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/
In this tutorial, you will learn how to call a store procedure in Hibernate.
MySQL store procedure
Here’s a MySQL store procedure, which accept a stock code parameter and return the related stock data.
SQL
DELIMITER $$
CREATE PROCEDURE `GetStocks`(int_stockcode varchar(20))
BEGIN
SELECT * FROM stock where stock_code = int_stockcode;
END $$
DELIMITER ;
In MySQL, you can simple call it with a call keyword :
SQL
CALL GetStocks('7277');
Hibernate call store procedure
In Hibernate, there are three approaches to call a database store procedure.
1. Native SQL – createSQLQuery
You can use createSQLQuery() to call a store procedure directly.
Java
Query query = session.createSQLQuery(
"CALL GetStocks(:stockCode)")
.addEntity(Stock.class)
.setParameter("stockCode", "7277");
List result = query.list();
for(int i=0; i<result.size(); i++){
Stock stock = (Stock)result.get(i);
System.out.println(stock.getStockCode());
}
2. NamedNativeQuery in annotation
Declare your store procedure inside the @NamedNativeQueries annotation.
Java
//Stock.java
...
@NamedNativeQueries({
@NamedNativeQuery(
name = "callStockStoreProcedure",
query = "CALL GetStocks(:stockCode)",
resultClass = Stock.class
)
})
@Entity
@Table(name = "stock")
public class Stock implements java.io.Serializable {
...
Call it with getNamedQuery().
Java
Query query = session.getNamedQuery("callStockStoreProcedure")
.setParameter("stockCode", "7277");
List result = query.list();
for(int i=0; i<result.size(); i++){
Stock stock = (Stock)result.get(i);
System.out.println(stock.getStockCode());
}
3. sql-query in XML mapping file
Declare your store procedure inside the “sql-query” tag.
Markup
<!-- Stock.hbm.xml -->
...
<hibernate-mapping>
<class name="com.mkyong.common.Stock" table="stock" ...>
<id name="stockId" type="java.lang.Integer">
<column name="STOCK_ID" />
<generator class="identity" />
</id>
<property name="stockCode" type="string">
<column name="STOCK_CODE" length="10" not-null="true" unique="true" />
</property>
...
</class>
<sql-query name="callStockStoreProcedure">
<return alias="stock" class="com.mkyong.common.Stock"/>
<![CDATA[CALL GetStocks(:stockCode)]]>
</sql-query>
</hibernate-mapping>
Call it with getNamedQuery().
Java
Query query = session.getNamedQuery("callStockStoreProcedure")
.setParameter("stockCode", "7277");
List result = query.list();
for(int i=0; i<result.size(); i++){
Stock stock = (Stock)result.get(i);
System.out.println(stock.getStockCode());
}
Conclusion
The above three approaches are doing the same thing, call a store procedure in database. There are not much big different between the three approaches, which method you choose is depend on your personal prefer.
Read More:
- SQL error: 17006, sqlstate: 99999 invalid column name
- Use of HQL query.list () is a null pointer exception, but the database can find out the result
- Solve the problem that the interface between C # WinForm program and Oracle doesn’t respond for a long time
- PHP Fatal error: Call to a member function query() on a non-object in
- An unable to locate appropriate constructor on class solution appears
- Invalid object name ‘UserInfo’
- Mybatis uses step-by-step lazy loading to cause abnormal JSON conversion. The interface 500 reports an error
- Uncaught Error: Call to undefined function mysql_select_db()
- Java – how to shuffle an ArrayList
- SQLServerException: The server failed to resume the transaction. Desc:ab00000002
- java.lang.UnsupportedOperationException resolvent
- The solution of Hibernate query returning all null lists
- Call to a member function fetch_assoc() on a non-object
- Mybatis custom list collection parser
- How to Fix SQL Error: 1054, SQLState: 42S22 Unknown column ‘markcardex0_.art_service_time’ in ‘field list’
- Solution to error 1452: cannot add or update a child row: a foreign key constraint failures in MySQL
- Fatal error: Call to a member function bind_param() on a non-object in
- How can Oracle query tables of other users without adding a table user name
- Kill the specified port CMD command line taskkill in window