How To Call Stored Procedure In Hibernate

What is STORED PROCEDURE?

A stored procedure is a pre-compiled SQL subroutine used to perform multiple procedural operations.

MySQL store procedure

Here’s a MySQL store procedure, which accept a stock code parameter and return the related stock data.

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 :

CALL GetStocks('7277');

How to call MSSQL and MYSQL stored procedure in Hibernate?

In Hibernate, there are two approaches to call a database store procedure.

1. Native SQL – createSQLQuery

You can use createSQLQuery() to call a store procedure directly.

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.

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

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());
}

The above 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.

Source:http://www.tutorialsdesk.com/2014/11/how-to-call-stored-procedure-in.html