Calling Oracle Stored Procedure From Hibernate

Create Stored Procedure

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)

<?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.dialect">org.hibernate.dialect.Oracle9Dialect</property>
    <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
    <property name="hibernate.connection.username">hr</property>
    <property name="hibernate.connection.password">hr</property>
    <property name="hibernate.hbm2ddl.auto">update</property>
    <property name="hibernate.show_sql">true</property>
  </session-factory>
</hibernate-configuration>

Hibernate Application

Using JDBC Connection

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

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();
    }
}

Post Your Comment