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.

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.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

  • 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)

  • hirenpatel

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

  • http://www.jigarshah.net Jigar

    What if there are multiple cursors ?

  • 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

    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

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

  • 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

  • jatin

    thanks

  • 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)
    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

    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

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

  • 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

  • Pingback: Hibernate Tutorials | Tutorials()