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 :

Stored Procedures in Hibernate Example

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 🙂

Download Example

About the Author:

Founder of onlinetutorialspoint.com Love Java, Python, Shell and opensource frameworks. Follow him on twitter and facebook for latest updates.

Leave A Comment