In this tutorial, we are going to access the MySql stored procedures in hibernate application. To do so, we need to create a procedure in Mysql. Here is the code for creating the procedure in MySql.
Creating Procedure in MySql :
DELIMITER // CREATE PROCEDURE getAllEmployees() BEGIN SELECT * FROM employee; END // DELIMITER ;
Now, we are going to call the above procedure from hibernate application. Here is the example.
Calling Stored Procedures in Hibernate :
Project Structure :
Required Dependencies:
pom.xml
[xml]
<dependencies>
<!– Hibernate –>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>4.3.0.Final</version>
</dependency>
<!– MySQL Driver –>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.0.5</version>
</dependency>
</dependencies>
[/xml]
Hibernate Pojo Class :
[java]
package com.onlinetutorialspoint.hibernate.pojo;
public class Employee {
private int employeeId;
public int getEmployeeId() {
return employeeId;
}
public void setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public int getDepartmentNo() {
return departmentNo;
}
public void setDepartmentNo(int departmentNo) {
this.departmentNo = departmentNo;
}
private String employeeName;
private int departmentNo;
}
[/java]
Hibernate Mapping File :
[xml highlight=”11-14″]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.onlinetutorialspoint.hibernate.pojo.Employee" table="employee">
<id name="employeeId" column="empid" />
<property name="employeeName" column="empname" />
<property name="departmentNo" column="deptno" />
</class>
<sql-query name="sQuery" callable="true">
<return class="com.onlinetutorialspoint.hibernate.pojo.Employee" alias="e"/>
call getAllEmployees()
</sql-query>
</hibernate-mapping>
[/xml]
HibernateUtil.java
[java]
package com.onlinetutorialspoint.util;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
public class HibernateUtil {
private HibernateUtil() {
}
private static SessionFactory sessionFactory;
public static synchronized SessionFactory getInstnce() {
if (sessionFactory == null) {
Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
.applySettings(configuration.getProperties());
sessionFactory = configuration.buildSessionFactory(builder.build());
}
return sessionFactory;
}
}
[/java]
Run the Application :
Main.java
[java]
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import com.onlinetutorialspoint.hibernate.pojo.Employee;
import com.onlinetutorialspoint.util.HibernateUtil;
public class Main {
public static void main(String[] args) {
SessionFactory sessionFactory = HibernateUtil.getInstnce();
Session session = sessionFactory.openSession();
Query query = session.getNamedQuery("sQuery");
List employeeList = query.list();
Iterator employeeItr = employeeList.iterator();
while (employeeItr.hasNext()) {
Employee employee = (Employee) employeeItr.next();
System.out.println("Employee id : " + employee.getEmployeeId());
}
}
}
[/java]
Output :
Hibernate: call getAllEmployees() Employee id : 10 Employee id : 20 Employee id : 30 Employee id : 101
Happy Learning 🙂
