hibernate call mysql stored procedure

 

in mysql create two stored procedures, as follows :

 

1, according to id find some pieces of data :

 

  

1 CREATE PROCEDURE `findEmpById`(IN id INTEGER(11)) 
2 begin 
3      select * from emp where empId=id; 
4 end;

 

 

2, according to id find a field and return

 

  

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;

 

 

list of parameters in a stored procedure which , in the modified parameters represent the input parameters , out modifications on behalf of output parameters.

 

using hibernate call the above two stored procedures :

 

(1) call to the first stored procedure

 

  

 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 }

 

 

call a stored procedure sql statement is : call the stored procedure name ( arguments ... ) , but call a stored procedure in java generally add {} . Call a stored procedure using a CallableStatement.

 

(2) call the second stored procedure

 

  

 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 }

 

 If there are output parameters

, needs to be noted , as in the above code on line 22 .