Call Stored Procedure In Hibernate

Call Stored Procedure In Hibernate: Here is the simple answer of question 'How to call stored

procedure in Hibernate?'.A stored procedure is a declarative SQL statements stored inside the

database. A stored procedure can be invoked by triggers, using other stored procedures or

applications such as Java, C#, PHP, etc. Hibernate provides the easy way to call the stored

procedures. There are different ways to create query to call stored procedures in hibernate.

As simple JDBC query we can also pass the parameters in stored procedures.

Suppose user has a following stored procedure in the database :

    DELIMITER $$
    CREATE PROCEDURE 'getCustomers'(status varchar(20))
    BEGIN
       SELECT * FROM customerInfo WHERE status = status;
       END $$
    DELIMITER ;

In database you can call stored procedure using triggers and direct put following statement :

    CALL getCustomers('Active');

In Hibernate create a DAO class and use following code:

1) Using Native Query :

    Query query = session.createSQLQuery(
            "CALL getCustomers(:status)")
            .addEntity(Customer.class)
            .setParameter("status", "Active");
    List result = query.list();
    for(int i=0; i<result.size(); i++){
            Customer customer = (Customer)result.get(i);
            System.out.println(customer.getCustomerName());
    }

2) Using Named Native Query :

At the top of Customer model just declare the NamedNative Query

    @NamedNativeQueries({
            @NamedNativeQuery(
            name = "callCustomerStoreProcedure",
            query = "CALL getCustomers(:status)",
            resultClass = Customer.class
            )
    })
    @Entity
    @Table(name = "customerInfo")
    public class Customer implements java.io.Serializable {
    private int customerId;
    private String customerName;
    private String status
    ---
    }

In DAO code:

    Query query = session.getNamedQuery("callCustomerStoreProcedure")
            .setParameter("status", "Active");
    List result = query.list();
    for(int i=0; i<result.size(); i++){
            Customer customer = (Customer)result.get(i);
            System.out.println(customer.getCustomerName());
    }

3) Using Sql query in XML mapping Customer.hbm.xml

    <hibernate-mapping>
        <class name="com.evon.common.Customer" table="customerInfo" >
            <id name="customerId" type="java.lang.Integer">
                <column name="customerId" />
                <generator class="identity" />
            </id>
            <property name="customerName" type="string">
             <column name="customerName" length="30" not-null="true" unique="true" />
            </property>
        <property name="status" type="string">
             <column name="status" length="20" not-null="true" />
            </property>
            ...
        </class>
        <sql-query name="callCustomerStoreProcedure">
            <return alias="customer" class="com.evon.common.Customer"/>
            <![CDATA[CALL getCustomers(:status)]]>
        </sql-query>
    </hibernate-mapping>

In DAO code :

    Query query = session.getNamedQuery("callCustomerStoreProcedure").setParameter("status",

"Active");
    List result = query.list();
    for(int i=0; i<result.size(); i++){
            Customer customer = (Customer)result.get(i);
            System.out.println(customer.getCustomerName());
    }

You can find such more blogs at http://findnerd.com/NerdDigest/