Calling MySql Stored Procedure using Hibernate

Steps Involved  for Calling MySQL Stored Procedure in Hibernate:

Step1:
Create Employee Table

Create table Employee(ENO varchar(12),ENAME varchar(10),Primary Key(ENO));

 Insert into Employee(ENO,ENAME)VALUES('123','Rahul');

Step2:
Create a MySQL Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `EmployeeProcedure`$$

CREATE  PROCEDURE EmployeeProcedure (IN employeeNo varchar(10) )
BEGIN
SELECT * FROM Employee WHERE EMPNO=employeeNo;
END$$

DELIMITER ;

Step3:
1)Create Hibernate Project

2)Create a Model Class
Employee

package edu.model;

public class Employee {
 private String employeeNo;
 private String employeeName;
 public String getEmployeeNo() {
  return employeeNo;
 }
 public void setEmployeeNo(String employeeNo) {
  this.employeeNo = employeeNo;
 }
 public String getEmployeeName() {
  return employeeName;
 }
 public void setEmployeeName(String employeeName) {
  this.employeeName = employeeName;
 }

}


Create Employee.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
 <class name="edu.model.Employee" table="Employee">
  <id name="employeeNo" column="EMPNO">
  <generator class="identity" />  
  </id>
  <property name="employeeName">
   <column name="EMPNAME" />
  </property>
 </class>
 <sql-query name="EmployeeProcedure" callable="true">
 <return alias="employee" class="edu.model.Employee">
 <return-property name="employeeName" column="EMPNAME"/>
    <return-property name="employeeNo" column="EMPNO"/>

</return>
 <![CDATA[CALL EmployeeProcedure (:employeeNo)]]>

 </sql-query>
</hibernate-mapping>

Finally Create EmployeeTest Class:

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class EmployeeTest {
 public static void main(String args[]) {
  Session session = SessionUtil.getSession();
  Transaction tx = session.beginTransaction();
  try {
   Query query = session.getNamedQuery("EmployeeProcedure");
   query.setParameter("employeeNo", "123");
   Employee employee = (Employee) query.uniqueResult();
   System.out.println("Employee Name:" + employee.getEmployeeName());
  } catch (HibernateException e) {
   System.err.println("Hibernate Exception"+e.getMessage());
   tx.rollback();
  }
 }
}