How to use database stored procedure in hibernate

In this tutorial we are going to develop sample hibernate application to call database stored procedure. This will also explain how to create database stored procedure using MySQL.

Setup details (I am using):
-hibernate-distribution-3.3.2.GA
-MySQL server 5.1.35
-Java 6
-Extra classes slf4j required by hibernate can be downloaded from(http://www.4shared.com/get/96283822/9043bb56/slf4j-simple-152.html)
-Mysql connector jar

1. Create database stored procedure and test it at command prompt
-Create table employee
mysql>create table employee(id int(11) primary key,name varchar(255));

-Create procedure to select data from employee table by passing id.
delimiter //
DROP PROCEDURE IF EXISTS select_emp//
CREATE PROCEDURE select_emp(IN empid int)
READS SQL DATA
BEGIN
SET @emp = CONCAT('SELECT * FROM employee e where e.id=', empid);
PREPARE stmt FROM @emp;
EXECUTE stmt;
END;
//
Note: See mysql reference manual for more details.

-Execute procedure..
call select_emp()
//

2. Create hibernate sample application for executing this stored procedure:
This part is most critical part of the tutorial to understand. It doesn't matter much that you should have better understanding of hibernate framework. It's simple as java.

a) Person.java (a simple POJO or java bean)

package com.sam.hibernate.sample;
import java.io.Serializable;

public class Person implements Serializable {

private int id;
private String name;

public Person() {
}

public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}

b) Person.hbm.xml (hibernate mapping file for Person java class)

c) hibernate configuration file for Mysql database

d) finale step: Write a test class to execute procedure using hibernate
HibernateProcedureTest.java:

package com.sam.hibernate.sample;

import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.wipro.hibernate.Person;

public class HibernateProcedureTest{

public static void main(String[] args) {

SessionFactory sessions = new Configuration().configure()
.buildSessionFactory();
Session session = sessions.openSession();

Transaction tx = null;
try {
tx = session.beginTransaction();

List list = session.getNamedQuery("selectEmp").setParameter("id",
new Integer(1)).list();
for (Object obj : list){
Person person = (Person)obj;
System.out.println("EMPID>"+person.getId());
System.out.println("EMPNAME>"+person.getName());
}
System.out.println(list);

} catch (HibernateException e) {
if (tx != null)
tx.rollback();
e.printStackTrace();
} finally {
session.close();
}
}
}

Note: assuming employee with id 1 exist in database;

When you execute this test class this will display id and name of employee with id 1;