java-MySQL存储过程

 1 import java.sql.CallableStatement; 
 2 import java.sql.Connection; 
 3 import java.sql.DriverManager; 
 4 import java.sql.Types; 
 5 
 6 
 7 
 8 /* 
 9  * 对应的存储过程 
10  DROP PROCEDURE IF EXISTS `pro_num_user`; 
11 delimiter ;; 
12 CREATE PROCEDURE `pro_num_user`(IN user_name varchar(10) ,OUT count_num INT) 
13     READS SQL DATA 
14 BEGIN 
15     SELECT COUNT(*) INTO count_num FROM tab_user WHERE 'name'=user_name; 
16 END 
17  ;; 
18 delimiter ; 
19  */ 
20 public class Test { 
21 
22     public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; 
23     public static final String URL = "jdbc:mysql://127.0.0.1:3306/test"; 
24     public static final String USERNAME = "root"; 
25     public static final String PASSWORD = "acbee"; 
26     public static void main(String[] args) throws Exception { 
27         test1(); 
28         test2(); 
29     } 
30      
31     public static void test1() throws Exception 
32     { 
33         Class.forName(DRIVER_CLASS); 
34         Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); 
35         String sql = "{CALL pro_num_user(?,?)}"; //调用存储过程 
36         CallableStatement cstm = connection.prepareCall(sql); //实例化对象cstm 
37         cstm.setString(1, "myd"); //存储过程输入参数 
38         //cstm.setInt(2, 2); // 存储过程输入参数 
39         cstm.registerOutParameter(2, Types.INTEGER); // 设置返回值类型 即返回值 
40         cstm.execute(); // 执行存储过程 
41         System.out.println(cstm.getInt(2)); 
42         cstm.close(); 
43         connection.close(); 
44     } 
45      
46     public static void test2() throws Exception 
47     { 
48         Class.forName(DRIVER_CLASS); 
49         Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); 
50         String sql = "{CALL pro_number(?,?,?)}"; //调用存储过程 
51         CallableStatement cstm = connection.prepareCall(sql); //实例化对象cstm 
52         cstm.setInt(1, 2); // 存储过程输入参数 
53         cstm.setInt(2, 2); // 存储过程输入参数 
54         cstm.registerOutParameter(3, Types.INTEGER); // 设置返回值类型 即返回值 
55         cstm.execute(); // 执行存储过程 
56         System.out.println(cstm.getInt(3)); 
57         cstm.close(); 
58         connection.close(); 
59          
60     } 
61 } 

 

转载于:https://www.cnblogs.com/kuangwong/p/6263954.html