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();
}
}
}
