HIbernate 调用Oracle存储过程

HIbernate 调用Oracle存储过程

1、创建存储过程

Proc代码

1. create or replace procedure changesalary(p_employeeid number, p_newsalary number)  is   
2. begin   
3.   update employees set salary= p_newsalary   
4.   where employee_id = p_employeeid;   
5.      
6.   if  sql%notfound then   
7.       raise_application_error(-20100,'Invalid Employee Id');   
8.   end if;   
9.   
10. end;   
11. /  

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;
/

2、hibernate配置

Xml代码

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">  
3. <hibernate-configuration>  
4.   <session-factory>  
5.     <property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>  
6.     <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>  
7.     <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>  
8.     <property name="hibernate.connection.username">hr</property>  
9.     <property name="hibernate.connection.password">hr</property>  
10.     <property name="hibernate.hbm2ddl.auto">update</property>  
11.     <property name="hibernate.show_sql">true</property>  
12.   </session-factory>  
13. </hibernate-configuration>  

<?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>

 3、hibernate应用

（1）使用JDBC连接

Java代码

1. import org.hibernate.Query;   
2. import org.hibernate.Session;   
3. import org.hibernate.SessionFactory;   
4. import org.hibernate.cfg.Configuration;   
5.   
6. public class CallSP {   
7.   
8.     public static void main(String[] args) throws Exception  {   
9.   
10.         Configuration c = new Configuration().configure();   
11.         SessionFactory sf = c.buildSessionFactory();   
12.         Session session = sf.openSession();   
13.         session.beginTransaction();   
14.   
15.         Connection con = session.connection();  // obtain JDBC connection from Session object   
16.         CallableStatement cs = con.prepareCall("{ call changesalary(?,?) }");   
17.         cs.setInt(1,100);  // first parameter index start with 1   
18.         cs.setInt(2,6000); // second parameter   
19.         cs.execute();  // call stored procedure   
20.   
21.         session.getTransaction().commit();   
22.         session.close();   
23.         sf.close();   
24.     }   
25. }  

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

 （2）使用Native SQL

Java代码

1. import org.hibernate.Query;   
2. import org.hibernate.Session;   
3. import org.hibernate.SessionFactory;   
4. import org.hibernate.cfg.Configuration;   
5.   
6. public class CallSP {   
7.   
8.     public static void main(String[] args) throws Exception  {   
9.   
10.         Configuration c = new Configuration().configure();   
11.         SessionFactory sf = c.buildSessionFactory();   
12.         Session session = sf.openSession();   
13.         session.beginTransaction();   
14.   
15.         Query q = session.createSQLQuery(" { call changesalary(?,?) }");   
16.         q.setInteger(0,100);  // first parameter, index starts with 0   
17.         q.setInteger(1,4000); // secon parameter   
18.         q.executeUpdate();   
19.         session.getTransaction().commit();   
20.         session.close();   
21.         sf.close();   
22.     }   
23. }