Hibernate supports query via store procedure and functions. We can use store procedure in various ways.

One of the simple way has described below.

Suppose in employee1000 table there are so many employees.We want to give hike 10% to specific employee whose salary less than 10000. Our objective is achieve it through store procedure.

These are the steps

Create one store procedure in database like this

create or replace procedure employeeHike(salary number)  is
begin
  update emp1000 set sal=(sal+(sal*0.1))
  where sal<salary;
  if  sql%notfound then
      raise_application_error(-20100,'Invalid Employee Id');
  end if;
end;
Create Employee Class

package com;
import java.io.Serializable;
public class Employee implements Serializable{
	private long empno;
	private String ename;
	private int sal;
	private String job;
	private int deptno ;
	public long getEmpno() {
		return empno;
	}
	
	public void setEmpno(long empno) {
		this.empno = empno;
	}
	
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	
}

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="com.Employee" table="emp1000">
        <meta attribute="class-description"> This class contains employee details. </meta>
        <id name="empno" type="long" column="empno">
            <generator class="native" />
        </id>
        <property name="ename" type="string" column="ename" not-null="true" />
        <property name="job" type="string" column="job" not-null="true" />
        <property name="sal" type="integer" column="sal" not-null="true" />
        <property name="deptno" type="integer" column="deptno"
            not-null="true" />
    </class>
  </hibernate-mapping>

Cteate hibernateUtil class to get the SessionFactory object

package com.util;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {
	private static final SessionFactory sessionFactory;
	static {
		try {
			sessionFactory = new Configuration().configure()
					.buildSessionFactory();
		} catch (Throwable ex) {
			System.err.println("SessionFactory creation failed" + ex);
			throw new ExceptionInInitializerError(ex);
		}
	}

	public static SessionFactory getSessionFactory() {
		return sessionFactory;
	}
}

Desin the main class

package com;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.util.*;
public class Main {
	public static void main(String[] args) {
		Main dao=new Main();
		dao.saveEmployee("Rex", "MGR", 30000, 10);
		dao.saveEmployee("Raj", "CLERK", 10000, 30);
		dao.saveEmployee("Mike", "SALESMAN", 8000, 10);
		dao.updateEmployee();
	}
	public Long saveEmployee(String ename,String job,int sal,int deptno)
	{
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = null;
		Long empId = null;
		try {
			transaction = session.beginTransaction();
			Employee emp=new Employee();
			emp.setEname(ename);
			emp.setJob(job);
			emp.setSal(sal);
			emp.setDeptno(deptno);
			session.save(emp);
			transaction.commit();
			System.out.println("Records inserted sucessessfully");
		} catch (HibernateException e) {
			transaction.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		return empId;
	}


	public  void  updateEmployee() 
	{

		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = null;
		try { 
			transaction = session.beginTransaction();
			String queryString = "from Employee where sal = :sal";
			Connection con = session.connection();  
			CallableStatement cs = con.prepareCall("{ call employeeHike(?) }");
			cs.setInt(1,10000);
			cs.execute();
			System.out.println("Hike has given to employees");
		} catch (HibernateException e) { 

			transaction .rollback(); 

			e.printStackTrace(); 

		} catch (SQLException e) {
			e.printStackTrace();
		}

		finally { 

			session.close(); 

		} 
	}
} 

Create hibernate configuration file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
		"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
		"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
        <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:ORCL</property>
        <property name="hibernate.connection.username">scott</property>
        <property name="connection.password">cat</property>
        <property name="connection.pool_size">1</property>
        <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
        <property name="show_sql">true</property>
        <property name="hbm2ddl.auto">create-update</property>
        <mapping resource="com/Employee.hbm.xml"/>
    </session-factory>
</hibernate-configuration>


DOWNLOAD ZIP