Main Tutorials

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 Author

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

Comments

Subscribe
Notify of
36 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Sagar
2 years ago

Thank you for this blog
Helps a lot to understand the concept.

Vikesh
3 years ago

Hi,
Getting this
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Function “PROC_NAME” not found; SQL statement:
Using h2db for IT tests with Junit 5 using spring boot 2.2.1.
Calling proc like
@repository
interface repo {
  @Query(value = “CALL PROC_NAME(:id_in);”, nativeQuery = true
List<Name> fetchNames(Long id);
}

Last edited 3 years ago by Vikesh
Sankar
4 years ago

Using Query query = session.createSQLQuery for stored procedure call taking 2 mins for 125000 records

Andrew
6 years ago

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.

Deepaksingh
4 years ago
Reply to  Andrew

for every .list() call it will return zero size list. that means it will work in both case w/o return from procedure.

srini
6 years ago

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

Bill
6 years ago

Thanks!

Sathana Subramaniam
7 years ago

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

Arpit Shah
7 years ago

what about out parameter

Tarang Zilpe
8 years ago

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
8 years ago

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
8 years ago

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
9 years ago

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
9 years ago

Hi Mkyong,

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

Lemongrass
9 years ago

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
9 years ago
Reply to  Lemongrass

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
9 years ago

EXEC is also Possible.

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

Niharika Saxena
10 years ago

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

ABHILASH JAIN
10 years ago

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

sapna
10 years ago

Can we create procedure from hibernate?

Alexei
10 years ago

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
9 years ago
Reply to  Alexei

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

hirenpatel
10 years ago

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

Jigar
10 years ago

What if there are multiple cursors ?

Varun Jadhav
10 years ago

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
11 years ago

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.
Thanks all.

Andres
11 years ago

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

Pv
11 years ago

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
8 years ago
Reply to  Pv

you can implement an union for easy handle 🙂

jatin
11 years ago

thanks

Sagar Dahagamwar
11 years ago

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)
at weblogic.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at weblogic.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at weblogic.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at weblogic.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at weblogic.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at weblogic.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at weblogic.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at weblogic.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at weblogic.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at weblogic.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at weblogic.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:128)
at sun.reflect.GeneratedMethodAccessor3317.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)

Please review and let me know if there are any precautions needed while executing Stored Procedure through Hibernate on Linux environment ?

Note : We are using Hibernate 4.1 for development.

Madan
12 years ago

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.

Vikas Kumar
8 years ago
Reply to  Madan

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

Milind Durugkar
11 years ago
Reply to  Madan

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

vijaya
13 years ago

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
10 years ago
Reply to  vijaya

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