How to call stored procedure in Hibernate ?

Steps to call stored procedure

1. Create POJO and Hibernate mapping file for that stored procedure.

2. Create a simple java class to get the connection from hibernate session and use that to call the stored procedure which is already mapped in hibernate mapping file.

Example

The stored procedure getEmpDetails (empNo , dep)

Execution command in MS SQL server 2005 –

exec getEmpDetails 1234, HR

This returns the columns Emp_Name, Emp_Address, Emp_DOB, Emp_Sal.

Make a class, implement it by serializable interface and make private variables for the columns which are returned from the stored procedure.

public class NewClass implements java.io.Serializable {

  private String Emp_Name;

  private String Emp_Address;

  private Date Emp_DOB;

  private Double Emp_Sal;

}

Make getter / setters and constructor with all parameters.

In hibernate mapping file, give the class name which maps to your POJO created. Also, put the property tags for the class and which column it maps to.

<class name="com.genius.NewClass">

   <property name="Emp_Name" type="java.lang.String">

       <column name=" Emp_Name" length="10" notnull=" true" />

   </property>

   ...

</class>

In hibernate mapping file, after the class tag and before ending the hibernate-mapping tag, we need to put tag as shown below : 

<!-- Declare a named query with the procedure to call -->

<sql-query name=" getEmpDetails" callable="true">

  <return alias=" getEmpDetails" class="com.genius.NewClass">

     <return-property name="Emp_Name" column="Emp_Name"/>

     ...

  </return>

  exec getEmpDetails :EmpNo, : Dept

</sql-query>

Create a Java class to get the data

SessionFactory factory = HibernateUtil.getSessionFactory();

Session session = factory.getCurrentSession();

session.beginTransaction();

Query query = session.getNamedQuery("getEmpDetails");   // get named query reference

query.setString("EmpNo", “1234”);

query.setString("Dept", “HR”);

List result = query.list();  // get the query result