How to call stored 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.


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');

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.


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

3. sql-query in XML mapping file

Declare your store procedure inside the "sql-query" tag.


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


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.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Andrew
Guest
Andrew

Can you please provide an example for calling a stored procedure that does not perform any query. For example, a stored procedure that performs some updates and insert and does not return anything.

srini
Guest
srini

Hi Mk yong can you tell me about how to fetch particular columns instead of select *

Bill
Guest
Bill

Thanks!

Sathana Subramaniam
Guest
Sathana Subramaniam

It is nice. Can you please explain about output params?

Tarang Zilpe
Guest
Tarang Zilpe

I am using session.createSqlQuery()….
Question is query.list() will return a resultSet. How do I close this resultSet? Or is it that session.close() will close the result set automatically.

Vikas Kumar
Guest
Vikas Kumar

Thnaks for the post.
It was throwing JDBC Exception. So i changed query.list() with query.executeUpdate() and now it’s working fine.

Rachana Navarkar
Guest
Rachana Navarkar

I am using ORACLE 11g as my database.Above code run fine with MYSQL database but what if am run it with ORACLE 11g DB.

IT gives following error

ERROR: Cannot perform fetch on a PLSQL statement: next

Can you please help me to get the solution for this.

Acmejav Javi
Guest
Acmejav Javi

Hello master, could you help me please ??
I have an errror in Stock stock = (Stock)result.get(i), says cannot be cast to stock

Arun
Guest
Arun

Hi Mkyong,

how to get Output parameter Mysql Store procedure using Hibernate??

Lemongrass
Guest
Lemongrass

The examples are good, but in 1) there is a mistake:

The call should be like this:
SQLQuery query = session.createSQLQuery( …. );

If you use the Query class you will not be able to use addEntity etc.

Best regards

guitarIsTooExpensive
Guest
guitarIsTooExpensive

Nope, SQLQuery Interface is a Subinterface of Query Interface. The one-liner statement given here does work (a multiple statement solution would not be possible in such a transparent manner, though).

Cyanide
Guest
Cyanide

EXEC is also Possible.

Example: EXEC *StoredProcedureName* :Param1, :Param2, :Param3

Niharika Saxena
Guest
Niharika Saxena

Hi, Can you please tell how to call a procedure using hibernate when we have both in and OUt parametrs

ABHILASH JAIN
Guest
ABHILASH JAIN

How about if selected column is been queried in Stored Proc, then how hibernate handles it . with respect to xml mapping

sapna
Guest
sapna

Can we create procedure from hibernate?

Alexei
Guest
Alexei

I’ts very nice.
But what if using jpa 2.0 and oracle stored function with % rowtype as result?
(I have a problem and have not yet found a solution)

Avinash Reddy Cheerla
Guest
Avinash Reddy Cheerla

Oracle function return types must be predefined and not user defined.

hirenpatel
Guest
hirenpatel

Hi,
Please give me Example of call oracle storage procedure(sp) using Hibernate with struts2
Thanks,
hiren patel

Jigar
Guest
Jigar

What if there are multiple cursors ?

Varun Jadhav
Guest
Varun Jadhav

Can you give an example for calling Stored Procedure using named query.Actually I want to know how to set OUT parameter using named query.

Quang Vinh
Guest
Quang Vinh
Hi all, I want create namedquery to call one stored procedure and one function as below: Create or replace sp_test( p_num IN NUMBER(2), p_out OUT NUMBER(2) ) IS BEGIN p_out:=p_num*10; END; —-FUNCTION FUNCTION get_awardhis (p_member_id IN NUMBER) RETURN my_cursor IS c_result my_cursor; v_sql VARCHAR2 (7000); BEGIN v_sql := ‘ SELECT TO_CHAR (rh.bill_cycle,”dd/mm/yyyy”), rh.reward_id, rh.amount, rh.usage_mark, rh.serial,’ || ‘ rh.status, to_char(rh.create_date,”dd/mm/yyyy”), to_char(rh.approve_date,”dd/mm/yyyy”), to_char(rh.receive_date,”dd/mm/yyyy”) ‘ || ‘ FROM reward_history rh ‘ || ‘ WHERE rh.member_id = :p_member_id ‘ || ‘ ORDER BY rh.create_date DESC’; OPEN c_result FOR v_sql USING p_member_id; RETURN c_result; END; Please help me call them without java.sql.connection,only use hibernate.… Read more »
Andres
Guest
Andres

Hey, what I do not understand is where do you store the sql code so that java can call it?

Pv
Guest
Pv

I have a stored procedure which contains multiple results. for example, after executing the stored procedure in SQL, I will get two tables employee, Department.

How can I use both the tables using HIBERNATE in my GWT application?

Can you please provide an example for the same.

Thanks
Pratik

Martin Borruel
Guest
Martin Borruel

you can implement an union for easy handle :)

jatin
Guest
jatin

thanks

Sagar Dahagamwar
Guest
Sagar Dahagamwar
Hi, I found above tutorial very useful. I’ve implemented SP call in same manner (using annotation) as mentioned in above tutorial. I’ve deployed my application in windows/weblogic application server (11g). And the database I am using is MSSQL. On windows machine I am successfuly getting the result, But when I’ve deployed application on Linux/weblogic application server (11g) by communicating with the same database, I am getting below exception while execution of store procedure. [OWLS][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ‘@P1’. Here is the exact stack trace. Caused by: java.sql.SQLException: [OWLS][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ‘@P1’. at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source) at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)… Read more »
Madan
Guest
Madan

hi, this is my oracle stored procedure
CREATE OR REPLACE PROCEDURE sp_pos_pur_resp (
p_cursor OUT sys_refcursor,
hashed_pan IN VARCHAR2,
acc_type IN VARCHAR2,
trans_amt IN NUMBER
)
AS
bal NUMBER;
response VARCHAR2 (100);
BEGIN
reponse := NULL;
bal := 0;

OPEN p_cursor FOR
SELECT a.current_balance
INTO bal
FROM prod_accounts a, prod_card c
WHERE c.prod_card_id = a.prod_card_id
AND c.prod_card_hased_pan = hashed_pan
AND a.primaryaccttype = acc_type;
bal := bal – trans_amt;
IF bal < 0
THEN
response := 'in sufficient fund';
END IF;
END;

here this procedure returns balance & response.
response is not a column in table.
please tell me how can
i configure that in the hbm file?
also how should i call in the java?

Thanks in advance.

Milind Durugkar
Guest
Milind Durugkar

Instead of using hbm file Map class file and annoted column with @TRANSIENT to bal column.

Vikas Kumar
Guest
Vikas Kumar

It was throwing JDBC Exception. So i changed query.list() with query.executeUpdate() and now it’s working fine.

vijaya
Guest
vijaya

Hi,
Could you please give an example how to invoke SP which contains both insert and update queries in same stored procedure and either insert or update wil be executed depending upon the existance of the Record in DB.

Thanks
Vijaya

sapna
Guest
sapna

Can we create procedure from hibernate? Please help me.
Thanks in advance..

trackback
Hibernate Tutorials | Tutorials

[…] How to call store procedure in Hibernate It’s not recommend to put business logic into store procedure, never mind, you still allow to call store procedure in Hibernate. […]

Arpit Shah
Guest
Arpit Shah

what about out parameter