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.

How To Call Stored Procedure In Hibernate