java 调用mysql存储过程

来源：http://www.open-open.com/code/view/1456844372750

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. }

复制代码