Hibernate调用存储过程

Hibernate调用存储过程

Hibernate调用存储过程方法一：
 List<?> list = new ArrayList<HibernateProcedureObject>();
try {
session = getCurrentSession(entityClass);
SQLQuery query = session.createSQLQuery(procedure);
query.setString(0, requestId);
list = query.list();
} catch (HibernateException e) {
e.printStackTrace();
logger.log(RMTManagementLevel.ERROR, e);
} finally {
this.closeConnection();
}
return list;

SQLQuery query=this.getSession().createSQLQuery("{call procName(?,?,?)}");
query.setString(0,x);
query.setString(1,xxx);
query.setString(2,xxxx);
List list = query.list();
Hibernate调用存储过程方法二：
 Session session =HibernateSessionFactory.getSession(); 
Connection conn = session.connection(); 
ResultSet rs =null; 
CallableStatement call = conn.prepareCall("{Call proc()}"); 
rs = call.executeQuery(); 
rs.close(); 
session.close(); 

===================================================

hibernate调用mysql存储过程

　　在mysql中创建两个存储过程，如下：
 
1、根据id查找某条数据：
 
1 CREATE PROCEDURE `findEmpById`(IN id INTEGER(11))
2 begin
3      select * from emp where empId=id;
4 end;

2、根据id查找某个字段，并返回
 
1 CREATE PROCEDURE `getNameById`(in id integer(11),out eName varchar(50))
2 begin
3      select empName into eName from emp where empId=id;
4 end;
　　在存储过程的参数列表里面，in修饰的参数代表输入参数，out修饰的代表输出参数。
 
使用hibernate调用上面两个存储过程：
 
　　（1）调用第一个存储过程
 

 1 package com.test;
 2
 3 import java.sql.CallableStatement;
 4 import java.sql.Connection;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7
 8 import org.hibernate.Session;
 9 import org.hibernate.SessionFactory;
10 import org.hibernate.cfg.Configuration;
11
12
13 public class 调用存储过程 {
14
15     /**
16      * @param args
17      * @throws SQLException
18      */
19     public static void main(String[] args) throws SQLException {
20         Configuration cfg = new Configuration().configure();
21         SessionFactory factory = cfg.buildSessionFactory();
22         Session session = factory.openSession();
23         Connection con = session.connection();
24         String sql = "{call findEmpById(?)}";
25         CallableStatement cs = con.prepareCall(sql);
26         cs.setObject(1, 2);
27         ResultSet rs = cs.executeQuery();
28         while(rs.next()){
29             int id = rs.getInt("empId");
30             String name = rs.getString("empName");
31             System.out.println(id+"\t"+name);
32         }
33     }
34
35 }

   　调用存储过程的sql语句为:call 存储过程名(参数...)，不过在java中调用存储过程一般都加{}。调用存储过程使用的是CallableStatement。
 
　　（2）调用第二个存储过程
 

 1 package com.test;
 2
 3 import java.sql.CallableStatement;
 4 import java.sql.Connection;
 5 import java.sql.SQLException;
 6
 7 import org.hibernate.Session;
 8 import org.hibernate.SessionFactory;
 9 import org.hibernate.cfg.Configuration;
10
11 public class 调用存储过程1 {
12
13    
14     public static void main(String[] args) throws SQLException {
15         Configuration config = new Configuration().configure();
16         SessionFactory sessionFactory = config.buildSessionFactory();
17         Session session = sessionFactory.openSession();
18         Connection conn = session.connection();
19         String sql = "{call getNameById(?,?)}";
20         CallableStatement cs = conn.prepareCall(sql); 
21         cs.setObject(1, 3); //设置输出参数
22         cs.registerOutParameter(2, java.sql.Types.VARCHAR); //设置第二个参数为输出参数
23         cs.execute(); //调用存储过程
24         String name = cs.getString(2);//获取输出参数
25         System.out.println(name);
26     }
27
28 }
============================================================

如何通过hibernate去调用存储过程？

说实话还真看不出来hibernate调用存储过程有什么优势
Java代码
  CREATE   TABLE  `proctab` (    `id`  int ( 11 )  NOT   NULL  auto_increment,    `Name`  varchar ( 20 ),    `age`  int ( 11 ),    PRIMARY   KEY   (`id`)    )  CREATE TABLE `proctab` ( `id` int ( 11 ) NOT NULL auto_increment, `Name`

varchar ( 20 ), `age` int ( 11 ), PRIMARY KEY (`id`) )简单的存储过程Java代码  create   PROCEDURE  proc()     begin      select   *   from  proctab;    end ;   create PROCEDURE proc() begin select * from proctab; end ;一种方法是通过

hibernate传统的xml映射方式去调用Java代码  <class name="com.test.User" table="proctab">        <id name="id" column="id">     <generator class="native"/>      </id>    <property name="name" column="name" type="string" />      <property

name="age" column="age" type="integer" />    </class>       <sql-query name="getUser" callable="true">        <return alias="user" class="com.test.User">        <return-property name="id" column="id" />        <return-property

name="name" column="name" />         <return-property name="age" column="age" />       </return>         {call proc()}       </sql-query>  <class name="com.test.User" table="proctab"> <id name="id" column="id"> <generator

class="native"/> </id> <property name="name" column="name" type="string" /> <property name="age" column="age" type="integer" /> </class> <sql-query name="getUser" callable="true"> <return alias="user" class="com.test.User"> <return-

property name="id" column="id" /><return-property name="name" column="name" /> <return-property name="age" column="age" /> </return> {call proc()} </sql-query>调用方法Java代码  Session ss= HibernateSessionFactory.getSession()    List

li=ss.getNamedQuery("getUser").list();    ss.close();  Session ss= HibernateSessionFactory.getSession() List li=ss.getNamedQuery("getUser").list(); ss.close();及其类似jdbc的方法Java代码  Session session

=HibernateSessionFactory.getSession();     Connection conn = session.connection();     ResultSet rs =null;    CallableStatement call = conn.prepareCall("{Call proc()}");    rs = call.executeQuery();    rs.close();    session.close(); 

Session session =HibernateSessionFactory.getSession(); Connection conn = session.connection(); ResultSet rs =null; CallableStatement call = conn.prepareCall("{Call proc()}"); rs = call.executeQuery(); rs.close(); session.close();

这种方法基本上就是jdbc还有就是通过强大的createSQLQuery来实现个人比较喜欢这种方法
Java代码
  Session session =HibernateSessionFactory.getSession();     SQLQuery query = session.createSQLQuery("{Call proc()}");    List list =query.list();    session.close();  Session session =HibernateSessionFactory.getSession(); SQLQuery query

= session.createSQLQuery("{Call proc()}"); List list =query.list(); session.close();如果没有返回值 直接用execute的方法就可以了还有一个重要的问题就是存储过程的传参问题Java代码  CallableStatement call = conn.prepareCall("{Call proc(?)}"); 

  call.setString(1, 参数);    rs = call.executeQuery();  CallableStatement call = conn.prepareCall("{Call proc(?)}"); call.setString(1, 参数); rs = call.executeQuery();Java代码  SQLQuery query = session.createSQLQuery("{Call proc(?)}"); 

  query.setString(0, 参数);    List list =query.list();