Calling Stored procedure from Hibenate

Create Stored Procedure
Create a stored procedure to change salary of the given employee, by enter the following code at SQL prompt in Sql*Plus. You can use any of the other client tools provided by Oracle such as Application Express and SQL Developer.

create or replace procedure changesalary(p_employeeid number, p_newsalary number) is
begin
update employees set salary= p_newsalary
where employee_id = p_employeeid;

if sql%notfound then
raise_application_error(-20100,'Invalid Employee Id');
end if;

end;
/


Hibernate Configuration File (hibernate.cfg.xml)
The following details are to be provided in hibernate.cfg.xml. I am using Oracle Thin driver to connect to HR account. My Oracle is running on the same system as Hibernate application. According to your environment, change the setting as necessary.





org.hibernate.dialect.Oracle9Dialect
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@localhost:1521:xe
hr
hr
update
true



Hibernate Application
You can call stored procedure using any of the following two techniques:
Using JDBC Connection
In this technique, we obtain Connection object from Hibernate Session object and then use CallableStatement to call stored procedure. The following code shows this technique:

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class CallSP {

public static void main(String[] args) throws Exception {

Configuration c = new Configuration().configure();
SessionFactory sf = c.buildSessionFactory();
Session session = sf.openSession();
session.beginTransaction();

Connection con = session.connection(); // obtain JDBC connection from Session object
CallableStatement cs = con.prepareCall("{ call changesalary(?,?) }");
cs.setInt(1,100); // first parameter index start with 1
cs.setInt(2,6000); // second parameter
cs.execute(); // call stored procedure

session.getTransaction().commit();
session.close();
sf.close();
}
}


Using Native SQL
The second technique (and recommended one) is to call a stored procedure using native query with standard stored procedure calling syntax of JDBC. The following code demonstrates it.

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class CallSP {

public static void main(String[] args) throws Exception {

Configuration c = new Configuration().configure();
SessionFactory sf = c.buildSessionFactory();
Session session = sf.openSession();
session.beginTransaction();

Query q = session.createSQLQuery(" { call changesalary(?,?) }");
q.setInteger(0,100); // first parameter, index starts with 0
q.setInteger(1,4000); // secon parameter
q.executeUpdate();
session.getTransaction().commit();
session.close();
sf.close();
}
}