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.
Thank you for this blog
Helps a lot to understand the concept.
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);
}
Using Query query = session.createSQLQuery for stored procedure call taking 2 mins for 125000 records
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.
for every .list() call it will return zero size list. that means it will work in both case w/o return from procedure.
Hi Mk yong can you tell me about how to fetch particular columns instead of select *
Thanks!
It is nice. Can you please explain about output params?
what about out parameter
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.
Thnaks for the post.
It was throwing JDBC Exception. So i changed query.list() with query.executeUpdate() and now it’s working fine.
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.
Hello master, could you help me please ??
I have an errror in Stock stock = (Stock)result.get(i), says cannot be cast to stock
Hi Mkyong,
how to get Output parameter Mysql Store procedure using Hibernate??
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
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).
EXEC is also Possible.
Example: EXEC *StoredProcedureName* :Param1, :Param2, :Param3
Hi, Can you please tell how to call a procedure using hibernate when we have both in and OUt parametrs
How about if selected column is been queried in Stored Proc, then how hibernate handles it . with respect to xml mapping
Can we create procedure from hibernate?
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)
Oracle function return types must be predefined and not user defined.
Hi,
Please give me Example of call oracle storage procedure(sp) using Hibernate with struts2
Thanks,
hiren patel
What if there are multiple cursors ?
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.
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.
Hey, what I do not understand is where do you store the sql code so that java can call it?
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
you can implement an union for easy handle 🙂
thanks
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.
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.
Instead of using hbm file Map class file and annoted column with @TRANSIENT to bal column.
It was throwing JDBC Exception. So i changed query.list() with query.executeUpdate() and now it’s working fine.
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
Can we create procedure from hibernate? Please help me.
Thanks in advance..